005 ADO.Net DataSet

ADO.Net DataSet

DataSet is a class under the. Net Framework. DataSet is in memory representation , can hold large data , copied from database. DataSet contain mainly two object

1) DataTableCollection
2) DataRelationCollection  

DataTableCollection is a collection of tables , maybe or may not be with data . DataRelationCollection  is  a collection of Schema of the tables and relationship between them. Foreign key, Primary key information of tables are store in the data collection relations. DataSet hold data in XML format. DataAdopter execute one or more select command , fetch data from database and fill it to the DataSet . DataSet hold a copy of this data with its schema. DataSet never connected with database, it is disconnected in nature. DataSet are Programmable also, tables and tables collections with or without data can be added to the data set.

Some commonly used of data set features are

Constructor 

• DataSet : Initialise new instance of a DataSet . 

 Properties

• DataSetName : Get or set name of the DataSet .  
• HasErrors : Indicator , Get or Set at any tables in the data set has error or not. 
• Tables: Get the collection of tables in a DataSet . 

Method 

• AcceptChanges : Commit all changes in a DataSet when last loaded. 
• Clear: Removes all tables and rows from a DataSet . 
• Copy: Copy DataSet with tables and schema and with and without data. 
• Dispose: Dispose a DataSet and release all resources used by the DataSet.
• GetXml : Return XML of the data from the DataSet . 
• HasChanges : Indicate any changes in data after the Lord has made or not. 
• Merge: Add datatable in the DataSet . 
• ReadXml : Read XML form a file and load it to DataSet . 
• RejectChanges : Changes after the latest it has been loaded. 
• WriteXml : DataSet inner XML is written in a file.

Here are some widely used functionality of DataSet

1)Add table with data to DataSet 

DataSet ds = new DataSet();

DataTable dt = new DataTable();

dt.Columns.Add("Name");

dt.Columns.Add("RollNumber");

DataRow nr1 = dt.NewRow();

nr1["Name"] = "John1";

nr1["RollNumber"] = 500;

dt.Rows.Add(nr1);

DataRow nr2 = dt.NewRow();

nr2["Name"] = "John2";

nr2["RollNumber"] = 600;

dt.Rows.Add(nr2);

DataRow nr3 = dt.NewRow();

nr3["Name"] = "John3";

nr3["RollNumber"] = 700;

dt.Rows.Add(nr3);

ds.Merge(dt);

DataTable dt2 = new DataTable();

dt2.Columns.Add("Address");

dt2.Columns.Add("ZIP");

DataRow nr4 = dt2.NewRow();

nr4["Address"] = "3,Kolkata";

nr4["ZIP"] = 500;

dt2.Rows.Add(nr4);

DataRow nr5 = dt2.NewRow();

nr5["Address"] = "4,Mumbai";

nr5["ZIP"] = 600;

dt2.Rows.Add(nr5);

DataRow nr6 = dt2.NewRow();

nr6["Address"] = "5,Bombay";

nr6["ZIP"] = 700;

dt2.Rows.Add(nr6);

ds.Merge(dt2);

 2)DataSet Tables Loop

DataSet ds = new DataSet();

DataTable dt = new DataTable("MyFirstTable");

dt.Columns.Add("Name");

dt.Columns.Add("RollNumber");

DataRow nr1 = dt.NewRow();

nr1["Name"] = "John1";

nr1["RollNumber"] = 500;

dt.Rows.Add(nr1);

DataRow nr2 = dt.NewRow();

nr2["Name"] = "John2";

nr2["RollNumber"] = 600;

dt.Rows.Add(nr2);

DataRow nr3 = dt.NewRow();

nr3["Name"] = "John3";

nr3["RollNumber"] = 700;

dt.Rows.Add(nr3);

ds.Merge(dt);

DataTable dt2 = new DataTable("MySecondTable");

dt2.Columns.Add("Address");

dt2.Columns.Add("ZIP");

DataRow nr4 = dt2.NewRow();

nr4["Address"] = "3,Kolkata";

nr4["ZIP"] = 500;

dt2.Rows.Add(nr4);

DataRow nr5 = dt2.NewRow();

nr5["Address"] = "4,Mumbai";

nr5["ZIP"] = 600;

