After refering the DLL into your project add these lines to import the namespace.
using iGold.OpenDL;
using iGold.OpenDL.SQL;
Connection string has to be set. If its been set in common class its a one time
process and will be accessible through out your application.
Common.ConnectionString = @"User id=sa;password=pwd;initial catalog=iGold;data
source =myDB";
Here comes a simple method to insert data into a table. This example assumes a table
purchase with two columns.
QuickSQL.ExecuteNonQuery("Insert into Purchase values('" + textBox1.Text +
"'," + textBox2.Text + ")", Common.CommandType.Text);
The same code in a different way of approach
QuickSQL.ExecuteNonQuery("Insert into Purchase values(@v1,@v2)",
Common.CommandType.Text,new DataParameter("@v1",textBox1.Text),
new DataParameter("@v2",textBox2.Text));
Passing Dataparameter as Array
DataParameter[] P = new DataParameter[2];
P[0] = new DataParameter("@v1", textBox1.Text);
P[1] = new DataParameter("@v2", textBox2.Text);
QuickSQL.ExecuteNonQuery("Insert into Purchase values(@v1,@v2)",
Common.CommandType.Text,P);
Using DataAccess object to retrieve data.
In the below example I am using an stored procedure to retrieve data. The stored
procedure contains an ordinary select query to fetch data from purchase table.
DataAccess Da = new DataAccess(Common.ConnectionString);
dataGridView1.DataSource = Da.ExecuteDataTable("SP_GetAll_Purchase",
Common.CommandType.StoredProcedure);
To handle a SQL transaction
The scenario is, Let say we have a Sales Master Table and Sales Details table. The
Sale Id generated in Sales master has to be used to insert data in sales details.
If any thing went wrong the entire operation has to be roll backed. For that purpose
we can go ahead with Transaction Manager class. The Connection string for TransactionManager
class has to be explicitly set and begin transaction method has to be called. If
we have an output parameter in Stored procedure the same has to be mentioned while
passing data to be executed.
DataParameter P = new DataParameter("@SaleId", SqlDbType.Int, true);
The third parameter in the above code specifies the parameter type as output. On
execution the value of P is populated and can be used any where in the subsequent
code. Here comes the full code.
TransactionManager Tm = new TransactionManager();
Tm.ConnectionString = Common.ConnectionString;
DataParameter P = new DataParameter("@SaleId", SqlDbType.Int, true);
Tm.BeginTransaction();
Tm.Execute(new DataCommand("Sp_Create_SalesMaster",
Common.CommandType.StoredProcedure, P,
new DataParameter("@Clientname", "Ashok Raja")));
Tm.Execute(new DataCommand("Sp_Create_Sales",
Common.CommandType.StoredProcedure,
new DataParameter("@SaleId", P.Value),
new DataParameter("@PurchaseDate", DateTime.Now),
new DataParameter("@PurchaseAmt", 2000)));
Tm.Commit();
|