ExecuteNonQuery ExecuteScalar ExecuteReader Example In Asp.Net

SqlCommand ExecuteNonQuery, ExecuteScalar, ExecuteReader and ExecuteXmlReader Method Examples in Asp.Net C# VB.

1. SqlCommand ExecuteNonQuery Example

---

How and when to use ExecuteNonQuery method of SqlCommand in asp.net using C# and VB.NET.

2. ExecuteScalar

---

This example explains use of ExecuteScalar .

3. ExecuteReader

---

How to use ExecuteReader Method using C# and VB.NET.

4. ExecuteXmlReader

---

ExecuteXmlReader method of SqlCommand in asp.net.

Read more >>

ExecuteXmlReader Example In Asp.Net C# VB.Net

This example explains how to use ExecuteXmlReader Method In Asp.Net Using C# And VB

You may also read how to use NonQuery, Reader and ExecuteScalar Methods of SqlCommand.

This method executes the command and builds XmlReader, for it to work with fetching database records we need to use FOR XML AUTO clause in sql query.

C# CODE

---

string strConnection = ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;
        string strSelect = "SELECT Username,Password FROM Users FOR XML AUTO";
        
        SqlConnection con = new SqlConnection(strConnection);
        SqlCommand cmd = new SqlCommand(strSelect, con);
        
        con.Open();
        XmlReader reader = cmd.ExecuteXmlReader();
        reader.Read();
        XmlDocument doc = new XmlDocument();
        doc.Load(reader);
        doc.Save("test.xml");
        reader.Close();
        con.Close();

VB.NET

---

Dim strConnection As String = ConfigurationManager.ConnectionStrings("ConnectionString").ConnectionString
Dim strSelect As String = "SELECT Username,Password FROM Users FOR XML AUTO"

Dim con As New SqlConnection(strConnection)
Dim cmd As New SqlCommand(strSelect, con)

con.Open()
Dim reader As XmlReader = cmd.ExecuteXmlReader()
reader.Read()
Dim doc As New XmlDocument()
doc.Load(reader)
doc.Save("test.xml")
reader.Close()
con.Close()

Read more >>

ExecuteScalar Example In Asp.Net C# VB

This example describes when and how to use SqlCommand ExecuteScalar Method in Asp.Net Using C# and VB

Other Methods like ExecuteNonQuery , Reader and XmlReader are also explained.

ExecuteScalar Executes the query and returns the first column of first row in result set returned by query, other columns and rows are ignored by this method.

It is best used for retrieving a single value eg record count, max,min,sum or getting the identity values of inserted record from database.

C# CODE

---

string strConnection = ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;
        string strSelect = "SELECT COUNT(*) FROM Users WHERE Username = @Username AND Password = @Password";
        
        SqlConnection con = new SqlConnection(strConnection);
        SqlCommand cmd = new SqlCommand();
        cmd.Connection = con;
        cmd.CommandType = CommandType.Text;
        cmd.CommandText = strSelect;

        SqlParameter username = new SqlParameter("@Username", SqlDbType.VarChar, 50);
        username.Value = txtUserName.Text.Trim().ToString();
        cmd.Parameters.Add(username);

        SqlParameter password = new SqlParameter("@Password", SqlDbType.VarChar, 50);
        password.Value = txtPassword.Text.Trim().ToString();
        cmd.Parameters.Add(password);

        con.Open();
        int result = (Int32)cmd.ExecuteScalar();
        con.Close();

        if (result >= 1)
            Response.Redirect("Default.aspx");
        else
            lblMsg.Text = "Incorrect Username or Password";

VB.NET

---

Dim strConnection As String = ConfigurationManager.ConnectionStrings("ConnectionString").ConnectionString
Dim strSelect As String = "SELECT COUNT(*) FROM Users WHERE Username = @Username AND Password = @Password"

Dim con As New SqlConnection(strConnection)
Dim cmd As New SqlCommand()
cmd.Connection = con
cmd.CommandType = CommandType.Text
cmd.CommandText = strSelect

Dim username As New SqlParameter("@Username", SqlDbType.VarChar, 50)
username.Value = txtUserName.Text.Trim().ToString()
cmd.Parameters.Add(username)

Dim password As New SqlParameter("@Password", SqlDbType.VarChar, 50)
password.Value = txtPassword.Text.Trim().ToString()
cmd.Parameters.Add(password)

con.Open()
Dim result As Integer = DirectCast(cmd.ExecuteScalar(), Int32)
con.Close()

If result >= 1 Then
 Response.Redirect("Default.aspx")
Else
 lblMsg.Text = "Incorrect Username or Password"
End If

To retrieve Identity of inserted record we can write code like mentined below