dt2.Rows.Add(nr5);

DataRow nr6 = dt2.NewRow();

nr6["Address"] = "5,Bombay";

nr6["ZIP"] = 700;

dt2.Rows.Add(nr6);

ds.Merge(dt2);

//Loop throught DataSet Tables

for (int i = 0; i < ds.Tables.Count; i++)

{

Console.WriteLine(ds.Tables[i].TableName.ToString());

}

OutPut :
MyFirstTable
MySecondTable 

3)Load XML  to DataSet

string xml = @"John150";

DataSet ds = new DataSet();

ds.ReadXml(System.Xml.XmlReader.Create(new System.IO.StringReader(xml)));

for (int i = 0; i < ds.Tables.Count; i++)

{

Console.WriteLine(ds.Tables[i].Rows[0]["Name"].ToString());

Console.WriteLine(ds.Tables[i].Rows[0]["RollNumber"].ToString());

}

Output : 
John
150

4)Write XML from DataSet

string xml = @"John150";

DataSet ds = new DataSet();

ds.ReadXml(System.Xml.XmlReader.Create(new System.IO.StringReader(xml)));

System.IO.StringWriter sw = new System.IO.StringWriter();

ds.WriteXml(sw);

string result = sw.ToString();

Console.Write(result);

Output : 

 
    John
    150
 

5)AcceptChanges Example

DataSet ds = new DataSet();

DataTable dt = new DataTable("MyFirstTable");

dt.Columns.Add("Name");

dt.Columns.Add("RollNumber");

DataRow nr1 = dt.NewRow();

nr1["Name"] = "John1";

nr1["RollNumber"] = 500;

dt.Rows.Add(nr1);

DataRow nr2 = dt.NewRow();

nr2["Name"] = "John2";

nr2["RollNumber"] = 600;

dt.Rows.Add(nr2);

DataRow nr3 = dt.NewRow();

nr3["Name"] = "John3";

nr3["RollNumber"] = 700;

dt.Rows.Add(nr3);

ds.Merge(dt);

DataTable dt2 = new DataTable("MySecondTable");

dt2.Columns.Add("Address");

dt2.Columns.Add("ZIP");

DataRow nr4 = dt2.NewRow();

nr4["Address"] = "3,Kolkata";

nr4["ZIP"] = 500;

dt2.Rows.Add(nr4);

DataRow nr5 = dt2.NewRow();

nr5["Address"] = "4,Mumbai";

nr5["ZIP"] = 600;

dt2.Rows.Add(nr5);

DataRow nr6 = dt2.NewRow();

nr6["Address"] = "5,Bombay";

nr6["ZIP"] = 700;

dt2.Rows.Add(nr6);

ds.Merge(dt2);

ds.AcceptChanges();

//Loop to get data

for (int i = 0; i < ds.Tables.Count; i++)

{

    for (int l = 0; l < ds.Tables[i].Rows.Count; l++)

    {

     Console.WriteLine(ds.Tables[i].Rows[l][0].ToString());

   }

}

Output :
John1
John2
John3
3,Kolkata4,
Mumbai5,
Bombay 



 6)RejectChanges Example

 

DataSet ds = new DataSet();

DataTable dt = new DataTable("MyFirstTable");

dt.Columns.Add("Name");

dt.Columns.Add("RollNumber");

DataRow nr1 = dt.NewRow();

nr1["Name"] = "John1";

nr1["RollNumber"] = 500;

dt.Rows.Add(nr1);

DataRow nr2 = dt.NewRow();

nr2["Name"] = "John2";

nr2["RollNumber"] = 600;

dt.Rows.Add(nr2);

DataRow nr3 = dt.NewRow();

nr3["Name"] = "John3";

nr3["RollNumber"] = 700;

dt.Rows.Add(nr3);

ds.Merge(dt);

DataTable dt2 = new DataTable("MySecondTable");

dt2.Columns.Add("Address");

dt2.Columns.Add("ZIP");

DataRow nr4 = dt2.NewRow();

nr4["Address"] = "3,Kolkata";

nr4["ZIP"] = 500;

dt2.Rows.Add(nr4);

DataRow nr5 = dt2.NewRow();

nr5["Address"] = "4,Mumbai";

nr5["ZIP"] = 600;

