adoNET.txt

INTRO
	ADO.NET is a set of classes (Framework) that can be used to interact with data sources like Databases and XML files. 
	ADO stands for Microsoft ActiveX Data Objects. 
	steps to interact with data
		1. create database connection
		2. prepare an SQL command
		3a. open database connection
		3b. execute the Command and read incoming data, if any - <provider>DataReader (Provides a way of reading a forward-only stream of rows from database)
		3c. close database connection - connectObject.Close()
		3. manipulate data(alternate to 3rd step) - <provider>DataAdapter (Represents a set of data commands like update,delete and also fills System.Data.DataSet to work off-line)
	System.Data.SqlClient - data provider for SQL Server
	System.Data.OracleClient - data provider for Oracle Server
	System.Data.Odbc - Open Database Connectivity. Provides access only to relational databases
	System.Data.OleDb - Object Linking and Embedding Database. Provide access to all data regardless of its format or location. Full access to ODBC data sources and ODBC drivers
	System.Data.DataSet - Represents an in-memory cache of data store, that can store tables, just like a database. DataSet is the collection of the DataTables
	System.Data.DataTable - Represents a table of in-memory data. this has rows and columns
	ADO.NET offers two data access modes
		Connection oriented data access - SqlDataReader provide connected data access
		Disconnected data access - SqlDataAdapter and DataSet objects together provide disconnected data access.
	common methods of SqlCommand
		SqlDataReader ExecuteReader - Use when the T-SQL statement returns more than a single value. For example, if the query returns rows of data.
		int ExecuteNonQuery - Use when you want to perform an Insert, Update or Delete operation. This returns number of rows affected
		object ExecuteScalar - Use when the query returns a single(scalar) value like no-of-rows. Returns first column of the first row in the result set of the query
	
SQLCONNECTION 
	connection string - string of key/value pair
		data source=.\SQLEXPRESS;initial catalog=SampleDB;integrated security=SSPI  - connection object with windows authentication
		data source=.\SQLEXPRESS; initial catalog=SampleDB; user id=MyUserName; password=MyPassword  - connection object with SQL Server authentication
	2 ways to close connection
		put connectObj.Close(); in the finally block
		using(connectObj){ .....connectObj.Open();....}
		
SQL INJECTION ATTACK
	attack example
		text = "phone'; Delete from tblProductInventory --";  //-- is used from in-line commenting
		command = "Select * from tblProductInventory where ProductName like '" + text + "%'";
	ways to prevent sql injection attack
		using parametrized queries
			using (SqlConnection con = new SqlConnection(CS))
			{
				string Command = "Select * from tblProductInventory where ProductName like @ProductName";  // @ProductName is the parameter
				SqlCommand cmd = new SqlCommand(Command, con);
				cmd.Parameters.AddWithValue("@ProductName", TextBox1.Text + "%");  // Provide the value for the parameter
				con.Open();
				GridView1.DataSource = cmd.ExecuteReader();
				GridView1.DataBind();
			} 
		using stored procedure
			stored procedure
				Create Procedure spGetProductsByName
				@ProductName nvarchar(50)
				as
				Begin
					Select * from tblProductInventory 
					where ProductName like @ProductName + '%'
				End
			ado.net code
				using (SqlConnection con = new SqlConnection(CS))
				{
					SqlCommand cmd = new SqlCommand("spGetProductsByName", con);
					cmd.CommandType = System.Data.CommandType.StoredProcedure;  // Specify that the T-SQL command is a stored procedure
					cmd.Parameters.AddWithValue("@ProductName", TextBox1.Text + "%");  // Associate the parameter and it's value with the command object
					con.Open();
					GridView1.DataSource = cmd.ExecuteReader();
					GridView1.DataBind();
				}

