w3mentor
Learning web technologies simplified!
 
Home / C Sharp / Archive by category 'C# ADO.NET'

C# ADO.NET

How to fill a DataSet Object in C#

Add a Button control named btnLoad and a DataGrid control named dgProducts on the form/page.

using System.Data;
using System.Data.SqlClient;
private void btnLoad_Click(object sender,  System.EventArgs e)
{
    // Create a SqlConnection
    SqlConnection cnn = new SqlConnection(
    "Data Source=(local); Initial Catalog=Northwind;"+
    " Integrated Security=SSPI");
    // Create a SqlCommand
    SqlCommand cmd = cnn.CreateCommand();
    cmd.CommandType = CommandType.Text;
    cmd.CommandText = "SELECT * FROM Products " +
     " ORDER BY ProductName";
    // Set up the DataAdapter and fill the DataSet
    SqlDataAdapter da = new SqlDataAdapter();
    da.SelectCommand = cmd;
    DataSet ds = new DataSet();
    da.Fill(ds, "Products");
    // Display the data on the user interface
    dgProducts.DataSource = ds;
    dgProducts.DataMember = "Products";
}

Using a ADO.NET SqlDataReader Object in C#

Add a Button control (btnGetCustomers) and a ListBox control (lbCustomers) to your form/page.

using System.Data;
using System.Data.SqlClient;
 
private void btnGetCustomers_Click(object sender,
     System.EventArgs e)
{
    // Connect to the database
    SqlConnection cnn = new SqlConnection();
    cnn.ConnectionString = "Data Source=(local);" +
    "Initial Catalog=Northwind;" +
    "Integrated Security=SSPI";
    // Create a new ad hoc query
    // to retrieve customer names
    SqlCommand cmd = cnn.CreateCommand();
    cmd.CommandType = CommandType.Text;
    cmd.CommandText =
        "SELECT CompanyName FROM Customers "
    + " ORDER BY CompanyName";
    // Dump the data to the user interface
    cnn.Open();
    SqlDataReader dr = cmd.ExecuteReader();
    while (dr.Read())
    {
        lbCustomers.Items.Add(dr.GetString(0));
    }
    // Clean up
    dr.Close();
    cnn.Close();
}

What are the different data provider objects in ADO.NET

The five main data provider objects are:

1.Connection
2.Command
3.Parameter
4.DataReader
5.DataAdapter

These are the generic names for the classes defined in System.Data.Common namespace.

Display data on page using datatable, object datasource and detailsview

Declare Object data source in page

<asp:ObjectDataSource  
  ID="ObjectDataSource1"  
  runat="server"  
  TypeName="testship" 
   SelectMethod="GetAllShippers"> 
</asp:ObjectDataSource>

Class to return data:

public class testship
{ 
    private static string _cnnString = ConfigurationManager.ConnectionStrings["NorthwindConnectionString"].ToString(); 
     public static DataTable GetAllShippers()  
    { 
        SqlDataAdapter adp = new SqlDataAdapter( 
            "SELECT * FROM shippers", _cnnString); 
 
        DataSet ds = new DataSet("shippers"); 
        adp.Fill(ds, "shippers"); 
 
        return ds.Tables["shippers"];     
    } 
}

Details view on page to display data:

<asp:DetailsView  
  ID="DetailsView1"  
  runat="server"  
  DataSourceID="ObjectDataSource1" 
  AllowPaging="true"> 
</asp:DetailsView>

Return a datatable using sqldataadapter in C#

public class testship
{ 
    private static string _cnnString = ConfigurationManager.ConnectionStrings["NorthwindConnectionString"].ToString(); 
     public static DataTable GetAllShippers()  
    { 
        SqlDataAdapter adp = new SqlDataAdapter( 
            "SELECT * FROM shippers", _cnnString); 
 
        DataSet ds = new DataSet("shippers"); 
        adp.Fill(ds, "shippers"); 
 
        return ds.Tables["shippers"];     
    } 
}

Connecting to the Products table in the Northwind database using SqlDataSource control

<asp: SqlDataSource ID=”SqlDataSource1” runat=”server”  
    ConnectionString=”<%$ConnectionStrings:NorthwindCnnString %>”  
    SelectCommand=”SELECT * FROM products”> 
</asp:SqlDataSource>

Test connection to access database using oledbconnection in C#

 
using System;
using System.Data;
using System.Data.OleDb;
 
