MySqlUtil - TableAdapters for MySql

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Data.OleDb;
using System.Drawing;
using System.Text;
using System.Windows.Forms;
using MySqlDatabase;
using SampleDataSetData;
using SampleDataSetData.SampleDataSetTableAdapters;
using MySql.Data.MySqlClient;

namespace Sample
{
	public class Tests
	{
		MySqlDB db;
		SampleDataSet ds;

		string connectionString;

		SampleDataSet.CustomerDataTable customers;
		CustomerTableAdapter customersTA;
		SampleDataSet.CustomerRow crow;
		SampleDataSet.CustomerRecord crec;

		SampleDataSet.ProductDataTable products;
		ProductTableAdapter productsTA;
		SampleDataSet.ProductRow prow;
		SampleDataSet.ProductRecord prec;

		SampleDataSet.CustomerOrdersDataTable orders;
		CustomerOrdersTableAdapter ordersTA;
		SampleDataSet.CustomerOrdersRow orow;
		SampleDataSet.CustomerOrdersRecord orec;

		public bool DoTests ( MainForm main, DataGridView cdgv, DataGridView pdgv, DataGridView odgv, 
			string user, string password )
		{
			main.LogLine ( "Connecting to Database" );

			db = new MySqlDB ();

			if ( ! db.Connect ( user, password, "sample" ) )
			{
				main.LogLine ( "Connect Failed" );
				return false;
			}
			main.LogLine ( "- OK" );

			connectionString = db.ConnectionString;

			main.LogLine ( "Connection string is " + connectionString );

			ds = new SampleDataSet ();

			customers = ds.customer;
			products = ds.product;
			orders = ds.customerorders;

			customersTA = new CustomerTableAdapter ( connectionString );
			productsTA = new ProductTableAdapter ( connectionString );
			ordersTA = new CustomerOrdersTableAdapter ( connectionString );

			// Clear the database
			ordersTA.DeleteALL ();
			customersTA.DeleteALL ();
			productsTA.DeleteALL ();

			main.LogLine ( "database zapped" );

//			ordersTA.Update ( ds );
//			customersTA.Update ( ds );
//			productsTA.Update ( ds );
//			ds.AcceptChanges ();
//			return true;

			uint id = (uint) customersTA.ExecuteSP ( "ADDED", "ADDED CUSTOMER", true );
			id = customersTA.Insert ( "IANS", "Ian Semmel", true );
			crow = customers.NewCustomerRow ();
			crow.CustomerCode = "NERKF";
			crow.CustomerName = "Fred Nerk";
			crow.IsActive = false;
			id = customersTA.Insert ( crow );

			customersTA.Update ( ds );
			ds.AcceptChanges ();
			customersTA.Fill ( customers );

			// Add some customers

			int toAdd = 100;

			crec = new SampleDataSet.CustomerRecord ();

			main.LogLine ( String.Format ( "{1} Adding {0} Customers", toAdd, DateTime.Now.ToString () ) );

			double startAdd = Convert.ToDouble ( DateTime.Now.ToBinary () );

			ds.EnforceConstraints = false;
			for ( int i = 0 ; i < toAdd ; i++ )
			{
				crec.customerID = 0;
				crec.customerCode = "C" + i.ToString ();
				crec.customerName = "Name " + crec.customerCode;
				crec.isActive = true;
				customers.AddCustomerRow ( crec.customerCode, crec.customerName, crec.isActive );

			}

			customersTA.Update ( customers );
			ds.EnforceConstraints = true;

			SampleDataSet.CustomerRow rowx = customers [ 5 ];

			customersTA.Update ( customers );

			double finishAdd = Convert.ToDouble ( DateTime.Now.ToBinary () );

			double timeTaken = ( finishAdd - startAdd ) / 10000000;

			main.LogLine ( String.Format ( "{1} Finished in {0} seconds", timeTaken, DateTime.Now.ToString () ) );

			customers.AcceptChanges ();
//			customersTA.DeleteByField ( new string [] { "CustomerCode", "CustomerName" },
//									  new object [] { "IANS", "Ian Semmel" } );

			odgv.DataSource = customers;
			odgv.Refresh ();
			main.Update ();

			prec = new SampleDataSet.ProductRecord ();

			// Add some Products
			main.LogLine ( String.Format ( "{1} Adding {0} Products", toAdd, DateTime.Now.ToString () ) );

			startAdd = Convert.ToDouble ( DateTime.Now.ToBinary () );

			ds.EnforceConstraints = false;
			for ( int i = 0 ; i < toAdd ; i++ )
			{
				prec.productID = 0;
				prec.productCode = "P" + i.ToString ();
				prec.productDescription = "Product " + prec.productCode;
				prec.price = 10.00m + i;
				prec.stockOnHand = (uint) (i * 4);
				products.AddProductRow ( prec );
//				productsTA.Update ( products );
			}
			productsTA.Update ( products );
			products.AcceptChanges ();
			ds.EnforceConstraints = true;

			finishAdd = Convert.ToDouble ( DateTime.Now.ToBinary () );
			timeTaken = ( finishAdd - startAdd ) / 10000000;
			main.LogLine ( String.Format ( "{1} Finished in {0} seconds", timeTaken, DateTime.Now.ToString () ) );

//			int m = productsTA.FillByField ( new string [] { "ProductCode", "ProductDescription" },
//											   new object [] { "P1", "Product P1" },
//											   products );

			pdgv.DataSource = products;
			pdgv.Refresh ();

			// Add Some Orders

			int opc = 3;

			orec = new SampleDataSet.CustomerOrdersRecord ();

			// Add some Products
			main.LogLine ( String.Format ( "{1} Adding {0} * {2} Orders", 
				toAdd, DateTime.Now.ToString (), opc ) );

			startAdd = Convert.ToDouble ( DateTime.Now.ToBinary () );

			for ( int i = 0 ; i < toAdd ; i++ )
			{
				string ccode = "C" + i.ToString ();
				string pcode = "P" + i.ToString ();

				crow = customersTA.GetRowByCustomerCode ( ccode );
				if ( crow == null )
					main.LogLine ( String.Format ( "Customer {0} NOT FOUND", ccode ) );

				prow = productsTA.GetRowByProductCode ( pcode );
				if ( prow == null )
					main.LogLine ( String.Format ( "Product {0} NOT FOUND", pcode ) );

				if ( ( crow != null ) && ( prow != null ) )
				{
					// n orders per combo

					for ( int n = 0 ; n < opc ; n++ )
					{
						orec.customerOrderID = 0;
						orec.customerID = crow.CustomerID;
						orec.productID = prow.ProductID;
						orec.orderDate = DateTime.Now;
						orec.quantity = i + n;
						try
						{
							orders.AddCustomerOrdersRow ( orec );
						}
						catch
						{
						}
//						ordersTA.Update ( orders );
					}
				}
			}
			ordersTA.Update ( orders );
			orders.AcceptChanges ();
//			int o = ordersTA.FillByField ( "Quantity", 2, orders );

			finishAdd = Convert.ToDouble ( DateTime.Now.ToBinary () );
			timeTaken = ( finishAdd - startAdd ) / 10000000;
			main.LogLine ( String.Format ( "{1} Finished in {0} seconds", timeTaken, DateTime.Now.ToString () ) );

			cdgv.DataSource = orders;
			cdgv.Refresh ();

			// Try some Finds

			string arg = "IANS";

			main.LogLine ( String.Format ( "Finding customer {0}", arg ) );
			crow = customersTA.GetRowByCustomerCode ( arg );
			if ( crow != null )
				main.LogLine ( "- Success" );
			else
				main.LogLine ( "- NOT FOUND" );

			arg = "C50X";
			main.LogLine ( String.Format ( "Finding customer {0}", arg ) );
			crow = customersTA.GetRowByCustomerCode ( arg );
			if ( crow != null )
				main.LogLine ( "- Success" );
			else
				main.LogLine ( "- NOT FOUND" );

			arg = "P50";
			main.LogLine ( String.Format ( "Finding product {0}", arg ) );
			prow = productsTA.GetRowByProductCode ( arg );
			if ( prow != null )
				main.LogLine ( "- Success" );
			else
				main.LogLine ( "- NOT FOUND" );

			arg = "P50X";
			main.LogLine ( String.Format ( "Finding product {0}", arg ) );
			prow = productsTA.GetRowByProductCode ( arg );
			if ( crow != null )
				main.LogLine ( "- Success" );
			else
				main.LogLine ( "- NOT FOUND" );

			ds.EnforceConstraints = false;
			customersTA.DeleteByField ( new string [] { "CustomerName" },
										new object [] { "Ian Semmel" } );
//			customersTA.DeleteByField ( "CustomerName", "Ian Semmel" );

			customersTA.Update ( ds );
			customers.AcceptChanges ();

			customersTA.Fill ( customers );

			productsTA.FillByField ( "Price", 10.00m, products );

			ordersTA.DeleteByCustomerOrderID ( 1 );
			ordersTA.Fill ( orders );

			return true;
		}

	}
}
// Example of extending the generated code with custom procedures

namespace SampleDataSetData.SampleDataSetTableAdapters
{
	public partial class CustomerTableAdapter
	{
		public int ExecuteSP ( string ccode, string cname, bool active )
		{
			MySqlCommand cmd = new MySqlCommand ( "sp_InsertCustomer", Connection );
			cmd.CommandType = CommandType.StoredProcedure;

			MySqlParameter parm = new MySqlParameter ( "CCode", ccode );
			parm.Direction = ParameterDirection.Input;
			cmd.Parameters.Add ( parm );

			parm = new MySqlParameter ( "CName", cname );
			parm.Direction = ParameterDirection.Input;
			cmd.Parameters.Add ( parm );

			parm = new MySqlParameter ( "Active", active );
			parm.Direction = ParameterDirection.Input;
			cmd.Parameters.Add ( parm );

			parm = new MySqlParameter ( "rc", MySqlDbType.Int32 );
			parm.Direction = ParameterDirection.Output;
			cmd.Parameters.Add ( parm );

			if ( ( Connection.State & ConnectionState.Open ) != System.Data.ConnectionState.Open )
				Connection.Open ();

			int rc = cmd.ExecuteNonQuery ();

			int id = (int) cmd.Parameters [ "rc" ].Value;

			return id;

		}
	}
}