ADO.Net Introduction

Command Object in ADO.NET

Command : Command is used to execute almost any SQL command from within the .net application. The SQL command like insert, update, delete, select, create, alter, drop can be executed with command object and you can also call stored procedures with the command object. Command object has the following important properties.

• Connection : used to specify the connection to be used by the command object.
• CommandType : Used to specify the type of SQL command you want to execute. To assign a value to this property, use the enumeration CommandType that has the members Text, StoredProcedure and TableDirect. Text is the default and is set when you want to execute ant SQL command with command object. StoredProcedure is set when you want to call a stored procedure or function andTableDirect is set when you want to retrieve data from the table directly by specifying the table name without writing a select statement.
• CommandText : Used to specify the SQL statement you want to execute.
• Transaction : Used to associate a transaction object to the command object so that the changes made to the database with command object can be committed or rollback.

Command object has the following important methods.

• ExecuteNonQuery() : Used to execute an SQL statement that doesn’t return any value like insert, update and delete. Return type of this method is int and it returns the no. of rows effected by the given statement.
• ExecuteScalar() : Used to execute an SQL statement and return a single value. When the select statement executed by executescalar() method returns a row and multiple rows, then the method will return the value of first column of first row returned by the query. Return type of this method is object.

• ExecuteReader() : Used to execute a select a statement and return the rows returned by the select statement as a DataReader. Return type of this method is DataReader.

Connected Architecture of ADO.NET

The architecture of ADO.net, in which connection must be opened to access the data retrieved from database is called as connected architecture. Connected architecture was built on the classes connection, command, datareader and transaction. 

Connection : in connected architecture also the purpose of connection is to just establish aconnection to database and it self will not transfer any data.

 DataReader : DataReader is used to store the data retrieved by command object and make it available for .net application. Data in DataReader is read only and within the DataReader you can navigate only in forward direction and it also only one record at a time. 
To access one by one record from the DataReader, call Read() method of the DataReader whose return type is bool. When the next record was successfully read, the Read() method will return true and otherwise returns false.

Disconnected Architecture in ADO.NET

                            The architecture of ADO.net in which data retrieved from database can be accessed even when connection to database was closed is called as disconnected architecture. Disconnected architecture of ADO.net was built on classes connection, dataadapter, commandbuilder and dataset and dataview.





Connection : Connection object is used to establish a connection to database and connectionit self will not transfer any data.

DataAdapter : DataAdapter is used to transfer the data between database and dataset. It has commands like select, insert, update and delete. Select command is used to retrieve data from database and insert, update and delete commands are used to send changes to the data in dataset to database. It needs a connection to transfer the data.

CommandBuilder : by default dataadapter contains only the select command and it doesn’tcontain insert, update and delete commands. To create insert, update and delete commands for the dataadapter, commandbuilder is used. It is used only to create these commands for the dataadapter and has no other purpose.

DataSet : Dataset is used to store the data retrieved from database by dataadapter and make it available for .net application.

To fill data in to dataset fill() method of dataadapter is used and has the following syntax.

Da.Fill(Ds,”TableName”); 

When fill method was called, dataadapter will open a connection to database, executes select command, stores the data retrieved by select command in to dataset and immediately closes the connection. 

As connection to database was closed, any changes to the data in dataset will not be directly sent to the database and will be made only in the dataset. To send changes made to data in dataset to the database, Update() method of the dataadapter is used that has the following syntax.

Da.Update(Ds,”Tablename”); 

When Update method was called, dataadapter will again open the connection to database, executes insert, update and delete commands to send changes in dataset to database and immediately closes the connection. As connection is opened only when it is required and will be automatically closed when it was not required, this architecture is called disconnected architecture. 

A dataset can contain data in multiple tables. 

DataView : DataView is a view of table available in DataSet. It is used to find a record, sort the records and filter the records. By using dataview, you can also perform insert, update and delete as in case of a DataSet.