public class Connect {    
 public static void Main () { 
   String connect = "Provider=Microsoft.JET.OLEDB.4.0;data source=.\\my.mdb";
   OleDbConnection con = new OleDbConnection(connect);
   con.Open();
   Console.WriteLine("Made the connection to the access database");
   con.Close();
 }
}

using oledbconnection to read Access data in c#

 
using System;
using System.Data;           
using System.Data.OleDb;     
using System.Collections.Generic;
using System.Text;
 
class Program {
    static void Main(string[] args) {
        OleDbConnection thisConnection = new OleDbConnection(@"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\my.mdb");
        thisConnection.Open();
        OleDbCommand thisCommand = thisConnection.CreateCommand();
        thisCommand.CommandText = "SELECT CustomerID, CompanyName FROM Customers";
        OleDbDataReader thisReader = thisCommand.ExecuteReader();
        while (thisReader.Read()) {
            Console.WriteLine("\t{0}\t{1}",thisReader["CustomerID"], thisReader["CompanyName"]);
        }
        thisReader.Close();
        thisConnection.Close();
    }
}

using excel datasource to load data in c#

<%@ Page Language="C#" %>
<%@ import Namespace="System.Data" %>
<%@ import Namespace="System.Data.OleDb" %>
<script runat="server">
void Page_Load(object sender, EventArgs e) {
string ConnectionString = @"Provider=Microsoft.Jet.OLEDB.4.0; Data Source=C:\myspreadsheet.xls; Extended Properties=""Excel 8.0;HDR=Yes"";";
string CommandText = "select * from [Book$]";
OleDbConnection myConnection = new OleDbConnection(ConnectionString);
OleDbCommand myCommand = new OleDbCommand(CommandText, myConnection);
myConnection.Open();
DataGrid1.DataSource = myCommand.ExecuteReader(CommandBehavior.CloseConnection);
DataGrid1.DataBind();
myConnection.Close();
}
</script>
<html>
<head>
</head>
<body>
<h2>Simple Data Report 
</h2>
<hr size="1" />
<form runat="server">
<asp:datagrid id="DataGrid1" runat="server" EnableViewState="False" ForeColor="Black" BackColor="White" CellPadding="3" GridLines="None" CellSpacing="1">
<HeaderStyle font-bold="True" forecolor="white" backcolor="black"></HeaderStyle>
<ItemStyle backcolor=”red"></ItemStyle>
</asp:datagrid>
</form>
</body>
</html>

Example of using a DELETE command with ExecuteNonQuery method

The ExecuteNonQuery() method executes commands that don’t return a result set, such as INSERT, DELETE, and UPDATE. The ExecuteNonQuery() method returns a single piece of information—the number of affected records.
 
SqlConnection con = new SqlConnection(connectionString);
string sql = "DELETE FROM Employees WHERE EmployeeID = " + empID.ToString();
SqlCommand cmd = new SqlCommand(sql, con);
try
{
con.Open();
int numAff = cmd.ExecuteNonQuery();
HtmlContent.Text += string.Format("<br />Deleted <b>{0}</b> record(s)<br />", numAff);
}
catch (SqlException exc)
{
HtmlContent.Text += string.Format(
"<b>Error:</b> {0}<br /><br />", exc.Message);
}
finally
{
con.Close();
}

Example of using ExecuteScalar() Method

The ExecuteScalar() method returns the value stored in the first field of the first row of a result set generated by the command’s SELECT query.

SqlConnection con = new SqlConnection(connectionString);
string sql = " SELECT COUNT(*) FROM Employees ";
SqlCommand cmd = new SqlCommand(sql, con);
// Open the Connection and get the COUNT(*) value.
con.Open();
int numEmployees = (int)cmd.ExecuteScalar();
con.Close();
// Display the information.
HtmlContent.Text += "<br />Total employees: <b>" +
numEmployees.ToString() + "</b><br />";

Cycle through the records and all the rowsets and build the HTML string

StringBuilder htmlStr = new StringBuilder("");
int i = 0;
do
{
htmlStr.Append("<h2>Rowset: ");
htmlStr.Append(i.ToString());
htmlStr.Append("</h2>");
while (reader.Read())
{
htmlStr.Append("<li>");
// Get all the fields in this row.
for (int field = 0; field < reader.FieldCount; field++)
{
htmlStr.Append(reader.GetName(field).ToString());
htmlStr.Append(": ");
htmlStr.Append(reader.GetValue(field).ToString());
htmlStr.Append("&nbsp;&nbsp;&nbsp;");
}
htmlStr.Append("</li>");
}
htmlStr.Append("<br /><br />");
i++;
} while (reader.NextResult());
// Close the DataReader and the Connection.
reader.Close();
con.Close();
// Show the generated HTML code on the page.
HtmlContent.Text = htmlStr.ToString();

