using System;
using System.Data.SqlClient;
using System.Data;

namespace AdapterSamp
{
	class DataAdapterSamp
	{
		static void Main(string[] args)
		{
			//Set the connection string for the database
			string connectionstring="Initial Catalog=NorthWind; Data Source=buildees;user id=sa;password=BaanIT00;";

			//Create Connection and open it
			System.Data.SqlClient.SqlConnection  conn = new SqlConnection(connectionstring);
			conn.Open ();

			//Create the command object
			SqlCommand comm = new SqlCommand();
			comm.Connection = conn;
			comm.CommandText = "Select employeeid, lastname, firstname, city from Employees";


			//Create an adapter object
			SqlDataAdapter adapter = new SqlDataAdapter("Select employeeid, lastname, firstname, city from Employees", connectionstring);
		
			//Create a dataset object and fill the values from Employees table	
			DataSet oDataSet = new DataSet();
			adapter.Fill(oDataSet, "Employees");
			
			//Print the records in XML format
			Console.WriteLine(oDataSet.GetXml());

			
			/************	Add a new record to the table	***************/
			//Create a new row
			DataRow oDataRow;
			oDataRow = oDataSet.Tables["Employees"].NewRow();

			//Set the filed values
			oDataRow["FirstName"] = "Lalitha";
			oDataRow["LastName"] = "Maheswaran";
			oDataSet.Tables["Employees"].Rows.Add(oDataRow);

			//Create the insert command object
			SqlCommand oInsertCmd = new SqlCommand();
			oInsertCmd.Connection = conn;
			oInsertCmd.CommandText = "Insert into employees( FirstName, LastName) values (@FirstName, @LastName)";
			
			//Declare the variables in the command
			oInsertCmd.Parameters.Add("@FirstName", System.Data.SqlDbType.NVarChar , 10, "FirstName");
			oInsertCmd.Parameters.Add("@LastName", System.Data.SqlDbType.NVarChar, 20, "LastName");

			//Set the insert command and update the table
			adapter.InsertCommand = oInsertCmd;
		    adapter.Update(oDataSet, "Employees");

			//Create a datatable to display the records in the table
			//Note: Refill the dataset so that any changes made to the tables are got
			adapter.Fill(oDataSet, "Employees");
			DataTable oTable = oDataSet.Tables["Employees"];
			
			foreach (DataRow Row in oTable.Rows)
			{
				Console.WriteLine(Row[0] + "  " + Row[1]);
			}
		}
	}
}