string strConnection = ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;
        string strInsert = "INSERT INTO Users (Username,Password) VALUES (@Username,@Password);"
                          + "SELECT CAST(scope_identity() AS int)";
        SqlConnection con = new SqlConnection(strConnection);
        SqlCommand cmd = new SqlCommand(strInsert, con);
        
        SqlParameter username = new SqlParameter("@Username", SqlDbType.VarChar, 50);
        username.Value = txtUserName.Text.Trim().ToString();
        cmd.Parameters.Add(username);

        SqlParameter password = new SqlParameter("@Password", SqlDbType.VarChar, 50);
        password.Value = txtPassword.Text.Trim().ToString();
        cmd.Parameters.Add(password);

        con.Open();
        int identity = (Int32)cmd.ExecuteScalar();
        con.Close();

Read more >>

ExecuteReader Example In Asp.Net C# VB.Net

SqlCommand ExecuteReader Method Example in Asp.Net Using C# And Vb

This method Execute the command and builds or populate the SqlDataReader object. It is used for accessing data when query returns a set of records for display or navigation purpose.

It provides a forward only, read only connected recordset which means it needs open Sql Connection untill DataReader is open.

Common usage of ExecuteReader Method can be populating a dropdownlist or listbox or retrieving binary files from database.

I have also explained NonQuery , Scalar and XmlReader methods.

C# CODE

---

protected void Page_Load(object sender, EventArgs e)
    {
        string strConnection = ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;
        string strSelect = "SELECT Username FROM Users";

        //Create SQL Connection And SQLCommand
        SqlConnection con = new SqlConnection(strConnection);
        SqlCommand cmd = new SqlCommand();
        cmd.Connection = con;
        cmd.CommandType = CommandType.Text;
        cmd.CommandText = strSelect;

        con.Open();
        SqlDataReader dReader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
            while (dReader.Read())
            {
                ddlUsers.Items.Add(dReader["Username"].ToString());
            }
        dReader.Close();
    }

VB.NET

---

Protected Sub Page_Load(sender As Object, e As EventArgs)
 Dim strConnection As String = ConfigurationManager.ConnectionStrings("ConnectionString").ConnectionString
 Dim strSelect As String = "SELECT Username FROM Users"

 'Create SQL Connection And SQLCommand
 Dim con As New SqlConnection(strConnection)
 Dim cmd As New SqlCommand()
 cmd.Connection = con
 cmd.CommandType = CommandType.Text
 cmd.CommandText = strSelect

 con.Open()
 Dim dReader As SqlDataReader = cmd.ExecuteReader(CommandBehavior.CloseConnection)
 While dReader.Read()
  ddlUsers.Items.Add(dReader("Username").ToString())
 End While
 dReader.Close()
End Sub

Hope this helps.

Read more >>

SqlCommand ExecuteNonQuery Example C# VB Asp.Net

This is Example of how to use ExecuteNonQuery Method Of SqlCommand In Asp.Net Using C# And VB.

ExecuteNonQuery() Executes a Transact-SQL statement, It returns -1 for SELECT statements and Number of Rows affected for all INSERT DELETE AND UPDATE statements.

I have also explained Other methods of SqlCommand.

C# CODE

---

protected void btnSignIn_Click(object sender, EventArgs e)
    {
        string strConnection = ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;
        string strInsert = "INSERT INTO Users (Username,Password) VALUES(@Username,@Password)";
        SqlConnection con = new SqlConnection(strConnection);
        SqlCommand cmd = new SqlCommand();
        cmd.Connection = con;
        cmd.CommandType = CommandType.Text;
        cmd.CommandText = strInsert;

        SqlParameter username = new SqlParameter("@Username", SqlDbType.VarChar, 50);
        username.Value = txtUserName.Text.Trim().ToString();
        cmd.Parameters.Add(username);

        SqlParameter password = new SqlParameter("@Password", SqlDbType.VarChar, 50);
        password.Value = txtPassword.Text.Trim().ToString();
        cmd.Parameters.Add(password);

        con.Open();
        int result = cmd.ExecuteNonQuery();
       
        con.Close();
    }

VB.NET

---

Protected Sub btnSignIn_Click(sender As Object, e As EventArgs)
 Dim strConnection As String = ConfigurationManager.ConnectionStrings("ConnectionString").ConnectionString
 Dim strInsert As String = "INSERT INTO Users (Username,Password) VALUES(@Username,@Password)"
 Dim con As New SqlConnection(strConnection)
 Dim cmd As New SqlCommand()
 cmd.Connection = con
 cmd.CommandType = CommandType.Text
 cmd.CommandText = strInsert

 Dim username As New SqlParameter("@Username", SqlDbType.VarChar, 50)
 username.Value = txtUserName.Text.Trim().ToString()
 cmd.Parameters.Add(username)

 Dim password As New SqlParameter("@Password", SqlDbType.VarChar, 50)
 password.Value = txtPassword.Text.Trim().ToString()
 cmd.Parameters.Add(password)

 con.Open()
 Dim result As Integer = cmd.ExecuteNonQuery()

 con.Close()
End Sub

Hope this helps

Read more >>