ado.net
DESCRIPTION
training Ado.netTRANSCRIPT
What is ADO.NET - Part 1What is ADO.NET?ADO.NET is not a different technology. In simple terms, you can think of ADO.NET, as a set of classes (Framework), that can be used to interact with data sources like Databases and XML files. This data can, then be consumed in any .NET application. ADO stands for Microsoft ActiveX Data Objects.
The following are, a few of the different types of .NET applications that use ADO.NET to connect to a database, execute commands, and retrieve data.ASP.NET Web ApplicationsWindows ApplicationsConsole Applications
What are .NET Data Providers?Databases only understand SQL. If a .NET application (Web, Windows, Console etc..) has to retrieve data, then the application needs to1. Connect to the Database2. Prepare an SQL Command3. Execute the Command4. Retrieve the results and display in the application
Sample ADO.NET code to connect to SQL Server Database and retrieve data. Notice that we are using SQLConnection, SQLCommand and SQLDataReader classes . All the objects are prefixed with the word SQL. All these classes are present inSystem.Data.SqlClient namespace. So, we can say that the .NET data provider for SQL Server is System.Data.SqlClient.SqlConnection con = new SqlConnection("data source=.; database=Sample; integrated security=SSPI");SqlCommand cmd = new SqlCommand("Select * from tblProduct", con);con.Open();SqlDataReader rdr = cmd.ExecuteReader();GridView1.DataSource = rdr;GridView1.DataBind();con.Close();
Sample ADO.NET code to connect to Oracle Database and retrieve data. Notice that we are using OracleConnection, OracleCommand and OracleDataReader classes . All the objects are prefixed with the word Oracle. All these classes are present in System.Data.OracleClient namespace. So, we can say that the .NET data provider for Oracle is System.Data.OracleClient.OracleConnection con = new OracleConnection("Oracle Database Connection String");
OracleCommand cmd = new OracleCommand("Select * from tblProduct", con);con.Open();OracleDataReader rdr = cmd.ExecuteReader();GridView1.DataSource = rdr;GridView1.DataBind();con.Close();
If we want to connect to OLEDB datasources like Excel, Access etc, we can useOleDbConnection, OleDbCommand and OleDbDataReader classes. So, .NET data provider for OLEDB is System.Data.OleDb.
Different .NET Data ProvidersData Provider for SQL Server - System.Data.SqlClientData Provider for Oracle - System.Data.OracleClientData Provider for OLEDB - System.Data.OleDbData Provider for ODBC - System.Data.Odbc
Please note that, depending on the provider, the following ADO.NET objects have a different prefix1. Connection - SQLConnection, OracleConnection, OleDbConnection, OdbcConnection etc2. Command - SQLCommand, OracleCommand, OleDbCommand, OdbcCommand etc3. DataReader - SQLDataReader, OracleDataReader, OleDbDataReader, OdbcDataReader etc4. DataAdapter - SQLDataAdapter, OracleDataAdapter, OleDbDataAdapter, OdbcDataAdapter etc
The DataSet object is not provider specific. Once we connect to a Database, execute command, and retrieve data into .NET application. The data can then be stored in a DataSet and work independently of the database.
SQLConnection in ADO.NET - Part 2Suggested VideosPart 1 - What is ADO.NET
In this video we will learn about1. Instantiating a SqlConnection object2. Using the SqlConnection object3. Why is it important to close a database connection4. How to properly close a connection
In Part 1 of ADO.NET video series, we have had an introduction to ADO.NET. We also understood about the different .NET data providers. The key to understanding ADO.NET, is to understand about the following objects. 1. Connection2. Command3. DataReader4. DataAdapter5. DataSet
From Part 1, it should be clear that Connection, Command, DataReader andDataAdapter objects are providers specific and DataSet is provider independent. So, if we are working with SQL Server, we will be using SQLConnection, SqlCommand,SqlDataReader and SqlDataAdapter objects from System.Data.SqlClient namespace. On the other hand if we are working with Oracle database, then we will be usingOracleConnection, OracleCommand, OracleDataReader and OracleDataAdapterobjects from System.Data.OracleClient namespace. The same logic applies when working with OleDb and Odbc data sources.
If we understand how to work with one database, then we should be able to easily work with any other database. All we have to do is, change the provider specific string (Sql, Oracle, Oledb, Odbc) on the Connection, Command, DataReader and DataAdapter objects depending on the data source you are working with.
So, in the next few sessions of this video series we will be discussing about each of the ADO.NET objects(Connection, Command, DataReader, DataSet and DataAdapter). In this session we will be discussing about SqlConnection object. The concepts that we discuss here are applicable to all the .NET data providers.
The first thing that we will have to do, when working with databases is to create a connection object. There are 2 ways to create an instance of SqlConnection class as shown below.
Create an instance of SqlConnection class uwing the constructor that takes ConnectionString parameterSqlConnection connection = new SqlConnection("data source=.; database=SampleDB; integrated security=SSPI");
//First create an instance of SqlConnection class using the parameter-less constructor SqlConnection connection = new SqlConnection();//Then set the ConnectionString property of the connection objectconnection.ConnectionString = "data source=.; database=SampleDB; integrated security=SSPI";
The ConnectionString parameter is a string made up of Key/Value pairs that has the information required to create a connection object.
To create a connection object with windows authenticationstring ConnectionString = "data source=.; database=SampleDB; integrated security=SSPI";
To create a connection object with SQL Server authenticationstring ConnectionString = "data source=.; database=SampleDB; user id=MyUserName; password=MyPassword";
The "data source" is the name or IP Address of the SQL Server that we want to connect to. If you are working with a local instance of sql server, you can just specify DOT(.). If the server is on a network, then use Name or IP address.
Sample ADO.NET code that 1. Creates a connection2. The created connection object is then passed to the command object, so that the command object knows on which sql server connection to execute this command.3. Execute the command, and set the command results, as the data source for the gridview control.4. Call the DataBind() method5. Close the connection in the finally block. Connections are limited and are very valuable. Connections must be closed properly, for better performance and scalability.
Note: Connections should be opened as late as possible, and should be closed as early as possible.
protected void Page_Load(object sender, EventArgs e){ //Create the connection object
SqlConnection connection = new SqlConnection("data source=.; database=Sample_Test_DB; integrated security=SSPI");; try { // Pass the connection to the command object, so the command object knows on which // connection to execute the command SqlCommand cmd = new SqlCommand("Select * from tblProductInventory", connection); // Open the connection. Otherwise you get a runtime error. An open connection is // required to execute the command connection.Open(); GridView1.DataSource = cmd.ExecuteReader(); GridView1.DataBind(); } catch (Exception ex) { // Handle Exceptions, if any } finally { // The finally block is guarenteed to execute even if there is an exception. // This ensures connections are always properly closed. connection.Close(); }}
We can also use "using" statement to properly close the connection as shown below. We don't have to explicitly call Close() method, when using is used. The connection will be automatically closed for us.protected void Page_Load(object sender, EventArgs e){ using (SqlConnection connection = new SqlConnection("data source=.; database=Sample_Test_DB; integrated security=SSPI")) { SqlCommand cmd = new SqlCommand("Select * from tblProductInventory", connection); connection.Open(); GridView1.DataSource = cmd.ExecuteReader(); GridView1.DataBind(); }}
Common Interview Question: What are the 2 uses of an using statement in C#?1. To import a namespace. Example: using System;2. To close connections properly as shown in the example above
Connection Strings in web.config configuration file - Part 3Suggested VideosPart 1 - What is ADO.NETPart 2 - SQLConnection in ADO.NET
In this video session we will learn about1. Storing connection strings in a configuration file. For example, web.config for an asp.net web application and app.config for windows application2. Reading the connection strings from web.config and app.config files.3. Disadvantages of storing connection strings in application code.4. Advantages of storing connection string in configuration files - web.config and app.config.
In Part 2, of this video series, we have discussed about the SqlConnection object.SqlConnection object uses connection string. The connection strings were hard coded in the examples that we worked with in Part 2.
There are 2 issues with hard coding the connection strings in application code1. For some reason, if we want to point our application to a different database server, we will have to change the application code. If you change application code, the application requires a re-build and a re-deployment which is a time waster.2. All the pages that has the connection string hard coded needs to change. This adds to the maintenance overhead and is also error prone.
In real time, we may point our applications from time to time, from Development database to testing database to UAT database.
Because of these issues, the best practice is to store the connection in the configuration file, from which all the pages can read and use it. This way we have only one place to change, and we don't have to re-build and re-deploy our application. This saves a lot of time.
In an asp.net web application, the configuration strings can be stored in web.config file, as shown below. Give a meaningful name to your connection string. Since we are working with sql server, the provider name is System.Data.SqlClient.<connectionStrings> <add name="DatabaseConnectionString" connectionString="data source=.; database=Sample_Test_DB; Integrated Security=SSPI" providerName="System.Data.SqlClient" /></connectionStrings>
How to read the connection string from web.config file?Use the ConnectionStrings property of the ConfigurationManager class to retrieve the connection string value from web.config. ConfigurationManager class is present inSystem.Configuration namespace.protected void Page_Load(object sender, EventArgs e){ string ConnectionString = ConfigurationManager.co .ConnectionStrings["DatabaseConnectionString"].ConnectionString; using (SqlConnection connection = new SqlConnection( ConnectionString )) { SqlCommand cmd = new SqlCommand("Select * from tblProductInventory", connection); connection.Open(); GridView1.DataSource = cmd.ExecuteReader(); GridView1.DataBind(); }}
The configuration file in a windows application is App.config. Storing connection strings in App.config is similar to web.config. The same ConfigurationManager class can be used to read connection string from App.config file. The example below, shows how to read connection strings from App.config file, and bind the data to a DataGridview control in a windows application.private void Form1_Load(object sender, EventArgs e){ string ConnectionString = ConfigurationManager.ConnectionStrings["DatabaseConnectionString"].ConnectionString; using (SqlConnection connection = new SqlConnection( ConnectionString )) { SqlCommand cmd = new SqlCommand("Select * from tblProductInventory", connection); connection.Open(); BindingSource source = new BindingSource(); source.DataSource = cmd.ExecuteReader(); dataGridView1.DataSource = source; }}
SqlCommand in ado.net - Part 4Suggested VideoPart 1 - What is ADO.NETPart 2 - SQL Connection in ADO.NETPart 3 - Storing and Retrieving connection string from web.config
In this video we will learn about 1. The Purpose of the SqlCommand object2. Creating an instance of the SqlCommand class3. When and how to use ExecuteReader(), ExecuteScalar() and ExecuteNonQuery() methods of the SqlCommand object.
In Part 2, of this video series, we have discussed about the SqlConnection class. In this part, we will learn about SqlCommand class. SqlCommand class is used to prepare an SQL statement or StoredProcedure that we want to execute on a SQL Server database. In this session, we will discuss about executing Transact-SQL statements on a SQL Server. In a later session we will learn about executing stored procedures using the SqlCommand class.
The following are the most commonly used methods of the SqlCommand class.ExecuteReader - Use when the T-SQL statement returns more than a single value. For example, if the query returns rows of data.ExecuteNonQuery - Use when you want to perform an Insert, Update or Delete operationExecuteScalar - Use when the query returns a single(scalar) value. For example, queries that return the total number of rows in a table.
We will be using tblProductInventory table for our examples. The table is shown below for your reference.
tblProductInventory
The sample code below, executes a T-SQL statement, that returns multiple rows of data using ExecuteReader() method. In this example, we are creating an instance of SqlCommand class, in just one line, by passing in the command text, and the connection object. For this purpose, we are using an overloaded constructor of the SqlCommand class that takes 2 parameters(cmdText, connection).protected void Page_Load(object sender, EventArgs e){ string ConnectionString = ConfigurationManager.ConnectionStrings["DatabaseConnectionString"].ConnectionString; using (SqlConnection connection = new SqlConnection("data source=.; database=Sample_Test_DB; integrated security=SSPI")) { //Create an instance of SqlCommand class, specifying the T-SQL command that
//we want to execute, and the connection object. SqlCommand cmd = new SqlCommand("Select Id,ProductName,QuantityAvailable from tblProductInventory", connection); connection.Open(); //As the T-SQL statement that we want to execute return multiple rows of data, //use ExecuteReader() method of the command object. GridView1.DataSource = cmd.ExecuteReader(); GridView1.DataBind(); }}
It is also possible, to create an instance of SqlCommand class using the parameter less constructor, and then later specify the command text and connection, using theCommandText and Connection properties of the SqlCommand object as shown below.protected void Page_Load(object sender, EventArgs e){ string ConnectionString = ConfigurationManager.ConnectionStrings["DatabaseConnectionString"].ConnectionString; using (SqlConnection connection = new SqlConnection("data source=.; database=Sample_Test_DB; integrated security=SSPI")) { //Create an instance of SqlCommand class using the parameter less constructor SqlCommand cmd = new SqlCommand(); //Specify the command, we want to execute using the CommandText property cmd.CommandText = "Select Id,ProductName,QuantityAvailable from tblProductInventory"; //Specify the connection, on which we want to execute the command //using the Connection property cmd.Connection = connection; connection.Open(); //As the T-SQL statement that we want to execute return multiple rows of data, //use ExecuteReader() method of the command object. GridView1.DataSource = cmd.ExecuteReader(); GridView1.DataBind(); }}
In the example below, we are using ExecuteScalar() method, as the T-SQL statement returns a single value.protected void Page_Load(object sender, EventArgs e){ string ConnectionString =
ConfigurationManager.ConnectionStrings["DatabaseConnectionString"].ConnectionString; using (SqlConnection connection = new SqlConnection("data source=.; database=Sample; integrated security=SSPI")) { //Create an instance of SqlCommand class, specifying the T-SQL command //that we want to execute, and the connection object. SqlCommand cmd = new SqlCommand("Select Count(Id) from tblProductInventory", connection); connection.Open(); //As the T-SQL statement that we want to execute return a single value, //use ExecuteScalar() method of the command object. //Since the return type of ExecuteScalar() is object, we are type casting to int datatype int TotalRows = (int)cmd.ExecuteScalar(); Response.Write("Total Rows = " + TotalRows.ToString()); }}
The following example performs an Insert, Update and Delete operations on a SQL server database using the ExecuteNonQuery() method of the SqlCommand object.protected void Page_Load(object sender, EventArgs e){ string ConnectionString = ConfigurationManager.ConnectionStrings["DatabaseConnectionString"].ConnectionString; using (SqlConnection connection = new SqlConnection("data source=.; database=Sample_Test_DB; integrated security=SSPI")) { //Create an instance of SqlCommand class, specifying the T-SQL command //that we want to execute, and the connection object. SqlCommand cmd = new SqlCommand("insert into tblProductInventory values (103, 'Apple Laptops', 100)", connection); connection.Open(); //Since we are performing an insert operation, use ExecuteNonQuery() //method of the command object. ExecuteNonQuery() method returns an //integer, which specifies the number of rows inserted int rowsAffected = cmd.ExecuteNonQuery(); Response.Write("Inserted Rows = " + rowsAffected.ToString() + "<br/>");
//Set to CommandText to the update query. We are reusing the command object, //instead of creating a new command object cmd.CommandText = "update tblProductInventory set QuantityAvailable = 101 where Id = 101"; //use ExecuteNonQuery() method to execute the update statement on the database rowsAffected = cmd.ExecuteNonQuery(); Response.Write("Updated Rows = " + rowsAffected.ToString() + "<br/>");
//Set to CommandText to the delete query. We are reusing the command object, //instead of creating a new command object cmd.CommandText = "Delete from tblProductInventory where Id = 102"; //use ExecuteNonQuery() method to delete the row from the database rowsAffected = cmd.ExecuteNonQuery(); Response.Write("Deleted Rows = " + rowsAffected.ToString() + "<br/>"); }}
Sql injection tutorial - Part 5In this video we will learn about sql injection attack and what can cause it to happen
Suggested VideoPart 1 - What is ADO.NETPart 2 - SQL Connection in ADO.NETPart 3 - Storing and Retrieving connection string from web.config
In Part 4 of ADO.NET video series, we discussed about the SqlCommand object. In this session we will continue with another example of using SqlCommand object. We will be using tblProductInventory table for our example.
If you want to following along, use the following sql script to create the table.Create table tblProductInventory( Id int primary key, ProductName nvarchar(50), QuantityAvailable int)
Insert script to populate the table with sample data.Insert into tblProductInventory values(101,'iPhone',101)Insert into tblProductInventory values(102,'Apple Laptops',100)Insert into tblProductInventory values(103,'Books',120)Insert into tblProductInventory values(104,'Acer Laptops',119)
Insert into tblProductInventory values(105,'iPads',134)
Drag and drop a TextBox, Button and a GridView control onto the webform. Change the ID of the TextBox to ProductNameTextBox and GridView toProductsGridView. Change the ID of the Button to GetProductsButton and the Textto "Get Products". At this point the HTML of the webform should be as shown below.<asp:TextBox ID="ProductNameTextBox" runat="server"></asp:TextBox><asp:Button ID="GetProductsButton" runat="server" Text="Get Products" /><br /><br /><asp:GridView ID="ProductsGridView" runat="server"></asp:GridView>
Now double click the Button control to generate the Click event handler in the code behind file, and then copy and paste the following code. In this example, we are building the query dynamically by concatenating the strings that the user has typed into the textbox. This is extremely dangerous, as it is vulnerable to SQL injection attacks.protected void GetProductsButton_Click(object sender, EventArgs e){ string ConnectionString = ConfigurationManager.ConnectionStrings["DatabaseConnectionString"].ConnectionString; using (SqlConnection connection = new SqlConnection("DatabaseConnectionString")) { //Build the query dynamically, by concatenating the text, that the user has //typed into the ProductNameTextBox. This is a bad way of constructing //queries. This line of code will open doors for sql injection attack SqlCommand cmd = new SqlCommand("Select * from tblProductInventory where ProductName like '" + ProductNameTextBox.Text + "%'", connection); connection.Open(); ProductsGridView.DataSource = cmd.ExecuteReader(); ProductsGridView.DataBind(); }}
Now, run the project. Enter letter "i" into the textbox and click Get Products button. The iPhone and ipad products will be listed in the gridview as expected. But remember, user can type some dangerous sql queries into the textbox, which in turn will be executed by the application on the database. To give you a flavour of that, just imagine what could happen if the user types the following into the TextBox, and clicks Get Products button.i'; Delete from tblProductInventory --
Now execute the following select query on the databaseSelect * from tblProductInventory
The entire data from tblProductInventory table is deleted. This is called SQL injection attack. I have seen a lot of new developers building queries dynamically by concatenating the strings, that end users enter into user interface controls like textboxes. Just imagine the extent of damage that can happen as a result of sql injection.
However, sql injection can be easily avoided, by using parameterized queries or stored procedures. We will talk about these in our next video session.
Sql injection prevention - Part 6Suggested VideosPart 3 - ConnectionStrings in web.config configuration filePart 4 - SqlCommand in ado.netPart 5 - Sql injection tutorial
In Part 5, we have learn about, what can cause sql injection. In this session we will learn about 1. Preventing sql injection using parametrized queries and stored procedures.2. How to execute stored procedures and parameterized queries using ADO.NET command object
The table, this demo is based on, is shown below.
The following ADO.NET code is from Part 5. This is the code, that let's sql injection happen. string CS = ConfigurationManager.ConnectionStrings["DBCS"].ConnectionString;using (SqlConnection con = new SqlConnection(CS)){ string Command = "Select * from tblProductInventory where ProductName like '" + TextBox1.Text + "%'"; SqlCommand cmd = new SqlCommand(Command, con); con.Open(); GridView1.DataSource = cmd.ExecuteReader(); GridView1.DataBind();}
The above code can be easily re-written using parameterized queries to prevent sql injection attack. The re-written code is shown below. Notice, that the query now uses parameter - @ProductName. The value for this parameter is then provided using theAddWithValue() method. The parameter is associated with the command object usingPrameters collection property of the command object.string CS = ConfigurationManager.ConnectionStrings["DBCS"].ConnectionString;using (SqlConnection con = new SqlConnection(CS)){ // Parameterized query. @ProductName is the parameter string Command = "Select * from tblProductInventory where ProductName like @ProductName" ; SqlCommand cmd = new SqlCommand(Command, con); // Provide the value for the parameter cmd.Parameters.AddWithValue("@ProductName", TextBox1.Text + "%"); con.Open(); GridView1.DataSource = cmd.ExecuteReader(); GridView1.DataBind();}
Sql injection can also be prevented using stored procedures. So, first let's write a stored procedure, that returns the list of products. This stored procedure takes an input parameter @ProductName.Create Procedure spGetProductsByName@ProductName nvarchar(50)asBegin Select * from tblProductInventory where ProductName like @ProductName + '%'End
To test this procedure execute the follwing command in sql server management studio.Execute spGetProductsByName 'ip'
Now, let's re-write the code, to use stored procedure spGetProductsByName.string CS = ConfigurationManager.ConnectionStrings["DBCS"].ConnectionString;using (SqlConnection con = new SqlConnection(CS)){ // The command, that we want to execute is a stored procedure, // so specify the name of the procedure as cmdText SqlCommand cmd = new SqlCommand("spGetProductsByName", con); // Specify that the T-SQL command is a stored procedure cmd.CommandType = System.Data.CommandType.StoredProcedure; // Associate the parameter and it's value with the command object cmd.Parameters.AddWithValue("@ProductName", TextBox1.Text + "%"); con.Open(); GridView1.DataSource = cmd.ExecuteReader(); GridView1.DataBind();}
If you type the following input into the TextBox, the entire content of the TextBox is now treated as a value for the parameter - @ProductName not as a seperate sql statement.i'; Delete from tblProductInventory --
So the conclusion is that, always used parameterized queries or stored procedures, to avoid sql injection attacks.
Calling a stored procedure with output parameters - Part 7Suggested VideosPart 7 of SQL Server - Identity Column in SQL ServerPart 8 of SQL Server - How to get the last generated identity column value in SQL ServerPart 19 of SQL Server - Stored procedures with output parametersPart 6 of ADO.NET - Sql injection prevention
In Part 6, we have discussed that sql injection can be prevented by using parameterized queries or stored procedures. We have also seen how to call a stored procedure with input parameters. In this part, we will learn about calling a stored procedure with output parameters.
Our example, will be based on tblEmployees. The script to create this table is shown below. The important point to note here is that, EmployeeId is marked as an identity column. When inserting a row into the table, we need not provide a value for theEmployeeId column. If you want to learn more about identity column, please watch - Part 7 of SQL Server : Identity Column in SQL Server.Create Table tblEmployees( EmployeeId int identity primary key, Name nvarchar(50), Gender nvarchar(10), Salary int)
Script to insert sample data. Notice, that in the insert statement we are not providing a value for EmployeeId Column.Insert into tblEmployees values('Mike','Male',5000)Insert into tblEmployees values('Pam','Female',3500)Insert into tblEmployees values('John','Male',2350)Insert into tblEmployees values('Sara','Female',5700)Insert into tblEmployees values('Steve','Male',4890)Insert into tblEmployees values('Sana','Female',4500)
1. spAddEmployee stored procedure inserts a row into tblEmployees tables.
2. @Name, @Gender and @Salary are input parameters.3. @EmployeeId is an output parameter4. The stored procedure has got only 2 lines of code with in the body. The first line inserts a row into the tblEmployees table. The second line, gets the auto generated identity value of the EmployeeId column.5. This procedure, will later be called by a dot net application.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
At this point, we have done everything that is required for our demo, from a database perspective. Now let's flip to visual studio. Create an asp.net web application. Copy and Paste the following HTML onto a webform.<table style="border: 1px solid black; font-family:Arial"> <tr> <td> Employee Name </td> <td> <asp:TextBox ID="txtEmployeeName" runat="server"></asp:TextBox> </td> </tr> <tr> <td> Gender </td> <td> <asp:DropDownList ID="ddlGender" runat="server"> <asp:ListItem>Male</asp:ListItem> <asp:ListItem>Female</asp:ListItem> </asp:DropDownList> </td> </tr> <tr> <td> Salary </td> <td> <asp:TextBox ID="txtSalary" runat="server"></asp:TextBox> </td> </tr> <tr>
<td colspan="2"> <asp:Button ID="btnSubmit" runat="server" Text="Submit" onclick="btnSubmit_Click" /> </td> </tr> <tr> <td colspan="2"> <asp:Label ID="lblMessage" runat="server"></asp:Label> </td> </tr> </table>
The design of the webform, should be as shown below.
Copy and paste the following code in the code behind page.protected void btnSubmit_Click(object sender, EventArgs e){ //Read the connection string from Web.Config file string ConnectionString =ConfigurationManager.ConnectionStrings["DBCS"].ConnectionString; using (SqlConnection con = new SqlConnection(ConnectionString)) { //Create the SqlCommand object SqlCommand cmd = new SqlCommand("spAddEmployee", con); //Specify that the SqlCommand is a stored procedure 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; }}
Note: Please make sure to add the following using declarations at the top of the code behind page.using System.Data.SqlClient;using System.Configuration;
Now, run the application. Fill in the employee details and click Submit. The Employee row gets added to the database, and the generated EmployeeId is shown on the screen.
SqlDataReader object in ADO.NET - Part 8Suggested VideosPart 5 - Sql injection tutorialPart 6 - Sql injection preventionPart 7 - Calling a stored procedure with output parameters
In this video we will learn about1. The purpose of SqlDataReader2. Creating an instance of SqlDataReader3. Reading data using SqlDataReader's Read() method
SqlDataReader reads data in the most efficient manner possible. 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 also not possible to change the data using SqlDataReader. SqlDataReader is connection oriented, meaning it requires an active connection to the data source, while reading data. The forward-only nature of SqlDataReader is what makes it an efficient choice to read data.
You cannot create an instance of SqlDataReader using the new operator as shown below. If you try to new up SqlDataReader, you will get a compilation error stating - The type 'System.Data.SqlClient.SqlDataReader' has no constructors defined.SqlDataReader rd = new SqlDataReader();
The SqlCommand object's ExecuteReader() method creates and returns an instance of SqlDataReader. SqlCommand command = new SqlCommand("Select * from tblProductInventory", connection);SqlDataReader reader = command.ExecuteReader();
Another important point to keep in mind is that, SqlDataReader is connection oriented and the connection needs to be opened explicitly, by calling the Open() method on the connection object, before calling the ExecuteReader() method of the command object.
The simplest way to bind a SqlDataReader object to a GridView(Data-bound control), is to assign it to the DataSource property of the GridView control and then call the DataBind()
method as shown below. Notice that, just like the SqlConnection object, SqlDataReader is wrapped in an using block. This will ensure that the SqlDataReader is closed in a timely fashion, and that we don't run out of available connections to the database.string ConnectionString =ConfigurationManager.ConnectionStrings["DBConnectionString"].ConnectionString;using (SqlConnection connection = new SqlConnection(ConnectionString)){ connection.Open(); SqlCommand command = new SqlCommand("Select * from tblProductInventory", connection); using (SqlDataReader reader = command.ExecuteReader()) { ProductsGridView.DataSource = reader; ProductsGridView.DataBind(); }}
Please note that, finally block, can also be used to close the SqlConnection and SqlDataReader objects.
If for some reason, you want to loop thru each row in the SqlDataReader object, then use the Read() method, which returns true as long as there are rows to read. If there are no more rows to read, then this method will return false. In the following example, we loop thru each row in the SqlDataReader and then compute the 10% discounted price. string ConnectionString =ConfigurationManager.ConnectionStrings["DBConnectionString"].ConnectionString;using (SqlConnection connection = new SqlConnection(ConnectionString)){ connection.Open(); SqlCommand command = new SqlCommand("Select * from tblProductInventory",connection); using (SqlDataReader reader = command.ExecuteReader()) { // Create the DataTable and columns. This will // be used as the datasource for the GridView DataTable sourceTable = new DataTable(); sourceTable.Columns.Add("ID"); sourceTable.Columns.Add("Name"); sourceTable.Columns.Add("Price"); sourceTable.Columns.Add("DiscountedPrice");
while (reader.Read()) { //Calculate the 10% discounted price int OriginalPrice = Convert.ToInt32(reader["UnitPrice"]); double DiscountedPrice = OriginalPrice * 0.9;
// Populate datatable column values from the SqlDataReader DataRow datarow = sourceTable.NewRow(); datarow["ID"] = reader["ProductId"]; datarow["Name"] = reader["ProductName"];
datarow["Price"] = OriginalPrice; datarow["DiscountedPrice"] = DiscountedPrice;
//Add the DataRow to the DataTable sourceTable.Rows.Add(datarow); }
// Set sourceTable as the DataSource for the GridView ProductsGridView.DataSource = sourceTable; ProductsGridView.DataBind(); }}
SQL script to create the table we used in the Demo.Create table tblProductInventory ( ProductId int identity primary key, ProductName nvarchar(50), UnitPrice int)
Script to populate dataInsert into tblProductInventory values('iPhone',350)Insert into tblProductInventory values('Apple Laptops',1250)Insert into tblProductInventory values('Books',110)Insert into tblProductInventory values('Acer Laptops',1150)Insert into tblProductInventory values('iPads',450)
Note: Please make sure you have the following namespace declarations in the code behind file.using System.Data;using System.Data.SqlClient;using System.Configuration;
SqlDataReader object NextResult() method - Part 9Suggested VideosPart 6 - Sql injection preventionPart 7 - Calling a stored procedure with output parametersPart 8 - SqlDataReader object in ado.net
In this video we will learn about retrieving two or more result sets using the SqlDataReader object's NextResult() method. The follwoing SqlCommand object returns two result-sets, one from - tblProductInventory and the other from tblProductCategories.SqlCommand command = new SqlCommand("select * from tblProductInventory; select * from tblProductCategories", connection);
When you run this code only the result set from tblProductInventory is displayed.The result-set from tblProductCategories is not shown.string ConnectionString =ConfigurationManager.ConnectionStrings["DBConnectionString"].ConnectionString;using (SqlConnection connection = new SqlConnection(ConnectionString)){ connection.Open(); SqlCommand command = new SqlCommand("select * from tblProductInventory; select * from tblProductCategories", connection); using (SqlDataReader reader = command.ExecuteReader()) { ProductsGridView.DataSource = reader; ProductsGridView.DataBind();
CategoriesGridView.DataSource = reader; CategoriesGridView.DataBind(); }}
To retrieve the second result-set from SqlDataReader object, use the NextResult() as shown in the code snippet below. The NextResult() method returns true and advances to the next result-set.
string ConnectionString =ConfigurationManager.ConnectionStrings["DBConnectionString"].ConnectionString;using (SqlConnection connection = new SqlConnection(ConnectionString)){ connection.Open(); SqlCommand command = new SqlCommand("select * from tblProductInventory; select * from tblProductCategories", connection); using (SqlDataReader reader = command.ExecuteReader()) { ProductsGridView.DataSource = reader; ProductsGridView.DataBind();
while (reader.NextResult()) { CategoriesGridView.DataSource = reader; CategoriesGridView.DataBind(); } }}
The SqlDataReader object's Read() method is used to loop thru the rows in a given result set, where as the NextResult() method is used to loop thru multiple result sets.
Sql script to create and populate the required tables we used in this demo.Create table tblProductInventory ( ProductId int identity primary key, ProductName nvarchar(50), UnitPrice int)
Insert into tblProductInventory values('iPhone',350)Insert into tblProductInventory values('Apple Laptops',1250)Insert into tblProductInventory values('Books',110)Insert into tblProductInventory values('Acer Laptops',1150)Insert into tblProductInventory values('iPads',450)
Create table tblProductCategories( CategoryId int identity primary key, CategoryName nvarchar(50),)
Insert into tblProductCategories values ('Electronics')Insert into tblProductCategories values ('Sports')Insert into tblProductCategories values ('Kitchen')
SqlDataAdapter in ADO.NET - Part 10Suggested VideosPart 7 - Calling a stored procedure with output parametersPart 8 - SqlDataReader object in ado.netPart 9 - SqlDataReader object's NextResult method
In this video we will learn about1. SqlDataAdapter2. DataSet
In the previous sessions of ADO.NET video series, we have learnt aboutSqlDataReader. SqlDataReader is connection oriented, meaning it requires an active and open connection to the data source. SqlDataAdapter and DataSet provides us with disconnected data access model. In this part, we learn to use SqlDataAdapter and DataSet objects. In a later video session, we will discuss about, where the disconnected data access model can be used.
In Part 4, we have discussed about SqlCommand object. When we create an instance of SqlCommand object, we pass in the following 2 parameters to the constructor of the SqlCommand class.1. The command that we want to execute 2. The connection on which we want to execute the command
Along the same lines, when creating an instance of the SqlDataAdapter, we specify1. The sql command that we want to execute 2. The connection on which we want to execute the command
The example shown below 1. Creates an instance of SqlDataAdapter, passing in the required parameters (SqlCommandText and the Connection object)2. Creates an instance of DataSet object. A DataSet is an in-memory data store, that can store tables, just like a database.3. The Fill() method of the SqlDataAdapter class is then invoked. 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.4. The dataset object, is then set as the datasource of the GridView1 control5. Finally the DataBind() method is called, which binds the data to the control.string ConnectionString =ConfigurationManager.ConnectionStrings["DBConnectionString"].ConnectionString;using (SqlConnection connection = new SqlConnection(ConnectionString)){ // Create an instance of SqlDataAdapter. Spcify the command and the connection SqlDataAdapter dataAdapter = new SqlDataAdapter("select * from tblProductInventory", connection); // Create an instance of DataSet, which is an in-memory datastore for storing tables DataSet dataset = new DataSet(); // Call the Fill() methods, which automatically opens the connection, executes the command // and fills the dataset with data, and finally closes the connection. dataAdapter.Fill(dataset);
GridView1.DataSource = dataset; GridView1.DataBind();}
Executing a stored procedure using SqlDataAdapter:Create procedure spGetProductInventory asBegin Select ProductId, ProductName, UnitPrice from tblProductInventoryEnd
If you want to execute stored procedure spGetProductInventory, using theSqlDataAdapter, just specify the name of the procedure instead of the in-line sql statement.SqlDataAdapter dataAdapter = new SqlDataAdapter("spGetProductInventory", connection);dataAdapter.SelectCommand.CommandType = CommandType.StoredProcedure;
Executing a stored procedure with parameters using SqlDataAdapter:Create procedure spGetProductInventoryById@ProductId intasBegin Select ProductId, ProductName, UnitPrice from tblProductInventory where ProductId = @ProductIdEnd
To execute stored procedure spGetProductInventoryById, we need to associate parameter @ProductId to the SqlDataAdapeter object's SelectCommand as shown below.string ConnectionString =ConfigurationManager.ConnectionStrings["DBConnectionString"].ConnectionString;using (SqlConnection connection = new SqlConnection(ConnectionString))
{ // Create an instance of SqlDataAdapter, specifying the stored procedure // and the connection object to use SqlDataAdapter dataAdapter = new SqlDataAdapter("spGetProductInventoryById", connection); // Specify the command type is an SP dataAdapter.SelectCommand.CommandType = CommandType.StoredProcedure; // Associate the parameter with the stored procedure dataAdapter.SelectCommand.Parameters.AddWithValue("@ProductId", 1); DataSet dataset = new DataSet(); dataAdapter.Fill(dataset);
GridView1.DataSource = dataset; GridView1.DataBind();}
DataSet in asp.net - Part 11Suggested VideosPart 8 - SqlDataReader object in ado.netPart 9 - SqlDataReader object's NextResult methodPart 10 - SqlDataAdapter in ADO.NET
In this video we will learn about 1. Loading 2 or more tables into a DataSet, and giving these tables a meaningful name.2. Binding the tables in the DataSet to user interface databound controls like GridView, using the Tables collection property of the DataSet object.
When the following stored procedure is executed, we get 2 result-setsCreate procedure spGetProductAndCategoriesDataasBegin Select ProductId, ProductName, UnitPrice from tblProductInventory
Select CategoryId, CategoryName from tblProductCategoriesEnd
Drag and drop 2 GridView controls onto the webform and change the ID toGridViewProducts and GridViewCategories. The HTML in the aspx page should be as
shown below.<asp:GridView ID="GridViewProducts" runat="server"></asp:GridView><br /><asp:GridView ID="GridViewCategories" runat="server"></asp:GridView>
Copy and paste the following code into the code behind page.string ConnectionString =ConfigurationManager.ConnectionStrings["DBConnectionString"].ConnectionString;using (SqlConnection connection = new SqlConnection(ConnectionString)){ SqlDataAdapter dataAdapter = newSqlDataAdapter("spGetProductAndCategoriesData", connection); dataAdapter.SelectCommand.CommandType = CommandType.StoredProcedure; DataSet dataset = new DataSet(); dataAdapter.Fill(dataset);
GridViewProducts.DataSource = dataset; GridViewProducts.DataBind();
GridViewCategories.DataSource = dataset; GridViewCategories.DataBind();}
When you run the project now, notice that both the gridview controls show the same data. This is because, by default, the first table from the dataset is used as the data source for both the gridview controls. We actually want to show products data in one gridview control and categories data in the other. To specify the specific DataTable, that you want to bind to a gridview control, use the Tables collection property of the dataset object, as shown below.string ConnectionString =ConfigurationManager.ConnectionStrings["DBConnectionString"].ConnectionString;using (SqlConnection connection = new SqlConnection(ConnectionString)){ SqlDataAdapter dataAdapter = newSqlDataAdapter("spGetProductAndCategoriesData", connection); dataAdapter.SelectCommand.CommandType = CommandType.StoredProcedure; DataSet dataset = new DataSet(); dataAdapter.Fill(dataset);
GridViewProducts.DataSource = dataset.Tables[0]; GridViewProducts.DataBind();
GridViewCategories.DataSource = dataset.Tables[1]; GridViewCategories.DataBind();}
By default the tables in the DataSet will have table names as Table, Table1, Table2etc. So if you want to give the tables in the DataSet a meaningful name, use the TableName property as shown below.dataset.Tables[0].TableName = "Products";dataset.Tables[1].TableName = "Categories";
These table names can then be used when binding to a GridView control, instead of using the integral indexer, which makes your code more readable, and maintainable.GridViewProducts.DataSource = dataset.Tables["Products"];GridViewProducts.DataBind();
GridViewCategories.DataSource = dataset.Tables["Categories"];GridViewCategories.DataBind();
Caching dataset in asp.net - Part 12Suggested VideosPart 9 - SqlDataReader object's NextResult methodPart 10 - SqlDataAdapter in ADO.NETPart 11 - Dataset in asp.net
In this video we will learn about1. Caching a DataSet2. Check to see if the DataSet exists in the cache, and load data from the cache.3. Clearing the cache
We will be using tblProductInventory table for this demo. Sql script to the create the table populate it with some sample data.Create table tblProductInventory ( ProductId int identity primary key, ProductName nvarchar(50), UnitPrice int)
Insert into tblProductInventory values('iPhone',350)Insert into tblProductInventory values('Apple Laptops',1250)Insert into tblProductInventory values('Books',110)Insert into tblProductInventory values('Acer Laptops',1150)Insert into tblProductInventory values('iPads',450)
Create an asp.net web application project, and add the following database connectionstring to the web.config file.<connectionStrings> <add name="DBCS" connectionString="data source=.; database=Sample_Test_DB; Integrated Security=SSPI"
providerName="System.Data.SqlClient" /></connectionStrings>
Drag and drop 2 button controls, a label and a gridview control onto the webform.1. Set the ID of the first button control to btnLoadData and Text to Load Data2. Set the ID of the second button control to btnClearnCache and Text to Clear Cache3. Set the ID of the label control to lblMessage and remove the Text property4. Set the ID of the GridView to gvProducts
At this stage the HTML of your webform, should be as shown below.<asp:Button ID="btnLoadData" runat="server" Text="Load Data" onclick="btnLoadData_Click" /><asp:Button ID="btnClearnCache" runat="server" Text="Clear Cache" onclick="btnClearnCache_Click" /><br /><br /><asp:Label ID="lblMessage" runat="server"></asp:Label><br /><br /><asp:GridView ID="gvProducts" runat="server"></asp:GridView>
Now, copy and paste the following code in the code behind pageprotected void btnLoadData_Click(object sender, EventArgs e){ // Check if the DataSet is present in the cache if (Cache["Data"] == null) { // If the dataset is not in the cache load data from the database into the DataSet string CS = ConfigurationManager.ConnectionStrings["DBCS"].ConnectionString; using (SqlConnection connection = new SqlConnection(CS)) { SqlDataAdapter dataAdapter = new SqlDataAdapter("Select * from tblProductInventory", connection); DataSet dataset = new DataSet(); dataAdapter.Fill(dataset);
gvProducts.DataSource = dataset; gvProducts.DataBind();
// Store the DataSet in the Cache Cache["Data"] = dataset; lblMessage.Text = "Data loaded from the Database"; } } // If the DataSet is in the Cache else { // Retrieve the DataSet from the Cache and type cast to DataSet
gvProducts.DataSource = (DataSet)Cache["Data"]; gvProducts.DataBind(); lblMessage.Text = "Data loaded from the Cache"; }}
protected void btnClearnCache_Click(object sender, EventArgs e){ // Check if the DataSet is present in the cache if (Cache["Data"] != null) { // Remove the DataSet from the Cache Cache.Remove("Data"); lblMessage.Text = "DataSet removed from the cache"; } // If the DataSet is not in the Cache else { lblMessage.Text = "There is nothing in the cache to remove"; }}
Now, run the application. The first time you click Load Data button, the data will be loaded from the database, as we don't have the DataSet in the Cache yet. Once the Data is loaded into the DataSet. The DataSet is then cached. If you click the Load Data button now, then the Data will be loaded from the cache. At this point we don't need to have any connection to the Database.
To prove this stop the sql server service on your machine.1. In the run window, type services.msc and press enter key2. In the services window, find SQL Server service3. Right click on the SQL Server service and stop it.
Since we have stopped the service, sql server is no longer running on our machine. Now click the Load Data button. The data will be loaded from the cache. Now, clear the cache, by click on Clear Cache button. This will remove the DataSet from the cache. Now, try to load the data by clicking on Load Data button. Since, the DataSet is no longer present in the cache, and the sql server service is not running you will receive an error stating - A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server)
Start the service, and click the Load Data button. The data should now be loaded and cached.
Part 13 - What is SqlCommandBuilderSuggested VideosPart 10 - SqlDataAdapter in ADO.NETPart 11 - Dataset in asp.net Part 12 - Caching dataset in asp.net
SqlCommandBuilder automatically generates INSERT, UPDATE and DELETE sql statements based on the SELECT statement for a single table.
For the Transact-SQL statements to be generated using SqlCommandBuilder, there are 2 stepsStep 1. Set the "SelectCommand" property of the SqlDataAdapter objectSqlDataAdapter dataAdapter = new SqlDataAdapter();dataAdapter.SelectCommand = new SqlCommand("SELECT_Query", con);
Step 2. Create an instance of SqlCommandBuilder class and associate the SqlDataAdapter object created above using DataAdapter property of the SqlCommandBuilder objectSqlCommandBuilder builder = new SqlCommandBuilder();builder.DataAdapter = dataAdapter;
Please Note: Step 2, can also be done in single line as shown below. Here, we are passing the SqlDataAdapter instance as an argument to SqlCommandBuilder class constructorSqlCommandBuilder builder = new SqlCommandBuilder(dataAdapter);
Sql script for the table used in this demo. Create Table tblStudents( ID int identity primary key, Name nvarchar(50), Gender nvarchar(20), TotalMarks int)
Insert into tblStudents values('Mark Hastings','Male',900)Insert into tblStudents values('Pam Nicholas','Female',760)
Insert into tblStudents values('John Stenson','Male',980)Insert into tblStudents values('Ram Gerald','Male',990)Insert into tblStudents values('Ron Simpson','Male',440)Insert into tblStudents values('Able Wicht','Male',320)Insert into tblStudents values('Steve Thompson','Male',983)Insert into tblStudents values('James Bynes','Male',720)Insert into tblStudents values('Mary Ward','Female',870)Insert into tblStudents values('Nick Niron','Male',680)
ASPX Code:<div style="font-family: Arial"><table border="1"> <tr> <td> Student ID </td> <td> <asp:TextBox ID="txtStudentID" runat="server"></asp:TextBox> <asp:Button ID="btnGetStudent" runat="server" Text="Load" OnClick="btnGetStudent_Click" /> </td> </tr> <tr> <td> Name </td> <td> <asp:TextBox ID="txtStudentName" runat="server"></asp:TextBox> </td> </tr> <tr> <td> Gender </td> <td> <asp:DropDownList ID="ddlGender" runat="server"> <asp:ListItem Text="Select Gender" Value="-1"></asp:ListItem> <asp:ListItem Text="Male" Value="Male"></asp:ListItem> <asp:ListItem Text="Female" Value="Female"></asp:ListItem> </asp:DropDownList> </td> </tr> <tr> <td>
Total Marks </td> <td> <asp:TextBox ID="txtTotalMarks" runat="server"></asp:TextBox> </td> </tr> <tr> <td colspan="2"> <asp:Button ID="btnUpdate" runat="server" Text="Update" OnClick="btnUpdate_Click" /> <asp:Label ID="lblStatus" runat="server" Font-Bold="true"> </asp:Label> </td> </tr></table></div>
ASPX.CS Code:public partial class WebForm1 : System.Web.UI.Page{ protected void Page_Load(object sender, EventArgs e) { }
protected void btnGetStudent_Click(object sender, EventArgs e) { string connectionString = ConfigurationManager.ConnectionStrings["DBCS"].ConnectionString; SqlConnection connection = new SqlConnection(connectionString); string selectQuery = "Select * from tblStudents where ID = " + txtStudentID.Text; SqlDataAdapter dataAdapter = new SqlDataAdapter(selectQuery, connection);
DataSet dataSet = new DataSet(); dataAdapter.Fill(dataSet, "Students");
// Store DataSet and the select query in ViewState, so they can be used // later to generate the T-SQL commands using SqlCommandBuilder class ViewState["DATASET"] = dataSet; ViewState["SELECT_QUERY"] = selectQuery;
if (dataSet.Tables["Students"].Rows.Count > 0) { DataRow dataRow = dataSet.Tables["Students"].Rows[0];
txtStudentName.Text = dataRow["Name"].ToString(); txtTotalMarks.Text = dataRow["TotalMarks"].ToString(); ddlGender.SelectedValue = dataRow["Gender"].ToString(); lblStatus.Text = ""; } else { lblStatus.ForeColor = System.Drawing.Color.Red; lblStatus.Text = "No record with ID = " + txtStudentID.Text; } }
protected void btnUpdate_Click(object sender, EventArgs e) { string connectionString = ConfigurationManager.ConnectionStrings["DBCS"].ConnectionString; SqlConnection con = new SqlConnection(connectionString);
SqlDataAdapter dataAdapter = new SqlDataAdapter(); // Retrieve the Select query from ViewState and use it to build // SqlCommand command object, which will then be set as the // SelectCommand of the SqlDataAdapter object dataAdapter.SelectCommand = new SqlCommand((string)ViewState["SELECT_QUERY"], con);
// Associate SqlDataAdapter object with SqlCommandBuilder. At this point // SqlCommandBuilder should generate T-SQL statements automatically SqlCommandBuilder builder = new SqlCommandBuilder(dataAdapter);
DataSet ds = (DataSet)ViewState["DATASET"]; DataRow dr = ds.Tables["Students"].Rows[0]; dr["Name"] = txtStudentName.Text; dr["Gender"] = ddlGender.SelectedValue; dr["TotalMarks"] = txtTotalMarks.Text; dr["Id"] = txtStudentID.Text;
int rowsUpdated = dataAdapter.Update(ds, "Students"); if (rowsUpdated == 0) { lblStatus.ForeColor = System.Drawing.Color.Red; lblStatus.Text = "No rows updated"; } else {
lblStatus.ForeColor = System.Drawing.Color.Green; lblStatus.Text = rowsUpdated.ToString() + " row(s) updated"; } }}
Please make sure to include the following using declarations.using System.Data;using System.Data.SqlClient;using System.Configuration;
Part 14 - Sqlcommandbuilder update not workingSuggested VideosPart 11 - Dataset in asp.netPart 12 - Caching dataset in asp.net Part 13 - What is SqlCommandBuilder
This is continuation to Part 13. Please watch Part 13 from ADO.NET tutorial, before proceeding.
Two common reasons why SqlDataAdapter.Update does not work1. SqlCommandBuilder object not associated with SqlDataAdapter object. Without this association SqlCommandBuilder object does not know how to generate INSERT, UPDATE and DELETE statements.SqlCommandBuilder builder = new SqlCommandBuilder(dataAdapter);If the above line is not present in your code, SqlDataAdapter.Update() method will throw an exception - Update requires a valid UpdateCommand when passed DataRow collection with modified rows.
2. The SelectCommand that is associated with SqlDataAdapter, does not return atleast one primary key or unique column. If this is the case you will get an exception -Dynamic SQL generation for the UpdateCommand is not supported against a SelectCommand that does not return any key column information.
For troubleshooting purposes, if you want to see the autogenerated INSERT, UPDATE, and DELETE T-SQL statements, use GetInsertCommand(), GetUpdateCommand() and GetDeleteCommand().lblInsert.Text = builder.GetInsertCommand().CommandText;lblUpdate.Text = builder.GetUpdateCommand().CommandText;lblDelete.Text = builder.GetDeleteCommand().CommandText;
Part 15 - Disconnected data access in asp.netSuggested VideosPart 12 - Caching dataset in asp.netPart 13 - What is SqlCommandBuilder Part 14 - Sqlcommandbuilder update not working
ADO.NET offers two data access modes1. Connection oriented data access2. Disconnected data access
In this video, we will discuss disconnected data access. SqlDataAdapter and DataSet objects together provide disconnected data access.
A DataSet is an in-memory data store that can hold one or more tables. DataSets only hold data and do not interact with the underlying database table. The DataSet object has no knowledge of the underlying Data Source. It is the SqlDataAdapter object that retrieves data from the datasource.
This is how it works.1. You create an instance of SqlDataAdapter by specifying a select command and a connection objectstring connectionString =ConfigurationManager.ConnectionStrings["DBCS"].ConnectionString;SqlConnection connection = new SqlConnection(connectionString);string selectQuery = "Select * from tblStudents";SqlDataAdapter dataAdapter = new SqlDataAdapter(selectQuery, connection);
2. When SqlDataAdapter.Fill() method is invoked, SqlDataAdapter opens the connection to the
database, executes the select command, and the DataSet is populated with the data that is retrieved. The SqlDataAdapter automatically closes the connection.DataSet dataSet = new DataSet();dataAdapter.Fill(dataSet, "Students");
3. You now have data in the DataSet and there is no active connection to the database. At this point you can make any changes(insert, update, delete) to the data in the DataSet. Only the data in the DataSet is changed, the underlying database table data is not changed.
4. To update the underlying database table, invoke SqlDataAdapter.Update() method. Make sure there is an UPDATE, DELETE and INSERT command are associated with SqlDataAdapter object when Update() method is called, otherwise there would be a runtime exception.dataAdapter.Update(DataSetObject, "Students");
ASPX Code:<div style="font-family: Arial"> <asp:Button ID="btnGetDataFromDB" runat="server" Text="Get Data from Database" onclick="btnGetDataFromDB_Click" /> <asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False" DataKeyNames="ID" onrowediting="GridView1_RowEditing" onrowcancelingedit="GridView1_RowCancelingEdit" onrowdeleting="GridView1_RowDeleting" onrowupdating="GridView1_RowUpdating"> <Columns> <asp:CommandField ShowDeleteButton="True" ShowEditButton="True" /> <asp:BoundField DataField="ID" HeaderText="ID" InsertVisible="False" ReadOnly="True" SortExpression="ID" /> <asp:BoundField DataField="Name" HeaderText="Name" SortExpression="Name" /> <asp:BoundField DataField="Gender" HeaderText="Gender" SortExpression="Gender" /> <asp:BoundField DataField="TotalMarks" HeaderText="TotalMarks" SortExpression="TotalMarks" /> </Columns> </asp:GridView> <asp:Button ID="btnUpdateDatabaseTable" runat="server" Text="Update Database Table" onclick="btnUpdateDatabaseTable_Click" /> <asp:Label ID="lblStatus" runat="server"></asp:Label></div>
ASPX.CS Code:public partial class WebForm1 : System.Web.UI.Page{ protected void Page_Load(object sender, EventArgs e) {
}
private void GetDataFromDB() { string connectionString =ConfigurationManager.ConnectionStrings["DBCS"].ConnectionString; SqlConnection connection = new SqlConnection(connectionString); string selectQuery = "Select * from tblStudents"; SqlDataAdapter dataAdapter = new SqlDataAdapter(selectQuery, connection);
DataSet dataSet = new DataSet(); dataAdapter.Fill(dataSet, "Students"); // Set ID column as the primary key dataSet.Tables["Students"].PrimaryKey = new DataColumn[] { dataSet.Tables["Students"].Columns["ID"] }; // Store the dataset in Cache Cache.Insert("DATASET", dataSet, null, DateTime.Now.AddHours(24), System.Web.Caching.Cache.NoSlidingExpiration);
GridView1.DataSource = dataSet; GridView1.DataBind();
lblStatus.Text = "Data loded from Database"; }
private void GetDataFromCache() { if (Cache["DATASET"] != null) { GridView1.DataSource = (DataSet)Cache["DATASET"]; GridView1.DataBind(); } }
protected void GridView1_RowEditing(object sender, GridViewEditEventArgs e) { // Set row in editing mode GridView1.EditIndex = e.NewEditIndex; GetDataFromCache(); }
protected void GridView1_RowCancelingEdit(object sender,GridViewCancelEditEventArgs e) { GridView1.EditIndex = -1;
GetDataFromCache(); }
protected void GridView1_RowUpdating(object sender, GridViewUpdateEventArgs e) { // Retrieve dataset from cache DataSet dataSet = (DataSet)Cache["DATASET"]; // Find datarow to edit using primay key DataRow dataRow = dataSet.Tables["Students"].Rows.Find(e.Keys["ID"]); // Update datarow values dataRow["Name"] = e.NewValues["Name"]; dataRow["Gender"] = e.NewValues["Gender"]; dataRow["TotalMarks"] = e.NewValues["TotalMarks"]; // Overwrite the dataset in cache Cache.Insert("DATASET", dataSet, null, DateTime.Now.AddHours(24), System.Web.Caching.Cache.NoSlidingExpiration); // Remove the row from edit mode GridView1.EditIndex = -1; // Reload data to gridview from cache GetDataFromCache(); }
protected void GridView1_RowDeleting(object sender, GridViewDeleteEventArgs e) { DataSet dataSet = (DataSet)Cache["DATASET"]; dataSet.Tables["Students"].Rows.Find(e.Keys["ID"]).Delete(); Cache.Insert("DATASET", dataSet, null, DateTime.Now.AddHours(24), System.Web.Caching.Cache.NoSlidingExpiration); GetDataFromCache(); }
protected void btnGetDataFromDB_Click(object sender, EventArgs e) { GetDataFromDB(); }
protected void btnUpdateDatabaseTable_Click(object sender, EventArgs e) { if (Cache["DATASET"] != null) { string connectionString = ConfigurationManager.ConnectionStrings["DBCS"].ConnectionString; SqlConnection connection = new SqlConnection(connectionString); string selectQuery = "Select * from tblStudents";
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"; // Create an instance of SqlCommand using the update command created above SqlCommand updateCommand = new SqlCommand(strUpdateCommand, connection); // Specify the parameters of the update command 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"); // Associate update command with SqlDataAdapter instance dataAdapter.UpdateCommand = updateCommand;
// Delete command to delete data from database table string strDeleteCommand = "Delete from tblStudents where Id = @Id"; // Create an instance of SqlCommand using the delete command created above SqlCommand deleteCommand = new SqlCommand(strDeleteCommand, connection); // Specify the parameters of the delete command deleteCommand.Parameters.Add("@Id", SqlDbType.Int, 0, "Id"); // Associate delete command with SqlDataAdapter instance dataAdapter.DeleteCommand = deleteCommand;
// Update the underlying database table dataAdapter.Update((DataSet)Cache["DATASET"], "Students"); lblStatus.Text = "Database table updated"; } }}
Please make sure to include the following using declarations:using System.Configuration;using System.Data;using System.Data.SqlClient;
Part 16 - Dataset.rejectchanges and dataset.acceptchanges methodsSuggested VideosPart 13 - What is SqlCommandBuilderPart 14 - Sqlcommandbuilder update not working
Part 15 - Disconnected data access in asp.net
This is continuation to Part 15. Please watch Part 15, before proceeding.
To understand AcceptChanges() and RejectChanges() methods better, we need to understand Row States and Row Versions.
Every DataRow that is present in DataTable of a DataSet has RowState property. Please check the following MSDN link, for different values of RowState property and their description. Different DataRowVersion enumeration values and their description is also present.http://msdn.microsoft.com/en-us/library/ww3k31w0.aspx
HasVersion() method can be used to check if a row has got a specific DataRowVersion.DataRow.HasVersion(DataRowVersion.Original)
When we call DataAdapter.Fill() method, data is loaded into the DataSet and theRowState of all the rows will be Unchanged. When we edit a row the row state becomes Modified. If we delete a row, the row state becomes Deleted. At this point with in the DataSet, we have got Unchanged, Deleted and Modified rows. If we then invoke, DataAdapter.Update() method, based on the RowState, respective INSERT, UPDATE and DELETE commands are executed against the underlying database table andAcceptChanges() is called automatically.
When AcceptChanges() is invoked RowState property of each DataRow changes. Added and Modified rows become Unchanged, and Deleted rows are removed.
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 levels1. 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.2. At the DataTable level - When invoked at the DataTable level, they get called automatically on each DataRow within each DataTable.3. At the DataRow level - Gets called only for the row, on which it is invoked.
Part 17 - Strongly typed datasetsSuggested VideosPart 14 - Sqlcommandbuilder update not workingPart 15 - Disconnected data access in asp.net Part 16 - Dataset.rejectchanges and dataset.acceptchanges methods
In this video we will discuss1. What are strongly typed datasets2. Advantage of using strongly typed datasets over untyped datasets3. Steps to generate a typed dataset using visual studio
What are strongly typed datasets1. Strongly Typed Dataset is generated based on the Database Schema. 2. Strongly Typed Dataset derive form DataSet3. In a strongly typed dataset the database table columns become properties and the type associated with each column is known at design time
Advantage of using strongly typed datasets over untyped datasetsSince, in a strongly typed dataset the database table columns become properties and the type associated with each column is known at design time, 1. Development is much easier as we will have intellisense 2. Any errors related to misspelt column names can be detected at compile time, rather than at runtime
Steps to generate a typed dataset using visual studio1. Right click on the Project Name in solution explorer and select "Add - New Item"2. Select "DataSet", give it a meaningful name and click "Add". This should add a file with .XSD extension.3. Click on "View" menu item in Visual Studio and select "Server Explorer"4. In "Server Explorer", expand "Data Connections", then expand the "Database", and then expand "Tables"5. Drag and drop the table based on which you want to generate a strongly typed dataset.
ASPX code for both WebForm1.aspx and WebForm2.aspx<div style="font-family:Arial"> <asp:TextBox ID="TextBox1" runat="server"></asp:TextBox>
<asp:Button ID="Button1" runat="server" Text="Button" onclick="Button1_Click" /> <asp:GridView ID="GridView1" runat="server"> </asp:GridView></div>
Student class used in the demopublic class Student{ public int ID { get; set; } public string Name { get; set; } public string Gender { get; set; } public int TotalMarks { get; set; }}
Please make sure to include the following using declarations on WebForm1.aspx.csusing System.Configuration;using System.Data;using System.Data.SqlClient;
WebForm1.aspx.cs code:public partial class WebForm1 : System.Web.UI.Page{ protected void Page_Load(object sender, EventArgs e) { if (!IsPostBack) { string connectionString = ConfigurationManager.ConnectionStrings["DBCS"].ConnectionString; SqlConnection connection = new SqlConnection(connectionString); string selectQuery = "Select * from tblStudents"; SqlDataAdapter dataAdapter = new SqlDataAdapter(selectQuery, connection);
DataSet dataSet = new DataSet(); dataAdapter.Fill(dataSet, "Students");
Session["DATASET"] = dataSet;
GridView1.DataSource = from dataRow indataSet.Tables["Students"].AsEnumerable() select new Student { ID = Convert.ToInt32(dataRow["Id"]), Name = dataRow["Name"].ToString(), Gender = dataRow["Gender"].ToString(),
TotalMarks = Convert.ToInt32(dataRow["TotalMarks"]) }; GridView1.DataBind(); } }
protected void Button1_Click(object sender, EventArgs e) { DataSet dataSet = (DataSet)Session["DATASET"];
if (string.IsNullOrEmpty(TextBox1.Text)) { GridView1.DataSource = from dataRow in dataSet.Tables["Students"].AsEnumerable() select new Student { ID = Convert.ToInt32(dataRow["Id"]), Name = dataRow["Name"].ToString(), Gender = dataRow["Gender"].ToString(), TotalMarks = Convert.ToInt32(dataRow["TotalMarks"]) }; GridView1.DataBind(); } else { GridView1.DataSource = from dataRow indataSet.Tables["Students"].AsEnumerable() wheredataRow["Name"].ToString().ToUpper().StartsWith(TextBox1.Text.ToUpper()) select new Student { ID = Convert.ToInt32(dataRow["Id"]), Name = dataRow["Name"].ToString(), Gender = dataRow["Gender"].ToString(), TotalMarks = Convert.ToInt32(dataRow["TotalMarks"]) }; GridView1.DataBind(); } }}
WebForm2.aspx.cs code:public partial class WebForm2 : System.Web.UI.Page{ protected void Page_Load(object sender, EventArgs e) { if (!IsPostBack)
{ StudentDataSetTableAdapters.StudentsTableAdapter studentsTableAdapter = new StudentDataSetTableAdapters.StudentsTableAdapter(); StudentDataSet.StudentsDataTable studentsDataTable = new StudentDataSet.StudentsDataTable(); studentsTableAdapter.Fill(studentsDataTable);
Session["DATATABLE"] = studentsDataTable;
GridView1.DataSource = from student in studentsDataTable select new { student.ID, student.Name, student.Gender, student.TotalMarks }; GridView1.DataBind(); } }
protected void Button1_Click(object sender, EventArgs e) { StudentDataSet.StudentsDataTable studentsDataTable = (StudentDataSet.StudentsDataTable)Session["DATATABLE"];
if (string.IsNullOrEmpty(TextBox1.Text)) { GridView1.DataSource = from student in studentsDataTable select new { student.ID, student.Name, student.Gender, student.TotalMarks }; GridView1.DataBind(); } else { GridView1.DataSource = from student in studentsDataTable where student.Name.ToUpper().StartsWith(TextBox1.Text.ToUpper()) select new { student.ID, student.Name, student.Gender, student.TotalMarks }; GridView1.DataBind(); } }}
Part 18 - Load xml data into sql server table using sqlbulkcopySuggested VideosPart 15 - Disconnected data access in asp.netPart 16 - Dataset.rejectchanges and dataset.acceptchanges methods Part 17 - Strongly typed datasets
In this video we will discuss1. What is the use of SqlBulkCopy class2. Loading xml data into sql server table using sqlbulkcopy
What is the use of SqlBulkCopy classSqlBulkCopy class is used to bulk copy data from different data sources to SQL Server database. This class is present in System.Data.SqlClient namespace. This class can be used to write data only to SQL Server tables. However, the data source is not limited to SQL Server, any data source can be used, as long as the data can be loaded to a DataTable instance or read with a IDataReader instance.
From a performance standpoint, SqlBulkCopy makes it very easy and efficient to copy large amounts of data.
Loading xml data into sql server table using sqlbulkcopy
We want to load the following XML data into SQL Server database.
Notice that we have Departments and Employees data in the XML file. We would like to load Employees data into Employees table and Departments data into Departments table. After the data is loaded, the database tables should look as shown below.
The following are the steps to achieve this using SqlBulkCopy class
Step 1 : Create the database tables using the following sql script
Create table Departments
(
ID int primary key,
Name nvarchar(50),
Location nvarchar(50)
)
GO
Create table Employees
(
ID int primary key,
Name nvarchar(50),
Gender nvarchar(50),
DepartmentId int foreign key references Departments(Id)
)
GO
Step 2 : Create a new empty asp.net web application project. Name it Demo.
Step 3 : Add a new xml file to the project. Name it Data.xml. Copy and paste the following XML.
<Data>
<Department Id="1">
<Name>IT</Name>
<Location>New York</Location>
</Department>
<Department Id="2">
<Name>HR</Name>
<Location>London</Location>
</Department>
<Department Id="3">
<Name>Payroll</Name>
<Location>Mumbai</Location>
</Department>
<Employee Id="1">
<Name>Mark</Name>
<Gender>Male</Gender>
<DepartmentId>1</DepartmentId>
</Employee>
<Employee Id="2">
<Name>John</Name>
<Gender>Male</Gender>
<DepartmentId>1</DepartmentId>
</Employee>
<Employee Id="3">
<Name>Mary</Name>
<Gender>Female</Gender>
<DepartmentId>2</DepartmentId>
</Employee>
<Employee Id="4">
<Name>Steve</Name>
<Gender>Male</Gender>
<DepartmentId>2</DepartmentId>
</Employee>
<Employee Id="5">
<Name>Ben</Name>
<Gender>Male</Gender>
<DepartmentId>3</DepartmentId>
</Employee>
</Data>
Step 4 : Include the database connection string in web.config file
<connectionStrings>
<add name="CS"
connectionString="server=.;database=Sample;integrated security=true"/>
</connectionStrings>
Step 5 : Add a new WebForm to the project. Drag and drop a button control on the webform. Double click the button control to generate the click event handler. Copy and paste the following code in the the click event handler method.
string cs = ConfigurationManager.ConnectionStrings["CS"].ConnectionString;
using (SqlConnection con = new SqlConnection(cs))
{
DataSet ds = new DataSet();
ds.ReadXml(Server.MapPath("~/Data.xml"));
DataTable dtDept = ds.Tables["Department"];
DataTable dtEmp = ds.Tables["Employee"];
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);
}
using (SqlBulkCopy bc = new SqlBulkCopy(con))
{
bc.DestinationTableName = "Employees";
bc.ColumnMappings.Add("ID", "ID");
bc.ColumnMappings.Add("Name", "Name");
bc.ColumnMappings.Add("Gender", "Gender");
bc.ColumnMappings.Add("DepartmentId", "DepartmentId");
bc.WriteToServer(dtEmp);
}
}
Note: Please make sure to include the following using declarations
using System;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
Part 19 - Copying data from one table to another table using SqlBulkCopySuggested VideosPart 16 - Dataset.rejectchanges and dataset.acceptchanges methodsPart 17 - Strongly typed datasets Part 18 - Load xml data into sql server table using sqlbulkcopy
In this video we will discuss copying data from one table to another table. The source and destination tables may be in the same database or in different databases and these database can be on the same sql server or in different servers. In Part 18 we discussed, loading xml data into sql server table using sqlbulkcopy. We will be continuing with the example we worked with in Part 18.
Step 1 : Create a new database. Name it SourceDB. Execute the following sql script to create Departments and Employees tables, and to populate with data.
Create table Departments
(
ID int primary key identity,
Name nvarchar(50),
Location nvarchar(50)
)
GO
Create table Employees
(
ID int primary key identity,
Name nvarchar(50),
Gender nvarchar(50),
DepartmentId int foreign key references Departments(Id)
)
GO
Insert into Departments values ('IT', 'New York')
Insert into Departments values ('HR', 'London')
Insert into Departments values ('Payroll', 'Muumbai')
GO
Insert into Employees values ('Mark', 'Male', 1)
Insert into Employees values ('John', 'Male', 1)
Insert into Employees values ('Mary', 'Female', 2)
Insert into Employees values ('Steve', 'Male', 2)
Insert into Employees values ('Ben', 'Male', 3)
GO
Step 2 : Create another new database. Name it DestinationDB. Execute the just the create sql script to create Departments and Employees tables. Here we have just the structure of the tables and no data. We will be moving data from SourceDB tables to DestinationDB tables.
Step 3 : Include the following 2 connection strings for the Source and Destination databases in the web.config file of the Demo project we created in Part 18.
<connectionStrings>
<add name="SourceCS"
connectionString="server=.;database=SourceDB;integrated security=true"/>
<add name="DestinationCS"
connectionString="server=.;database=DestinationDB;integrated security=true"/>
</connectionStrings>
Step 4 : Copy and paste the following code in the button click event handler method in the code-behind file
string sourceCS =
ConfigurationManager.ConnectionStrings["SourceCS"].ConnectionString;
string destinationCS =
ConfigurationManager.ConnectionStrings["DestinationCS"].ConnectionString;
using (SqlConnection sourceCon = new SqlConnection(sourceCS))
{
SqlCommand cmd = new SqlCommand("Select * from Departments", sourceCon);
sourceCon.Open();
using (SqlDataReader rdr = cmd.ExecuteReader())
{
using (SqlConnection destinationCon = new SqlConnection(destinationCS))
{
using (SqlBulkCopy bc = new SqlBulkCopy(destinationCon))
{
bc.DestinationTableName = "Departments";
destinationCon.Open();
bc.WriteToServer(rdr);
}
}
}
cmd = new SqlCommand("Select * from Employees", sourceCon);
using (SqlDataReader rdr = cmd.ExecuteReader())
{
using (SqlConnection destinationCon = new SqlConnection(destinationCS))
{
using (SqlBulkCopy bc = new SqlBulkCopy(destinationCon))
{
bc.DestinationTableName = "Employees";
destinationCon.Open();
bc.WriteToServer(rdr);
}
}
}
}
Note: Please make sure to include the following using declarations
using System;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
As the column names in the source and destination tables are the same column mappings are
not required.
Part 20 - sqlbulkcopy notifyafter exampleSuggested VideosPart 17 - Strongly typed datasetsPart 18 - Load xml data into sql server table using sqlbulkcopy Part 19 - Copying data from one table to another table using SqlBulkCopy
In this video we will discuss the use 1. BatchSize property2. NotifyAfter property3. SqlRowsCopied event
BatchSize property - Specifies the number of rows in a batch that will be copied to the destination table. The BatchSize property is very important as the performance of data transfer
depends on it. The default batch size is 1. In the example below, BatchSize is set to 10000. This means once the reader has read 10000 rows they will be sent to the database as a single batch to perform the bulk copy operation.
NotifyAfter property - Defines the number of rows to be processed before raising SqlRowsCopied event. In the example below, NotifyAfter property is set to 5000. This means once every 5000 rows are copied to the destination table SqlRowsCopied event is raised.
SqlRowsCopied event - This event is 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.
Let us now understand these properties with an example.
Step 1 : Execute the following SQL script to create Products_Source table and populate it with test data.
Create Table Products_Source
(
[Id] int primary key,
[Name] nvarchar(50),
[Description] nvarchar(250)
)
GO
Declare @Id int
Set @Id = 1
While(@Id <= 300000)
Begin
Insert into Products_Source values
(@Id, 'Product - ' + CAST(@Id as nvarchar(20)),
'Product - ' + CAST(@Id as nvarchar(20)) + ' Description')
Print @Id
Set @Id = @Id + 1
End
GO
Step 2 : Create Products_Destination table
Create Table Products_Destination
(
[Id] int primary key,
[Name] nvarchar(50),
[Description] nvarchar(250)
)
GO
Step 3 : Create a new console application. Name it Demo. Include the database connection string in App.config file
<connectionStrings>
<add name="CS"
connectionString="server=.;database=Sample;integrated security=SSPI"/>
</connectionStrings>
Step 4 : Copy and paste the following code in Program.cs file.
using System;
using System.Configuration;
using System.Data.SqlClient;
namespace Demo
{
class Program
{
static void Main()
{
string cs = ConfigurationManager.ConnectionStrings["CS"].ConnectionString;
using (SqlConnection sourceCon = new SqlConnection(cs))
{
SqlCommand cmd = new
SqlCommand("Select * from Products_Source", sourceCon);
sourceCon.Open();
using (SqlDataReader rdr = cmd.ExecuteReader())
{
using (SqlConnection destinationCon = new SqlConnection(cs))
{
using (SqlBulkCopy bc = new SqlBulkCopy(destinationCon))
{
bc.BatchSize = 10000;
bc.NotifyAfter = 5000;
bc.SqlRowsCopied +=
new SqlRowsCopiedEventHandler(bc_SqlRowsCopied);
bc.DestinationTableName = "Products_Destination";
destinationCon.Open();
bc.WriteToServer(rdr);
}
}
}
}
}
static void bc_SqlRowsCopied(object sender, SqlRowsCopiedEventArgs e)
{
Console.WriteLine(e.RowsCopied + " loaded....");
}
}
}
Please Note : Instead of creating a separate event handler method for SqlRowsCopiedevent and then passing the name of the event handler method toSqlRowsCopiedEventHandler delegate, we can reduce the amount of code we have to write with the help of an anonymous function as shown below.
bc.SqlRowsCopied += (sender, eventArgs) =>
{
Console.WriteLine(eventArgs.RowsCopied + " loaded....");
};
This means the following method is no longer required
static void bc_SqlRowsCopied(object sender, SqlRowsCopiedEventArgs e)
{
Console.WriteLine(e.RowsCopied + " loaded....");
}
Part 21 - Transactions in ADO.NETSuggested VideosPart 18 - Load xml data into sql server table using sqlbulkcopyPart 19 - Copying data from one table to another table using SqlBulkCopy Part 20 - SqlBulkCopy NotifyAfter Example
In this video we will discuss how to implement Transactions in ADO.NET
What is a TransactionA 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. Let's understand this with an example.
We will be using the following Accounts table in this demo
The table has got 2 Accounts (A1 and A2). We want to design a web application totransfer $10 from Account A1 to Account A2. The design of the webform should be as shown below.
When we click "Transfer $10 from Account A1 to Account A2" button, we should subtract 10 from A1 account and add 10 to A2 account. So there will be 2 database UPDATE statements. What do you think will happen if only the first update statement is executed successfully and not the second statement. $10 is deducted from the first account, but not added to the second account. This is definitely not desirable. Either both the statements should succeed or both of them should fail. If one succeeds and other fails we should also rollback the changes made by the first statement to maintain the integrity of the data. This can be achieved using transactions in ado.net.
Step 1 : Create the Accounts table using the following SQL script
Create Table Accounts
(
AccountNumber nvarchar(10) primary key,
CustomerName nvarchar(50),
Balance int
)
GO
Insert into Accounts values('A1', 'Mark', 100)
Insert into Accounts values('A2', 'Steve', 100)
GO
Step 2 : Create a new empty asp.net web application. Name it Demo.
Step 3 : Include connection string in web.config file
<connectionStrings>
<add name="CS"
connectionString="server=.;database=Sample;integrated security=SSPI"/>
</connectionStrings>
Step 4 : Add a WebForm. Copy and paste the following HTML.
<div style="font-family: Arial">
<table border="1" style="background: brown; color: White">
<tr>
<td>
<b>Account Number </b>
</td>
<td>
<asp:Label ID="lblAccountNumber1" runat="server"></asp:Label>
</td>
<td>
<asp:Label ID="lblAccountNumber2" runat="server"></asp:Label>
</td>
</tr>
<tr>
<td>
<b>Customer Name </b>
</td>
<td>
<asp:Label ID="lblName1" runat="server"></asp:Label>
</td>
<td>
<asp:Label ID="lblName2" runat="server"></asp:Label>
</td>
</tr>
<tr>
<td>
<b>Balance </b>
</td>
<td>
<asp:Label ID="lblBalance1" runat="server"></asp:Label>
</td>
<td>
<asp:Label ID="lblBalance2" runat="server"></asp:Label>
</td>
</tr>
</table>
<br />
<asp:Button ID="btnTransfer" runat="server"
Text="Transfer $10 from Account A1 to Account A2"
OnClick="btnTransfer_Click" />
<br />
<br />
<asp:Label ID="lblMessage" runat="server" Font-Bold="true"></asp:Label>
</div>
Step 5 : Copy and paste the following code in the code-behind file.
using System;
using System.Configuration;
using System.Data.SqlClient;
namespace Demo
{
public partial class WebForm1 : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{
if (!IsPostBack)
{
GetAccountsData();
}
}
private void GetAccountsData()
{
string cs = ConfigurationManager.ConnectionStrings["CS"].ConnectionString;
using (SqlConnection con = new SqlConnection(cs))
{
SqlCommand cmd = new SqlCommand("Select * from Accounts", con);
con.Open();
SqlDataReader rdr = cmd.ExecuteReader();
while (rdr.Read())
{
if (rdr["AccountNumber"].ToString() == "A1")
{
lblAccountNumber1.Text = "A1";
lblName1.Text = rdr["CustomerName"].ToString();
lblBalance1.Text = rdr["Balance"].ToString();
}
else
{
lblAccountNumber2.Text = "A2";
lblName2.Text = rdr["CustomerName"].ToString();
lblBalance2.Text = rdr["Balance"].ToString();
}
}
}
}
protected void btnTransfer_Click(object sender, EventArgs e)
{
string cs = ConfigurationManager.ConnectionStrings["CS"].ConnectionString;
using (SqlConnection con = new SqlConnection(cs))
{
con.Open();
// Begin a transaction. The connection needs to
// be open before we begin a transaction
SqlTransaction transaction = con.BeginTransaction();
try
{
// Associate the first update command with the transaction
SqlCommand cmd = new SqlCommand
("Update Accounts set Balance = Balance - 10 where AccountNumber = 'A1'"
, con, transaction);
cmd.ExecuteNonQuery();
// Associate the second update command with the transaction
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();
lblMessage.ForeColor = System.Drawing.Color.Green;
lblMessage.Text = "Transaction committed";
}
catch
{
// If anything goes wrong, rollback the transaction
transaction.Rollback();
lblMessage.ForeColor = System.Drawing.Color.Red;
lblMessage.Text = "Transaction rolled back";
}
}
GetAccountsData();
}
}
}
Testing : Run the apllication and click the "Transfer $10 from Account A1 to Account A2" button. Notice that $10 is deducted from Account A1 and added to Account A2 and the transaction is committed.
Let's now deliberately introduce a change that would crash the application at run time after executing the first update statement.
CHANGE THE FOLLOWING LINEcmd = new SqlCommand("Update Accounts set Balance = Balance + 10 where AccountNumber = 'A2'", con, transaction);
TOcmd = new SqlCommand("Update Accounts1 set Balance = Balance + 10 where AccountNumber = 'A2'", con, transaction);
Run the apllication again and click the "Transfer $10 from Account A1 to Account A2"button. Notice that the transaction is rolled back and the data integrity is not lost.