STORED PROCEDURE WITH OUTPUT PARAMETER
	stored procedure
		Create Procedure spAddEmployee  
		@Name nvarchar(50),  
		@Gender nvarchar(20),  
		@Salary int,  
		@EmployeeId int Out  
		as  
		Begin  
			Insert into tblEmployees values(@Name, @Gender, @Salary)  
			Select @EmployeeId = SCOPE_IDENTITY()  
		End 
	ado.net code
		using (SqlConnection con = new SqlConnection(ConnectionString))
		{
			SqlCommand cmd = new SqlCommand("spAddEmployee", con);
			cmd.CommandType = System.Data.CommandType.StoredProcedure;
			//Add the input parameters to the command object
			cmd.Parameters.AddWithValue("@Name", txtEmployeeName.Text);
			cmd.Parameters.AddWithValue("@Gender", ddlGender.SelectedValue);
			cmd.Parameters.AddWithValue("@Salary", txtSalary.Text);
			//Add the output parameter to the command object
			SqlParameter outPutParameter = new SqlParameter();
			outPutParameter.ParameterName = "@EmployeeId";
			outPutParameter.SqlDbType = System.Data.SqlDbType.Int;
			outPutParameter.Direction = System.Data.ParameterDirection.Output;
			cmd.Parameters.Add(outPutParameter);
			//Open the connection and execute the query
			con.Open();
			cmd.ExecuteNonQuery();				
			//Retrieve the value of the output parameter
			string EmployeeId = outPutParameter.Value.ToString();
			lblMessage.Text = "Employee Id = " + EmployeeId;
		}
		
SQLDATAREADER
	SqlDataReader is read-only and forward only, meaning once you read a record and go to the next record, there is no way to go back to the previous record.
	it is connection oriented i.e. it requires an active and open connection to the data source
	cannot create an instance of SqlDataReader using the new operator. SqlDataReader has no constructor
	close SqlDataReader before closing the sql connection
	identity
		[string columnName] - Gets the value of the specified column in its native format given the column name.
		[int index] - Gets the value of the specified column in its native format given the column ordinal.
	methods
		Read() - returns boolean. Advances the read pointer to the next record/row
		NextResult() - returns boolean. Advances the pointer to the next result set. Useful when executing multiple command as one ("select * from tbl1; select * from tbl2;")
		
SQLDATAADAPTER
	SqlDataAdapter and DataSet provides us with disconnected data access model.
	Fill() method 
		this method does most of the work. 
		It opens the connection to the database, executes the sql command, fills the dataset with the data, and closes the connection. 
		Opening and closing connections is handled for us. The connection is kept open only as long as it is needed
	example
		using (SqlConnection connection = new SqlConnection(ConnectionString))
		{   
			SqlDataAdapter dataAdapter = new SqlDataAdapter("select * from tblProductInventory", connection);  // Specify the command and the connection
			DataSet dataset = new DataSet();
			dataAdapter.Fill(dataset);
			GridView1.DataSource = dataset;
			GridView1.DataBind();
			dataAdapter = new SqlDataAdapter("spGetProductInventoryById", connection);  // Specify stored procedure and connection
			dataAdapter.SelectCommand.CommandType = CommandType.StoredProcedure;  // Specify the command type is an SP
			dataAdapter.SelectCommand.Parameters.AddWithValue("@ProductId", 1);
			dataAdapter.Fill(dataset);
			GridView2.DataSource = dataset;
			GridView2.DataBind();
		}
	to invoke Update(),not along with SqlCommandBuilder, manually create sql operation command
		SqlDataAdapter dataAdapter = new SqlDataAdapter(selectQuery, connection);
		// Update command to update database table
		string strUpdateCommand = "Update tblStudents set Name = @Name, Gender = @Gender, TotalMarks = @TotalMarks where Id = @Id";
		SqlCommand updateCommand = new SqlCommand(strUpdateCommand, connection);
		updateCommand.Parameters.Add("@Name", SqlDbType.NVarChar, 50, "Name");
		updateCommand.Parameters.Add("@Gender", SqlDbType.NVarChar, 20, "Gender");
		updateCommand.Parameters.Add("@TotalMarks", SqlDbType.Int, 0, "TotalMarks");
		updateCommand.Parameters.Add("@Id", SqlDbType.Int, 0, "Id");
		dataAdapter.UpdateCommand = updateCommand;
		// Delete command to delete data from database table
		SqlCommand deleteCommand = new SqlCommand("Delete from tblStudents where Id = @Id", connection);
		deleteCommand.Parameters.Add("@Id", SqlDbType.Int, 0, "Id");
		dataAdapter.DeleteCommand = deleteCommand;
		// Update the underlying database table - Students
		dataAdapter.Update(<DataSet>, "Students");
		lblStatus.Text = "Database table updated";

