Tipp: Generating Database Selects and Typedescripters Half-automatically

Hi,

i came about a problem recently. I had to connect a BDC Application to some Database-Views. These are Views that have about 160 Columns each. I wanted to have all Columns available in the Entity - So i thought about what to do now ?

The solution I worked out took me about 15 mins to implement and spared a whole lot of time which i would have needed to write every single typedescriptor myself.

The presented solution helps you build Select Statements and the needed TypeDescriptors WITH THEIR TYPES !!! in almost no time...

I would like to share my solution with you now:

- First of all i fired up Visual Studio and created a new Windows Application Project.

- Then opened the Server Explorer inside Visual Studio and connected to my Database. (Works for SQL, Oracle, ....)

- Next i inserted a Typed Dataset and dragged all my desired views inside of it.

- After that i insertet a Textbox onto my form and set it to multiline.

- Then i inserted the following Function into my code:

privatevoid ParseDataTable(DataTable dt,outStringBuilder sbSelect,outStringBuilder sbTypes)

{

sbSelect =newStringBuilder();

sbTypes =newStringBuilder();

sbSelect.Append("SELECT ");

sbTypes.Append("<TypeDescriptors>" + System.Environment.NewLine);

foreach (DataColumn colin dt.Columns)

{

if (col.Ordinal == dt.Columns.Count - 1)

sbSelect.Append(col.ColumnName +" ");

else

sbSelect.Append(col.ColumnName +", ");

sbTypes.AppendFormat("<TypeDescriptor TypeName=\"{0}\" Name=\"{1}\" />{2}", col.DataType.FullName, col.ColumnName, System.Environment.NewLine);

}

sbSelect.AppendFormat("FROM {0}", dt.TableName);

sbTypes.Append("</TypeDescriptors>");

}

This function expects you to pass a Table of the Dataset and two stringbuilder-objects. One will contain the Select-Statement that gets generated, the other contains the ready to cut and paste TypeDescriptors ! Be careful though you have to set the identifierName of the corresponding TypeDescriptor additionally.

The call would look like the following:

privatevoid Form1_Load(object sender,EventArgs e)

{

DataSet1 ds =newDataSet1();

StringBuilder sbSelect;// = new StringBuilder();

StringBuilder sbTypes;// = new StringBuilder();

ParseDataTable(ds.Opportunity,out sbSelect,out sbTypes);

textBox1.Text = sbSelect.ToString();

textBox1.Text +=Environment.NewLine;

textBox1.Text += sbTypes.ToString();

}

Thats it !!

I know this is more a manual solution than an automatic one but maybe it comes in handy for you if you have to build methods with large Columns...

Maybe someone has a better way of doing it or would like to write a more automatic solution - please post it or any comments here !

[5283 byte] By [Vansen] at [2008-3-6]