Example connection string that sets a minimum pool size

string connectionString = "Data Source=localhost; Initial Catalog=Northwind;" +
"Integrated Security=SSPI; Min Pool Size=10";
SqlConnection con = new SqlConnection(connectionString);
// Get the connection from the pool (if it exists)
// or create the pool with 10 connections (if it doesn't).
con.Open();
// Return the connection to the pool.
con.Close();

Page.Load event handler to test a connection

// Create the Connection object.
string connectionString =
WebConfigurationManager.ConnectionStrings["Northwind"].ConnectionString;
SqlConnection con = new SqlConnection(connectionString);
try
{
// Try to open the connection.
con.Open();
lblInfo.Text = "<b>Server Version:</b> " + con.ServerVersion;
lblInfo.Text += "<br /><b>Connection Is:</b> " + con.State.ToString();
}
catch (Exception err)
{
// Handle an error by displaying the information.
lblInfo.Text = "Error reading the database. " + err.Message;
}
finally
{
// Either way, make sure the connection is properly closed.
// Even if the connection wasn't opened successfully,
// calling Close() won't cause an error.
con.Close();
lblInfo.Text += "<br /><b>Now Connection Is:</b> " +
con.State.ToString();
}

How to fix SQL Network Interfaces, error: 26

You may get the following error when you have developed an application, and then you move your site to another server (like your web host’s server):

An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connection. (provider: SQL Network Interfaces, error: 26 – Error Locating Server/Instance Specified)

Below you will find 2 causes and their resolutions.
Cause & Fix #1:

Cause: Server not configured for remote connections.

The error is telling us that your server has not been configured to allow remote connections. If your SQL Server has not been configured to allow remote connections, then configure your SQL Server.

Fix:

SQL Server 2000: Open the SQL Server Client Network Utility. On the General tab enable the protocols you need to use. Typically this will be ‘TCP/IP’ and possibly ‘Named Pipes’.

SQL Server 2005 Open SQL Server Configuration Manager. Select “SQL Server 2005 Network Configuration | Protocols for MSSQLSERVER” then enable the protocols you need.

Cause & Fix #2:

Cause: ASP.Net 2.0 Providers are trying to pull from the server’s (nonexistent) Providers database.

By default the machine.config file is trying to pull the Provider information from a SQLExpress database using an invalid connection string named “LocalSQLServer”. Many web servers will not have SQLExpress enabled, and will not have this value set to a valid SQL Server database that is of use to you. In a shared hosting environment, this is especially true, as it would be expected that you would want your Provider information stored in your database and not some single database shared by the other users of that web server.

Fix:

Since you probably cannot access the machine.config file, you need to override the Provider settings in your web.config file, and set the connection string name to your connection string name. The following code comes from the machine.config file and has been modified to first remove each provider before adding the provider.

Add the following code to your web.config file just under the “” tag.

Make sure to replace the 3 occurrences of connectionStringName=”LocalSQLServer” with your connection string name.
This goes in the system.web section of web.config.

<membership>
      <providers>
         <remove name="AspNetSqlMembershipProvider" />
         <add name="AspNetSqlMembershipProvider"
           type="System.Web.Security.SqlMembershipProvider,
           System.Web, Version=2.0.0.0, Culture=neutral,                                
           PublicKeyToken=b03f5f7f11d50a3a"
           connectionStringName="LocalSQLServer"
           enablePasswordRetrieval="false"
           enablePasswordReset="true"
           requiresQuestionAndAnswer="true"
           applicationName="/"
           requiresUniqueEmail="false"
           passwordFormat="Hashed"
           maxInvalidPasswordAttempts="5"
           minRequiredPasswordLength="7"
           minRequiredNonalphanumericCharacters="1"
           passwordAttemptWindow="10"
           passwordStrengthRegularExpression="" />
       </providers>
   </membership> 
   <profile>
       <providers>
          <remove name="AspNetSqlProfileProvider" />
          <add name="AspNetSqlProfileProvider"
             connectionStringName="LocalSQLServer"                           
             applicationName="/"
             type="System.Web.Profile.SqlProfileProvider,
             System.Web, Version=2.0.0.0, Culture=neutral,                   
             PublicKeyToken=b03f5f7f11d50a3a" />
        </providers>   
   </profile>
   <roleManager>
        <providers>
          <remove name="AspNetSqlRoleProvider" />
          <add name="AspNetSqlRoleProvider"
             connectionStringName="LocalSQLServer"
             applicationName="/"
             type="System.Web.Security.SqlRoleProvider,
             System.Web, Version=2.0.0.0, Culture=neutral,                               
             PublicKeyToken=b03f5f7f11d50a3a" />
        </providers>
   </roleManager>

