The use of a parameterized SQL statement (C#)

Posted: February 10, 2012 in Aspx.NET Posts
Tags: , , , , ,
<%@ Page Language="C#" %>
<%@ Import Namespace="System.Data" %>
<%@ Import Namespace="System.Data.SqlClient" %>
<%@ Import Namespace="System.Configuration" %>

// "server">
    protected void Page_Load(object sender, EventArgs e)
    {
       if (!Page.IsPostBack
       {
            SqlConnection MyConnection;
            SqlCommand MyCommand;
            SqlDataReader MyReader;
            SqlParameter CityParam;
            SqlParameter ContactParam;

            MyConnection = new SqlConnection();
            MyConnection.ConnectionString = ConfigurationManager.ConnectionStrings["DSN_Northwind"].ConnectionString;

            MyCommand = new SqlCommand();
            MyCommand.CommandText = " SELECT * FROM CUSTOMERS WHERE CITY = @CITY AND CONTACTNAME = @CONTACT ";
            MyCommand.CommandType = CommandType.Text;
            MyCommand.Connection = MyConnection;

            CityParam = new SqlParameter();
            CityParam.ParameterName = "@CITY";
            CityParam.SqlDbType = SqlDbType.VarChar;
            CityParam.Size = 15;
            CityParam.Direction = ParameterDirection.Input;
            CityParam.Value = "Berlin";

            ContactParam = new SqlParameter();
            ContactParam.ParameterName = "@CONTACT";
            ContactParam.SqlDbType = SqlDbType.VarChar;
            ContactParam.Size = 15;
            ContactParam.Direction = ParameterDirection.Input;
            ContactParam.Value = "Maria Anders";

            MyCommand.Parameters.Add(CityParam);
            MyCommand.Parameters.Add(ContactParam);

            MyCommand.Connection.Open();
            MyReader = MyCommand.ExecuteReader(CommandBehavior.CloseConnection);

            gvCustomers.DataSource = MyReader;
            gvCustomers.DataBind();

            MyCommand.Dispose();
            MyConnection.Dispose();           
       }
    }
</script>

<html>
<body>
    <form id="form1" runat="server">
    <div>
        GridView ID="gvCustomers" runat="server">
        </asp:GridView>    
    </div>
    </form>
</body>
</html>
File: Web.config

<configuration>

  <connectionStrings>
        <add name="DSN_Northwind" 
             connectionString="Data Source=localhost\SQLEXPRESS;Initial Catalog=Northwind;Integrated Security=True"
             providerName="System.Data.SqlClient" />
    </connectionStrings>

</configuration>

Advertisements
Comments
  1. Carson Ratkowski says:

    Thanks for all your efforts that you have put in this. Very interesting info. “He who despairs over an event is a coward, but he who holds hope for the human condition is a fool.” by Albert Camus.

    Like

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s