Dataset vs DataReader

ADO.NET Dataset vs DataReader

DataSet and DataReader are called fundamental objects of ADO.net as they are used to store data and make it available for .net application and they have the following differences.

Differences Between DataSet and DataReader

DataSet	DataReader
It  is  disconnected  object  and  can  provide	It is connected object and can not provide
access  to  data  even  when  connection  to	access to data when connection to database
database was closed.	was closed.
It can store data from multiple tables	It can store data from only one table.
It allows insert, update and delete on data	It  is  read  only  and  it  doesn’t  allow  insert,
	update and delete on data.
It  allows  navigation  between  record  either	It  allows  only  forward  navigation  that  also
forward or backward.	only to immediate next record.
It can contain multiple records.	It can contain only one record at a time.
All  the  data  of  a  dataset  will  be  on  client	All  the  data  of  a  DataReader  will  be  on
system.	server and one record at a time is retrieved
	and stored in datareader when you call the
	Read() method of datareader.

Boxing VsUnBoxing And Value Types Vs Reference Types

What is Boxing and UnBoxing in .Net

Converting value type to reference type is called boxing and converting reference type to value type is called as unboxing.
Eg:

int i=1;

object o=i;  --> Boxing

int j=(int) o;  -->unboxing  

   
Boxing and unboxing are only the technical terms for type casting from value type to reference type and vice versa.

Access to value types will be fast when compared to reference types. Because they directly contain the value and no need to refer another memory location.

It is recommended to avoid boxing and unboxing in the program wherever it is possible. Because these operations take time and will affect the performance of the application.

	Boxing	Unboxing
Definition:	Boxing is the process of converting a value type to the reference type.	Unboxing is the process of converting a reference type to value type .
Type of Conversion:	Implicit Conversion	Explicit Conversion
Example:	int i = 221; object obj = i; //boxing	object obj = 213; i = (int)obj ; // unboxing

What are Value Types and Reference Types in .Net

           According to MSDN,   A data type is a value type if it holds the data within its own memory allocation. A reference type contains a pointer to another memory location that holds the data.


All the data types in .net are classified in to value types and reference types.  

• The data types whose values are directly stored in stack memory area are called as value types and the data types whose values are stored in heap memory area and its address is stored in a variable in stack memory area are called as reference types.

• Among all built in data types of .net string and object are reference type and all other data types are value types.

• Among user defined data types, class, interface, delegate and arrays are reference type while structure and enumeration are value type.

Value Types----------------
Value types include the following:

• All numeric data types
• Boolean, Char, and Date
• All structures, even if their members are reference types
• Enumerations, since their underlying type is always SByte, Short, Integer, Long, Byte, UShort, UInteger, or ULong

Reference Types
----------------------Reference types include the following:

• String
• All arrays, even if their elements are value types
• Class types
• Delegates

XML String To SQL Server

How to read data from XML String and insert in to table in SQL Server

In this Article, i will explain you , how to insert the below xml string into SQL Server Database table.

<Customers>
  <customer>
    <ID>111589</ID>
    <FirstName>name1</FirstName>
    <LastName>Lname1</LastName>
    <Company>ABC</Company>
  </customer>
  <customer>
    <ID>12345</ID>
    <FirstName>name2</FirstName>
    <LastName>Lname2</LastName>
    <Company>ABC</Company>
  </customer>
  <customer>
    <ID>14567</ID>
    <FirstName>name3</FirstName>
    <LastName>Lname3</LastName>
    <Company>DEF</Company>
  </customer>
</Customers>

Step1. Create a Stored procedure in Sql server which takes one input parameter and returns “Success” /”Failure”

Create PROCEDURE [dbo].[SP_Insert_MultipleRows] ( @xmlData XML , @retValue varchar(20) OUTPUT)

ASBEGINSET @retValue='Failed';

INSERT INTO  [Employee]([id],[firstName],[lastName],[company])