Your database must be configured for the ASP.Net 2.0 Providers.

Illustrate the use of ExecuteReader method in C#

The ExecuteReader() method executes commands that return rows, such as a SQL SELECT statement. The returned rows are located in an OdbcDataReader, an OleDbDataReader, a SqlDataReader, or an OracleDataReader, depending on which Data Provider you are using.

string cmdString = "SELECT user_name, pass_word FROM LogIn ";
SqlCommand sqlCommand = new SqlCommand();
sqlCommand.Connection = sqlConnection;
sqlCommand.CommandType = CommandType.Text;
sqlCommand.CommandText = cmdString;
sqlDataReader = sqlCommand.ExecuteReader();

How to create a SqlCommand object in C#

This example demonstrates the following functionalities:
1. Create a SqlCommand object.
2. Create two SqlParameter objects.
3. Initialize two SqlParameter objects.
4. Initialize the SqlCommand object.
5. Add two Parameter objects into the Parameters collection of the Command object sqlCommand.

string cmdString = "SELECT user_name, pass_word FROM LogIn ";
cmdString += "WHERE (user_name LIKE @Param1 ) AND (pass_word LIKE @Param2)";
SqlParameter paramUserName = new SqlParameter();
SqlParameter paramPassWord = new SqlParameter();
SqlCommand sqlCommand As New SqlCommand();
paramUserName.ParameterName = "@Param1";
paramUserName.Value = txtUserName.Text;
paramPassWord.ParameterName = "@Param2";
paramPassWord.Value = txtPassWord.Text;
sqlCommand.Connection = sqlConnection;
sqlCommand.CommandType = CommandType.Text;
sqlCommand.CommandText = cmdString;
sqlCommand.Parameters.Add(paramUserName);
sqlCommand.Parameters.Add(paramPassWord);

Example methods to add SqlParameter objects in C#

To add Parameter objects to an Parameters collection of a Command object, two popular ways are generally adopted, Add() method and AddWithValue() method.

SqlParameter paramUserName = new SqlParameter();
SqlParameter paramPassWord = new SqlParameter();
paramUserName.ParameterName = "@Param1";
paramUserName.Value = txtUserName.Text;
paramPassWord.ParameterName ="@Param2" ;
paramPassWord.Value = txtPassWord.Text;
sqlCommand.Parameters.Add(paramUserName);
sqlCommand.Parameters.Add(paramPassWord);
sqlCommand.Parameters.AddWithValue("@Param1", txtUserName.Text);
sqlCommand.Parameters.AddWithValue("@Param2", txtPassWord.Text);

Initialize the property of a SqlParameter object in C#

In this example, two ParameterName properties are assigned with two dynamic parameters, “ @Param1 ” and “ @Param2 ” , respectively. Both Param1 and Param2 are nominal names of the dynamic parameters and an @ symbol is prefi xed before each parameter since this is the requirement of the SQL Server database when a dynamic parameter is utilized in an SQL statement.

SqlParameter paramUserName = new SqlParameter();
SqlParameter paramPassWord = new SqlParameter();
paramUserName.ParameterName = "@Param1";
paramUserName.Value = txtUserName.Text;
paramPassWord.ParameterName = "@Param2";
paramPassWord.Value = txtPassWord.Text;

Four constructors of the SqlParameter class

SqlParameter sqlParameter = new SqlParameter();
SqlParameter sqlParameter = new SqlParameter(ParamName, objValue);
SqlParameter sqlParameter = new SqlParameter(ParamName, sqlDbType);
SqlParameter sqlParameter = new SqlParameter(ParamName, sqlDbType, intSize);

Latest Tutorials & Code Snippets

Reference Guides