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;
}
}
}