SELECTCOALESCE([Table].[Column].value('ID[1]', 'int'),0) as 'ID',[Table].[Column].value('FirstName [1]', 'varchar(20)') as ' FirstName ',[Table].[Column].value(' LastName[1]', 'varchar(20)') as ' LastName',[Table].[Column].value(' Company [1]', 'varchar(50)') as ' Company'

 FROM @xmlData.nodes('/ Customers / customer') as [Table]([Column])IF(@@ROWCOUNT > 0 )  SET @retValue='SUCCESS';

END

Step2.Execute the procedure(F5)

Step3. Testing - Execute the above stored procedure by passing the xml string

Declare @retValue1 varchar(50);Declare @XmlStr XML;SET @XmlStr='<Customers> <customer>    <ID>111589</ID>    <FirstName>name1</FirstName>    <LastName>Lname1</LastName>    <Company>ABC</Company>  </customer>  <customer>    <ID>12345</ID>    <FirstName>name2</FirstName>    <LastName>Lname2</LastName>    <Company>ABC</Company>  </customer>  <customer>    <ID>14567</ID>    <FirstName>name3</FirstName>    <LastName>Lname3</LastName>    <Company>DEF</Company>  </customer></Customers>';

EXEC [SP_Insert_MultipleRows] @xmlData=@XmlStr,@retValue=@retValue1 OUTPUTprint @retValue1

Output

The output will be in tabular format as below
Execute below command in sql server database

Select * from Employee

Working With DataTable

DataTable

This object as its name points is the logical representation of a table, it supports column's types, default values, etc.
Usually when you run a query you get the results in a DataTable or into a DataSet which is no other thing that a set of DataTables.

Datatables can be modified in runtime for your code advantage, good cases for using datatable object processing instead of queries? when you need to recalculate over the same large data sets in order to provide different outputs, as in billing, costing and rating.
Let's suppose you have a set of items, and that you have different clients and providers for them, and you want to cost all the items plus run all the pricing formulas to every single one, your item's list is just as it comes from the database, for you the good thing would be to add new columns, one for each specific pricing plan and run all the calculations row by row registering that numbers, and then at the end of the process just deliver the proper pricing lists by client type using Views of the data you have just calculated.
Now imagine you have a hundred thousand items and thirty different client lists, do the math, faster, easier, cleaner to do it in memory at once than doing all the query-process-deliver for every client type and pricing you have.
If you command Datatables you will command large data processing.

DataTable MSDN Reference page

DataColumn

Is just what you think it is, the column object inside the datatable, you need to command this in order of adding new columns, calculating and filtering.

Working the Datatable

Here some code to show you the inner workings on how to create a table, remember you can have it created by the results of a query.

  // Creating a DataTable
  public void createDataTable( ref DataTable dt )
  {

   dt = new DataTable("MyTable");
   addColumn( ref dt, "ID","System.Int32",0 );
   addColumn(ref dt, "Person", "System.String", "Roger Federer");
  
   // adding some records does not hurt
   addRandomRecords(ref dt, 10);

   // we give it the spot
   dataGridView1.DataSource = dt;
   dataGridView1.Refresh();
  }

  //
  // This method will help you to add columns to your datatable,
  // you only need to give the parameters properly as in :
  //
  // - a reference to the actual datatable
  // - The name of the column, which is important cause it will be referenced later
  // - a string with the data type, string being C# type as in : "System.String", "System.Int32", etc
  // - a default value which will be set any time a row is created
  //
  // This will turn up useful once you understand the basics on
  // working on tables program                atically

  public void addColumn(ref DataTable table, string columnName, string tipo, object defaultValue)
  {
   DataColumn auxColumn = new DataColumn(columnName, System.Type.GetType(tipo));
   auxColumn.DefaultValue = defaultValue;
   table.Columns.Add(auxColumn);
  }
 
  // a method to add some random records to the new table
  public void addRandomRecords( ref DataTable dt, int records )
  {
   String[] names = new String[] { "Roger", "Richard", "Ellito", "Lisa","Andy","Barbara","Philip", "Victoria" };
   String[] surNames = new String[] { "Griffin", "Simpsom", "Brown", "Black", "White", "Federer", "Smith" };

   Random rnd = new Random(DateTime.Now.Millisecond);

   for (int i = 0; i < records; i++)
   {

    // This is how ou create a row that duplicates a datatable's row
    // so when you want to create a new record you create one of the
    // datatable's own kind, then fill it your way
    // and finally add it to the datatable
    DataRow dtr = dt.NewRow();

    dtr[0] = rnd.Next(1,records*5);
    dtr[1] = names[rnd.Next(0, names.Length)] + " " + surNames[rnd.Next(0, surNames.Length)];

    dt.Rows.Add(dtr);
   }
  }

