Wednesday, 25 September 2013

ABC of ADO.Net


Basic architecture of ADO.Net:


                                             [Sql Server]
                                                    |
                                             [Connection]
                                                    |
                                              [Command]    
                                                    |
                             --------------------------------------
                             |                                                  |
                      [Data Reader]                                [Data Adapter]
                                                                                |
                                                                           [Dataset]
                                                                                |
                                                              ---- -----------------------     
                                                              |                                    |
                                                       [Datatable]                      [Dataview]




(* Files to import : System.data, System.Data.SqlClient, System.Configuration)

Objects of ADO.Net:
  • Connection
  • Comand
  • Dataadapter
  • Datareader
  • Dataset


Define connection string in web.confiq file:
 
 
              
 
           


Using the connection string in code:
Dim con As New SqlConnection(ConfigurationManager.ConnectionStrings("ConString").ConnectionString)
SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["ConString"].ConnectionString);


>> Command: (*Metohds : .ExecuteNonQuery,.ExecuteScaler,.ExecuteReader)
With Sql query:
Dim com As New SqlCommand(strSql,con)  //strSql : SQL Query
com.CommandType = CommandType.Text
com.ExecuteNonQuery()


With Stored Proc:
Dim com As New SqlCommand(stpName,con) //stpName : Stored Proc Name
com.CommandType = CommandType.StoredProcedure
Dim sqlParam As New SqlParameter
           sqlParam.ParameterName = "@CustomerName"  // Param name in Stored Proc
           sqlParam.Value = _CustName   // Value on screen
           sqlParam.SqlDbType = SqlDbType.Text
com.Parameters.Add(sqlParam)
com.ExecuteNonQuery()
With Sql query:
SqlCommand com = new SqlCommand(strSql,con);
com.CommandType = CommandType.Text
com.ExecuteNonQuery()   //--ExecuteScaler


With Stored Proc:
SqlCommand com = new SqlCommand(strSql,con);
com.CommandType = CommandType.StoredProcedure;
SqlParameter sqlParam = new SqlParameter();
      sqlParam.ParameterName = "@CustomerName";  // Param name in Stored Proc
      sqlParam.Value = _CustName;   // Value on screen
      sqlParam.SqlDbType = SqlDbType.Text;
com.Parameters.Add(sqlParam);
com.ExecuteNonQuery();  

>> Data Adapater: (*Methods : .Fill,.FillSchema,.Update)
Dim da As New SqlDataAdapter(com)   // command object with select query/stp
Dim ds As New DataSet()
da.Fill(ds)
SqlDataAdapter da = new SqlDataAdapter(com);
DataSet ds = new DataSet();
da.Fill(ds);


>> Data Adapater >> Update:
Dim da As New SqlDataAdapter(Sql, com)
Dim comB As New SqlCommandBuilder(da)


Dim ds As New DataSet()
da.Fill(ds, "BatsMen")
ds.Tables(0).Rows(0)("Name") = "V Andrew"
da.UpdateCommand = comB.GetUpdateCommand()
da.Update(ds, "BatsMen")
SqlDataAdapter da = new SqlDataAdapter(str,con);
SqlCommandBuilder comB = new SqlCommandBuilder(da);


DataSet ds = new DataSet();
da.Fill(ds, "BatsMen");
ds.Tables[0].Rows[0]["Name"] = "V Andrew";
da.UpdateCommand = comB.GetUpdateCommand();
da.Update(ds, "BatsMen");

>> Data Reader:
Dim dr As SqlDataReader
dr = com.ExecuteReader()
If dr.HasRows Then
   While (dr.Read())
       Dim strName As String = dr.Item("CustomerName")
   End While
End If
SqlDataReader dr;
dr = com.ExecuteReader();
if (dr.HasRows)
{
    while (dr.Read())
    {
          string Name = dr["CustomerName"].ToString();
     }
}

Dataset:



>>Methods:

Clear()
Clears all rows from all tables.


AcceptChanges()
Commits all changes after last AcceptChanges call.


Clone()
Copies structure(schemas, relations, and constraints etc) not data.
[ds2 = ds1.Clone()]


Copy()
Copies structure(schemas, relations, and constraints etc)  + data.


GetChanges()  [*Dataset/Datatable]
Returns Dataset/datatable with changed data only.
[ds2 = ds1.GetChanges()]


HasChanges()  [*Boolean]
Returns true if there is any change in the dataset (before calling AcceptChanges).


Merge()
*Pre-conditions:
1) All the columns in both datasets must match.
2) The data type of all columns in the datasets must be the same.
3) The column names should match.
4) Table names should also match.


