Create SqlCommand from sql statement and connection : SqlCommand « ADO.net Database « ASP.NET Tutorial






  1. ASP.NET Tutorial
  2. ADO.net Database
  3. SqlCommand
<%@ Page Language="C#" AutoEventWireup="true" CodeFile="Default.aspx.cs" Inherits="DataReader" %>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.1//EN" "http://www.w3.org/TR/xhtml11/DTD/xhtml11.dtd">

<html xmlns="http://www.w3.org/1999/xhtml" >
<head runat="server">
    <title>Untitled Page</title>
</head>
<body>
    <form id="form1" runat="server">
    <div>
      <h2>Employees</h2>
    <asp:Literal runat="server" ID="HtmlContent" />
    </div>
    </form>
</body>
</html>

File: Default.aspx.cs

using System;
using System.Data;
using System.Configuration;
using System.Collections;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using System.Data.SqlClient;
using System.Text;
using System.Web.Configuration;

public partial class DataReader : System.Web.UI.Page
{
    protected void Page_Load(object sender, EventArgs e)
    {
    string connectionString = WebConfigurationManager.ConnectionStrings["Northwind"].ConnectionString;

    SqlConnection con = new SqlConnection(connectionString);
    string sql = "SELECT * FROM Employees";
    SqlCommand cmd = new SqlCommand(sql, con);

    con.Open();
    SqlDataReader reader = cmd.ExecuteReader();

    StringBuilder htmlStr = new StringBuilder("");
    while (reader.Read())
    {
      htmlStr.Append("<li>");
      htmlStr.Append(reader["TitleOfCourtesy"]);
      htmlStr.Append(" <b>");
      htmlStr.Append(reader.GetString(1));
      htmlStr.Append("</b>, ");
      htmlStr.Append(reader.GetString(2));
      htmlStr.Append(" - employee from ");
      htmlStr.Append(reader.GetDateTime(6).ToString("d"));
      htmlStr.Append("</li>");
    }

    reader.Close();
    con.Close();

    HtmlContent.Text = htmlStr.ToString();
    }
}

File: Web.config

<?xml version="1.0"?>
<configuration xmlns="http://schemas.microsoft.com/.NetConfiguration/v2.0">
  <connectionStrings>
    <add name="Northwind" connectionString="Data Source=localhost;Initial Catalog=Northwind;Integrated Security=SSPI"/>
  </connectionStrings>

  <appSettings>
    <add key="factory" value="System.Data.SqlClient" />
    <add key="employeeQuery" value="SELECT * FROM Employees" />
  </appSettings>

  <system.web>
    <compilation debug="true"/>
    <authentication mode="Windows"/>
  </system.web>
</configuration>








18.3.SqlCommand
18.3.1.Create SqlCommand from sql statement and connection
18.3.2.Executing a Command
18.3.3.Executing a Command with Parameters
18.3.4.Returning a Single Value
18.3.5.Read scalar data by using SqlCommand
18.3.6.Execute insert command by using SqlCommand
18.3.7.Execuate select command by using the SqlCommand
18.3.8.Execute update command
18.3.9.Attach SqlCommand to DataGrid
18.3.10.Pass a CommandBehavior.CloseConnection parameter to the ExecuteReader() method.
18.3.11.Executing Asynchronous Database Commands
18.3.12.Avoid SQL injection
18.3.13.Avoid SQL Injection attack
18.3.14.Browser Snoop