Insert Data in DataTable:

             DataTable dt = new DataTable("Emp");

            //Adding columns to table Emp
            DataColumn colEmpid = new DataColumn("Empid",typeof(System.Int32));
            DataColumn colName = new DataColumn("Name", typeof(System.String));
            DataColumn colDept = new DataColumn("Department",typeof(System.String));

            //Adding columns to datatable
            dt.Columns.AddRange(new DataColumn[] { colEmpid, colName, colDept });

         //Adding data
            dt.Rows.Add( 1000, "John Smith Brown", "Finance" );
            dt.Rows.Add( 1001, "Carry Brown", "Engineering" );
            dt.Rows.Add(1002, "Candle Pencil", "Marketing");
            dt.Rows.Add(1003, "Graham Bell", "Engineering");
            dt.Rows.Add(1004, "Peter Kevin", "Finance & Engineering");      
            
DataView

This is what you use for filtering and sorting purposes and you can do it without compromising the source DataTable and with the ease of SQL like sintax.
It works just like a Database's View, you give it an original dataset and then configure which data will you work with and how will it look like.

DataView MSDN Reference page


RowFilter

This is the mechanism the DataView provides in order to make the views filtereable and it uses a friendly TRANSACT-SQL like set of filtering op

RowFilter MSDN Reference page


Sorting and Filtering with the DataView

  // DataView is what you use to get a different view on the datatable
  // you can apply both, sorting and filtering there
  //
  public void sortUsingDataView(ref DataTable dt, string fields)
  {
   DataView dv = new DataView(dt);

   // in order to sort you only need to provide the columns to sort by
   // in a sql's order by fashion like this : ID, Person
   // which means sort by ID and then by person, you can use desc or
   // column numbers instead of names, just like in sql
   //
   dv.Sort = fields;  
   //

   // and of course you change the datagrid source to the view
   // cause the datatable keeps being the sa
   dataGridView1.DataSource = dv;
   dataGridView1.Refresh();
  }

  // Dataview filter,
  private void btFilterColumns_Click(object sender, EventArgs e)
  {

   DataView dv = new DataView( MyTable );

   // this does the trick, we use the filter entered in the text box
   // and apply it to the View
   dv.RowFilter = tbFilter.Text.Trim();

   // always refresh grid's datasource
   dataGridView1.DataSource = dv;
  }

Here we see how our created table looks like






And then we apply the filter






As you can see inside the red marking the RowFilter just takes the same syntax Transact-SQL from MS Sql Server.
So if you know how to work a WHERE clause in SQL you will probably have not problem in your row filtering.

As you can see the code is pretty simple, few lines, but don't let yourself to be deceived if you would have to think and research this it will have took time, getting to right and straight information is not easy, i know, i had to pull the pieces together one by one.

I hope you find this useful and have it in mind when you need to do that good old data process and reprocess kind of stuff, like when you have to calculate cube-like statiscs, such a the day by day, week by week, month by moth accumulators.

You can find the solution with the source code in the attachment.

Make us know if you liked this.