Re: MySQL and strongl typed datasets

[Emma Middlebrook]

Yes it is possible to use strongly typed datasets. This is the way I did it:

In your C# project you need to create an Xml Schema file. I decided to put these in a separate dll project. So you add a new Xml Schema data file to your project called something.xsd. (DatabaseTables.xsd for this example)

In your xsd file, you need to create an element that matches your database table design in MySQL. With Microsoft SQL Explorer you can just view your database and just drag the table onto this form to auto generate it for you. There is a MySQL server explorer tool out there but unfortunately you can't do this nifty little trick and instead you need to manually create it. You can specify Primary Keys and auto-increment attributes etc very easily. Once this is compiled it will generate all the code required to use your strongly typed dataset in your project.

Here's an xml example of one that I created:

<xs:element name="BookInfo">
<xs:complexType>
<xs:sequence>
<xs:element name="Book_ID" type="xs:int" msdata:AutoIncrement="true" />
<xs:element name="Author_ID" type="xs:int" />
<xs:element name="Title" type="xs:string" />
<xs:element name="Pen_Name" type="xs:string" />
<xs:element name="Synopsis" type="xs:string" />
</xs:sequence>
</xs:complexType>
<xs:key name="PK_Book" msdata:PrimaryKey="true">
<xs:selector xpath="." />
<xs:field xpath="mstns:Book_ID" />
</xs:key>
</xs:element>

DatabaseTables will be generated as your new strongly typed dataset and in there you will have DataTables (however many you defined on the xsd). Now you can start calling it like this:

DatabaseTables stronglyTypedDataset = new DatabaseTables();

Generate the sql objects that you require... e.g:

MySqlCommand cmd = new MySqlCommand(m_procedureName, m_sqlConnection);
cmd.CommandType = CommandType.StoredProcedure;
foreach (MySqlParameter param in m_params)
{
cmd.Parameters.Add(param);
}

MySqlDataAdapter adapter = new MySqlDataAdapter(cmd);
MySqlCommandBuilder builder = new MySqlCommandBuilder(adapter);
adapter.UpdateCommand = builder.GetUpdateCommand();
adapter.DeleteCommand = builder.GetDeleteCommand();
adapter.InsertCommand = builder.GetInsertCommand();

// Here I tell it what table I want updated in my strongly typed ds.
int updated = adapter.Update(stronglyTypedDataset, stronglyTypedDataset.BookInfo.TableName);


You know that it is strongly typed and you could get hold of your table easily like:
In my example I assign a table within my strongly typed dataset to a grid so that it displays an overview in my application. The data source can be a variety of things, I chose the datatable.

m_gridBooks.DataSource = stronglyTypedDataset.Tables[stronglyTypedDataset.BookInfo.TableName];

// Get the books table into our strongly typed data table member variable.
m_dtBooks = m_gridBooks.DataSource as DatabaseTables.BookInfoDataTable;

You will have rows as well defined:

DatabaseTables.BookInfoRow book = m_dtBooks[0].Row as DatabaseTables.BookInfoRow;

and then from this row I could just get at the columns values:

GetBookReleases(book.Book_ID);

You can easily get back to the dataset from a table by just calling m_dtBooks.DataSet.

Hope that Helps,

Emma