dt2.Rows.Add(nr5);

DataRow nr6 = dt2.NewRow();

nr6["Address"] = "5,Bombay";

nr6["ZIP"] = 700;

dt2.Rows.Add(nr6);

ds.Merge(dt2);

ds.RejectChanges();

//Loop to get data

for (int i = 0; i < ds.Tables.Count; i++)

{

           for (int l = 0; l < ds.Tables[i].Rows.Count; l++)

            {

                 Response.Write(ds.Tables[i].Rows[l][0].ToString());

            }

}

 7)Populate DataSet with SqlDataAdopter

DataSet ds = new DataSet();

string connection = "Data Source= Your Server; initial catalog=Your Database Name;Connect Timeout=6000; User ID= Your User id;Password=Your Password;";

string qry = "SELECT top 100 * from tbl_my_Table";

SqlDataAdapter da = new SqlDataAdapter(qry, connection);

da.Fill(ds);

001 ADO.Net Introduction

ADO.Net Introduction
                            ADO.NET is a object library under . Net Framework. ADO.NET help to interact with different data source and different type of Database. The main feature of ADO.NET is disconnected in nature and hold data in XML format. As disconnected in nature , it is faster than ADODB/DAO/RDO. When a client request for a data, ADO.NET read the data from the database and release the database, the data is supplied to the client . The database have no overburden as there is no connection established buy ADO.NET, database is completely free. This makes a database performance faster and deliver huge operation continuously.

                      ADO.NET follow a common way to interact with the database. Different kind of database can be handle buy it. Other than SQL server, Access , Oracle, text file, Excel file also be handled by ADO.NET . Data provider are the class to interact with different kind of database. Below is the list of some commonly used to provide. 

Data Provider	Library	Usage
ODBC Data Provider	System.Data.Odbc	ODBC supported older database
OleDb Data Provider	System.Data.OleDb	Acess ,Execl
Oracle Data Provider	System.Data.Oracle	Oracle Database
SQL Data Provider	System.Data.Sql	Microsoft Sql Server

To work with data, ADO.NET has several object . Data Provider object ,DataSet object , Connection object , Command object , DataReader object . 

Data Provider object : Data Provider is responsible for connect database and firing SQL command to retrieve the record. Recorded retrieve by data provider is transferred to either DataSet or DataReader. 

DataSet object : Data retrieve by Data provider store in DataSet in a form of tables collections. Data store in DataSet is in XML format. DataSet not only Store data, it also store the schema object ,called data relation object. DataSet contain Collection of Data Relation object in it . Tables and foreign keys relationship are stored in this objects.DataSet comes under the namespace System.Data.

Connection object : Connection object is responsible for connecting database. Identifying database server name, user ID, password and also physical file is connected. There are several connection classes in.Net framework. For SQL server it is SQL connection object comes under Sysrem.Data.SqlClient. For Oledb , connections object comes under Sytem.Data.Oledb. Below are the example.

   conn = @"provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + filePath+ ";Extended Properties='Excel 8.0;HRD=Yes;IMEX=1';";

OleDbConnection con = new OleDbConnection(conn);

"Data Source= Server IP; initial catalog=Your Server Name;Connect Timeout=6000; User ID=User id;Password=Password;"

SqlConnection objCN = new SqlConnection();

Command object :Command is the object in ADO.NET to execute an SQL in the  database. The command used for data manipulation for insert /update / delete or fetch operation. A comment object can be execute in a database directly or it can be through DataAdopter . On executing  command , if you are executing directly , it will give you the number of rows affected and if you are fetching data and executing through data DataAdopter , DataAdopter later fill the data to the DataSet.

DaraReader object :  DataReader is used to read the data. It is connection oriented and forward only. When ExecuteReader command is fired, DataReader get the stream of data to read. More than one set can be read by the DataReader . It is used to read the data only, it cannot manipulate data with the database. For SQL server DataReader comes under the System.Data.SqlClient.SqlDataReader.

DataAdapter Object : DataAdapter do a communication between data source and DataSet. DataAdapter  is responsible to fetch data from source and fill it in the DataSet. DataAdapter receive a parameter , connection string , to identify the source database, another parameter command . Command is used for data manipulation in database. Another operation to update DataBase , if any changes made to the DataSet , DataAdapter update original data ( AccesptChanges ) to the database.