SQLCOMMANDBUILDER
	SqlCommandBuilder automatically generates INSERT, UPDATE and DELETE sql statements based on the SELECT statement passed to DataAdapter
	steps to update database
		 dataAdapter.SelectCommand = new SqlCommand("select * from table1", con); //set the SelectCommand of the DataAdapter
		 SqlCommandBuilder builder = new SqlCommandBuilder(dataAdapter); //this code generate insert, update and delete sql statement for dataAdapter
		 int rowsUpdated = dataAdapter.Update(<DS>, "Students"); // Update method will execute insert, update and delete operation for each inserted, updated and deleted row in DS
	if you want to see the auto-generated INSERT, UPDATE, and DELETE T-SQL statements,
		builder.GetInsertCommand().CommandText;
		builder.GetUpdateCommand().CommandText;
		builder.GetDeleteCommand().CommandText; 
	possible reasons for update method not to work
		SqlCommandBuilder object not associated with SqlDataAdapter object
		The SelectCommand that is associated with SqlDataAdapter, does not return at-least one primary key or unique column
		
DATASET
	when more than one table is passed in DataSet, you can use a desired table as
		GridViewProducts.DataSource = dataset.Tables[0]; or GridViewProducts.DataSource = dataset.Tables["Table"];
	we can change the name of the table
		dataset.Tables[0].TableName = "Products";
	ADO.NET manages rows in tables using row states and versions. Row state indicates the status of a row; row versions maintain the values stored in a row as it is modified
	DataRowState
		Unchanged - No changes have been made since the last call to AcceptChanges or since the row was created by DataAdapter.Fill.
		Added - The row has been added to the table, but AcceptChanges has not been called.
		Modified - Some element of the row has been changed.
		Deleted - The row has been deleted from a table, and AcceptChanges has not been called.
	DataRowVersion
		Current - The current values for the row. This row version does not exist for rows with a RowState of Deleted.
		Original - The original values for the row. This row version does not exist for rows with a RowState of Added.
	When AcceptChanges() is invoked RowState property of each DataRow changes. Added and Modified rows become Unchanged, and Deleted rows are removed
	If we invoke DataAdapter.Update(), based on the RowState, respective INSERT, UPDATE and DELETE commands are executed on database table and AcceptChanges() is called automatically. 
	When RejectChanges() is invoked RowState property of each DataRow changes. Added rows are removed. Modified and Deleted rows becomes Unchanged.
	Both AcceptChanges() and RejectChanges() methods can be invoked at the following levels
		At the DataTable level - When invoked at the DataTable level, they get called automatically on each DataRow within each DataTable.
		At the DataSet level - When invoked at the DataSet level, they get called automatically on each DataTable with in the DataSet, and on each DataRow within each DataTable.
		At the DataRow level - Gets called only for the row, on which it is invoked
								
SQLBULKCOPY
	SqlBulkCopy class is used to bulk copy data from different data sources(which can be loaded to DataTable instance or read with IDataReader instance) to SQL Server database
	loading xml data into sql server table using sqlbulkcopy (using DataTable instance)
		DataSet ds = new DataSet();
		ds.ReadXml(Server.MapPath("~/Data.xml"));
		DataTable dtDept = ds.Tables["Department"];
		con.Open();
		using (SqlBulkCopy bc = new SqlBulkCopy(con))
		{
			bc.DestinationTableName = "Departments";
			bc.ColumnMappings.Add("ID", "ID");
			bc.ColumnMappings.Add("Name", "Name");
			bc.ColumnMappings.Add("Location", "Location");
			bc.WriteToServer(dtDept);
		}
	BatchSize property - specifies the number of rows in a batch that will be copied to the destination table. By default batch size is one row
	NotifyAfter property - defines the number of rows to be processed before raising SqlRowsCopied event.
	SqlRowsCopied event - raised every time the number of rows specified by NotifyAfter property are processed. This event is useful for reporting the progress of the data transfer.
	
TRANSACTION
	A Transaction ensures that either all of the database operations succeed or all of them fail. 
	This means the job is never half done, either all of it is done or nothing is done. 
	example
		// Begin a transaction. The connection needs to be open before we begin a transaction
		con.Open();
		SqlTransaction transaction = con.BeginTransaction();
		try
		{
			SqlCommand cmd = new SqlCommand("Update Accounts set Balance = Balance - 10 where AccountNumber = 'A1'", con, transaction);
			cmd.ExecuteNonQuery();
			cmd = new SqlCommand("Update Accounts set Balance = Balance + 10 where AccountNumber = 'A2'", con, transaction);
			cmd.ExecuteNonQuery();
			// If all goes well commit the transaction
			transaction.Commit();
		}
		catch
		{
			// If anything goes wrong, rollback the transaction
			transaction.Rollback();
		}