Dim ds1 As New DataSet
Dim ds2 As New DataSet
da.fill(ds1,”MyTable”)
da.fill(ds2,”MyTable”)           
ds1.Merge(ds2)


GetXml()   [*Returns String]
Returns the Xml data

ReadXml()
ds.ReadXml(Server.MapPath("NewFile.xml"))


WriteXml()
ds.WriteXml(Server.MapPath("NewFile.xml"))


Data Relation (*In two tables within a dataset)
Dim dtr As New DataRelation("CustomerRelation", ds.Tables(“Customer”).Columns("CustID"),
ds.Tables(“Address”).Columns("CustID"))


ds1.Relations.Add(dtr)
DataRelation dtr = new DataRelation("CustomerRelation", ds.Tables[“Customer”].Columns["CustID"],
ds.Tables[“Address”].Columns["CustID"]);


ds.Relations.Add(dtr);



Dataview:



Dim dv As New DataView
dv = ds.Tables(0).DefaultView()
DataView dv = new DataView();
dv = ds.Tables[0].DefaultView;


Sort
dv.Sort = "CustomerName ASC"   // ASC,DESC
dv.Sort = "CustomerName ASC, PhoneNo DESC"


Filter
dv.Sort = "CustomerName ASC"        // sorting is required
dv.RowFilter = "CustomerName='Ahu' AND Address='delhi'"
dv.RowFilter = "CustomerName='Ahu' OR Address='delhi'"
dv.RowFilter = "Address IN (‘delhi’, 'mumbai', 'bangalore')"
dv.RowFilter = "Age > 18"
dv.RowFilter = "CustomerID like 'ab_%'"


Find
dv.Sort = "CustomerName ASC"        // sort by the search column
dim index as integer = dv.Find("Andrew")
// returns the row number(-1 in case no record found)


FindRows
dv.Sort = "CustomerName ASC"        // sort by the search column
Dim drv() As DataRowView
drv = dv.FindRows("CustomerName")
      If drv.Length = 0 Then
           // Console.WriteLine("No match found.")
      Else
            Dim myDRV As DataRowView
            For Each myDRV In drv
                  // Console.WriteLine(myDRV("a").ToString())
            Console.WriteLine("No match found.")
            Next
      End If


Add Row
Dim newRow As DataRowView = dv.AddNew()
newRow("CustomerName") = "DS3-3"
newRow("Address") = "1001"
newRow.EndEdit()
DataRowView drv = dv.AddNew();
newRow["CustomerName"] = "DS3-3";
newRow["Address"] = "1001";
newRow.EndEdit();


Delete Row
dv.Delete(1)    // Row index


Update Row
dv(1)("CustomerName") = "ABC"   // Row index


Create Datatable
Dim dt As New DataTable
dt = dv.ToTable()



Datatable:



Create new table
Dim dt As New DataTable("MyTable")       // New Table
dt.Columns.Add("CustomerID", Type.GetType("System.Int32"))
dt.Columns.Add("CustomerName", Type.GetType("System.String"))
dt.Columns.Add("Address", Type.GetType("System.String"))


Dim dr As DataRow = dt.NewRow()       // New Row
dr("CustomerID") = "101"
dr("CustomerName") = "Andrew"
dr("Address") = "Delhi"


//-- or : dt.Rows.Add("102", "ddd", "mumbai")


dt.Rows.Add(dr)           // Add Row to table
DataTable dt = new DataTable("MyTable");
dt.Columns.Add("CustomerID", Type.GetType("System.String"));
dt.Columns.Add("CustomerName", Type.GetType("System.String"));
dt.Columns.Add("Address", Type.GetType("System.String"));


DataRow dr = dt.NewRow();
dr["CustomerID"] = "101";
dr["CustomerName"] = "Andrew";
dr["Address"] = "Delhi";


dt.Rows.Add(dr);


Add Primary Key
Dim pk(0) As DataColumn
pk(0) = ds1.Tables(0).Columns("CustID”)
dt.PrimaryKey = pk
DataColumn[] pk;
pk[0] = dt.Columns["CustID"];
dt.PrimaryKey = pk;


Select Row
Dim result() As DataRow = dt.Select("CustomerID >= 102")
      If result.Count > 0 Then
           For Each row As DataRow In result
                Console.WriteLine("{0}, {1}", row("CustomerName"), row("Address"))
           Next
      End If
DataRow[] result = dt.Select("CustomerID >= 102");
   if (result.Length > 0)
   {
           foreach (DataRow row in result)
           {
                Console.WriteLine("{0}, {1}", row["CustomerName"], row["Address"]);
           }
   }