047 C# Attribute

Attribute is a special type of tag top of an element to convey some information to run time.Attribute add meta data to the  program of element. .Net by default content meta data in the Assembly. You can add additional metadata with the help of attribute. The declarative tag attribute is written at the top of the element within opening and closing braket([..]) . Comments , description can also be added with attributes. Attribute are two to type

1. Predefined 
2. Customs

. Net Framework have Predefined attributes 

1. AttributeUsage 
2. Conditional 
3. Obsolete

 

AttributeUsage
 
AttributeUsage : The AttributeUsage, specify the type on whom attribute will be applicable. List of type can be added with (|) operator or all can we mention if you want to apply all element. AttributeUsage comes under System.AttributeUsage class.AttributeUsage can be apply on class, structure ,Method,Assembly,Event ect.

AllowMultiple : This property tell the computer compiler that attribute is used for multi use . AllowMultiple received Boolean value true or false. The default is false , that means the multi property is false if not specified that is for single use.


Inherited : Inherited is a attribute which is used to tell compiler that attribute will be available during the inheritance. If a parent is declared with the attribute child will get this attribute or not. default property is false.

 

using System;

[AttributeUsage(AttributeTargets.Class | 

 AttributeTargets.Struct |  

 AttributeTargets.Method, 

 AllowMultiple = true, 

 Inherited = false)]

public class MyStudentClass : Attribute

{

      static void Main(string[] args)

      {

          Console.ReadKey();

       }

}

Conditional: Conditional attribute is used very rare really. It use processor directives define or not. Processor directories define at the top of the program, with symbol #. Processor definition determine the execution of conditional  attribute.

#define LEARN

#undef PROGRAM

using System;

using System.Diagnostics;

class Program

{

       static void Main()

      {

       Example1();

       Example2();

     }

     [Conditional("LEARN")]

     static void Example1()

    {

          Console.WriteLine("LEARN is defined");

          Console.ReadKey();

    }

    [Conditional("PROGRAM")]

     static void Example2()

     {

       Console.WriteLine("PROGRAM is defined");

       Console.ReadKey();

    }

}

Output :
LEARN is defined

Obsolete:Obsolete attribute used to generate compile time warning.When up gradation or version changed , software method become unusable , then a attribute is added to the method. It prevent an obsolete method to call and execute. An information message also can be attached with the absolute attribute which inform the new method name to inform the developer. The first parameter of obsolete attribute is message and the second parameter iserror. If you iserror  as true. The compiler will generate an error.

using System;

class MyExampleClass

{

          [Obsolete("Method Example1 is no more is Use, Use method Example2", true)]

          public void Example1()

          {

                 Console.WriteLine("Execute Example1");

          }

          public void Example2()

         {

                 Console.WriteLine("Execute Example2");

         }

}

public class Programme

{

         static void Main(string[] args)

        {

              MyExampleClass obj = new MyExampleClass();

              obj.Example1();

              obj.Example2();

              Console.ReadKey();

         }

}

//Error
'MyExampleClass.Example1()' is obsolete: 'Method Example1 is no more is Use, Use method Example2'

Creating Custom Attributes

You can create your own custom attribute. You need to declare the class first, you need to inherit system.attribute. This class can be apply to target class.

using System;

[System.AttributeUsage(System.AttributeTargets.Class |

System.AttributeTargets.Struct)

]

public class MyAttribute : System.Attribute

{

       private string Param1;

       private double Param2;

      public MyAttribute(string Verion_Name, double Verion_Version)

      {

         this.Param1 = Verion_Name;

         this.Param2 = Verion_Version;

     }

}

[MyAttribute("C# Tutorial",1.5)]

class MyClass1

{

}

[MyAttribute("C# Tutorial 2010", 2.0)],

[MyAttribute("C# Tutorial 2011", 2.5)]

class MyClass2

{

}

How to read the Attribute of a class : Attribute is of a class can be read with the help of reflection. Reflection help you get the typeinfo of the class.Once typeinfo  is available,attribute can be read. There are two type of method to get the  attribute
GetCustom attribute
Obsolete attribute
/*example pending**************/