ADO.NET basics reference

- ADO.NET objects - Connection, Command, DataSet, DataReader, DataAdapter

- DataSet consists of a collection of tables, relationships, and constraints

- The GetChanges method of the DataSet object actually creates a second DataSet that contains only the changes to the data. This DataSet is then used by a DataAdapter (or other objects) to update the original data source.

- DataAdapter object works as a bridge between the DataSet and the source data(database)

- DataReader for forwardonly and readonly stream of data

- DataAdapter updates the data from dataset to Database using update, insert, delete command or by using SqlCommandBuilder which automatically generates the update commands.(Example4)

- SqlDataAdapter manages the connection itself, meaning it opens the connection and it closes the connection. If you
open the connection yourself, it detects this and it will not close the connection. You need to close the connection manually.

- SqlDataAdapter attach error messages to rows in a dataset by using RowError property(Example5). Then you can walk the error in a DataTable with the GetErrors() method. You can also test for errors using HasErrors.

- use RejectChanges on the DataSet, in which case the Row is restored.

- The SqlDataAdapter and SqlCommand are very similar, except for the Fill and Update methods. The Fill method fills the dataset, Update method takes changes from a DataSet and pushes them back into the database. This is accomplished by four commands specified on the DataAdapter. These commands are: SelectCommand, UpdateCommand, InsertCommand, and DeleteCommand. CommandBuilder object can generate these at run time based on a select statement. However, this run-time generation requires an extra round trip to the server to gather required metadata, so explicitly providing the insert, update, and delete commands at design time will result in better run-time performance.

- Filter Dataset using its select method
Ex: ds.Select("custname like 'Sri%'");


- Example1:

SqlConnection con=new SqlConnection(constring);
SqlCommand com=new SqlCommand();
SqlTransaction trans;
con.open();
com.Connection=con;
//Begin Transaction
trans=con.BeginTransaction();
com.Transaction=trans;
try
{
com.CommandText = "Delete from emp where empid=10";
com.ExecuteNonQuery();
com.CommandText = "Insert into emp(empid) values(10)";
com.ExecuteNonQuery();
trans.Commit();
}
catch(Exception e)
{
trans.Rollback();
}
finally
{
con.Close();
}

- Example2:

// Example using stored procedures-create, drop, in and out parameters
string spsql1="create procedure proc_name @name nchar(10), @id int out as select @id=empid from emp where empname=@name";
string spsql2="IF EXISTS ( select name from sysobjects where name='proc_name' and type='P') DROP PROCEDURE proc_name";
SqlConnection con=new SqlConnection(constring);
try
{
SqlCommand com1=new SqlCommand(spsql1,con);
SqlCommand com2=new SqlCommand(spsql2,con);
com2.ExecuteNonQuery();
com1.ExecuteNonQuery();

SqlCommand com3=new SqlCommand("proc_name",con);
com3.CommandType=CommandType.StoredProcedure;

SqlParameter params=null;
params=com3.Parameters.Add("@name", SqlDbType.NChar, 10);

// By default the parameter direction is input. So no need to specify the below statement.
params.Direction = ParameterDirection.Input;
com3.Parameters["@name"].value="ABCD";

params=com3.Parameters.Add("@id", SqlDbType.Int, 10);
params.Direction=ParameterDirection.Output;

com3.ExecuteNonQuery();

Console.WriteLine(com3.Parameters["@id"].value);
}
Catch(Exception e)
{

}
finally
{
con.Close();
}

- Example3:
//using dataset
string sqlstr="select * from emp";
SqlConnection con=new SqlConnection(constring);
try
{
SqlCommand com1=new SqlCommand(sqlstr,con);
SqlDataAdapter sa=new SqlDataAdapter(com1);
DataSet ds = new DataSet();
// we can use directly SqlDataAdapter as
// SqlDataAdapter sa=new SqlDataAdapter(sqlstr,con);
sa.fill(ds,"Emp");
foreach (DataRow dr in ds.Tables["Emp"].Rows)
{
Console.WriteLine(dr["empid"].ToString());
}
}
catch(Exception e)
{

}

- Example4:
//update using SqlDataAdapter and SqlCommandBuilder
string sqlstr1="select * from emp";
string sqlstr2="select * from dept";
SqlConnection con=new SqlConnection(constring);
try
{
SqlCommand com1=new SqlCommand(sqlstr1,con);
SqlCommand com2=new SqlCommand(sqlstr2,con);
SqlDataAdapter sa1=new SqlDataAdapter(com1);
SqlDataAdapter sa2=new SqlDataAdapter(com2);
DataSet ds = new DataSet();
DataRow dr;

// Create command builder. This line automatically generates the update commands for you, so you don't
// have to provide or create your own.
SqlCommandBuilder mySqlCommandBuilder = new SqlCommandBuilder(sa1);

// Set the MissingSchemaAction property to AddWithKey because Fill will not cause primary
// key & unique key information to be retrieved unless AddWithKey is specified.
sa1.MissingSchemaAction = MissingSchemaAction.AddWithKey;
sa2.MissingSchemaAction = MissingSchemaAction.AddWithKey;


sa1.fill(ds,"Emp");
sa2.fill(ds,"Dept");

// ADD RELATION
ds.Relations.Add("empdept", ds.Tables[0].Columns["empid"], ds.Tables[1].Columns["empid"]);

// EDIT
ds.Tables[0].Rows[0]["empname"] = "Pinkoo";

// ADD
dr=ds.Tables[0].NewRow();
dr["empid"]=10;
dr["empname"]="simpy";
ds.Tables[0].Rows.Add(dr);

//Update Database with SqlDataAdapter
da1.Update(ds,"Emp");

}
catch(Exception e)
{

}

-Example5:
// Create a new dataview instance on the emp table that was just created
DataView myDataView = new DataView(myDataSet.Tables["emp"]);
// Sort the view based on the empname column
myDataView.Sort = "empname";
myDataSet.Tables["emp"].Rows[0].RowError = "An Error was added";
myDataSet.Tables["emp"].Rows[1].RowError = "This is another error message";
if (myDataSet.Tables["Customers"].HasErrors)
{
DataRow[] ErrDataRows = myDataSet.Tables["emp"].GetErrors();
for (int i = 0; i <= ErrDataRows.Length - 1; i++)
{
}
}

-Example6:
//using Relations in a dataset
string sqlstr1="select * from emp";
string sqlstr2="select * from dept";
SqlConnection con=new SqlConnection(constring);
try
{
SqlCommand com1=new SqlCommand(sqlstr1,con);
SqlCommand com2=new SqlCommand(sqlstr2,con);
SqlDataAdapter sa1=new SqlDataAdapter(com1);
SqlDataAdapter sa2=new SqlDataAdapter(com2);
DataSet ds = new DataSet();

sa1.fill(ds,"Emp");
sa2.fill(ds,"Dept");

// ADD RELATION
ds.Relations.Add("empdept", ds.Tables[0].Columns["empid"], ds.Tables[1].Columns["empid"]);

foreach (DataRow dr1 in ds.Tables[0].Rows)
{
//access dr1["empid"]
foreach (DataRow dr2 in dr1.GetChildRows(ds.Relations["empdept"]))
{
//access dr2["deptid"]
}
}