1 Replies - 4903 Views - Last Post: 22 October 2014 - 11:57 AM

#1 andrewsw   User is offline

  • Bouncy!
  • member icon

Reputation: 6563
  • View blog
  • Posts: 26,615
  • Joined: 12-December 12

Simple SQL Parameters Example

Posted 15 September 2014 - 10:22 AM

The purpose of this snippet is simply to demonstrate how parameters are added to a SQL statement (for SQL Server) and then values are supplied for these parameters.

SqlCommand.Parameters Property :MSDN

I used a WinForm with a DataGridView, a TextBox (for Country, e.g. 'France'), a DateTimePicker and a Button. I used the Northwind sample database - the OrderDates start from 1997. Clicking the Button populates the DataGridView.

    Private Sub btnGo_Click(sender As Object, e As EventArgs) Handles btnGo.Click
        Dim sConn As String = "Data Source=.\SQLExpress;Initial Catalog=northwind;Integrated Security=True"

        Dim sSql As String = "SELECT * FROM Orders WHERE ShipCountry = @Country AND OrderDate > @OrderDate"

        Dim conn As New SqlConnection(sConn)
        Dim cmd As New SqlCommand(sSql, conn)

        'could configure parameters individually:
        cmd.Parameters.Add("@Country", SqlDbType.NVarChar)
        cmd.Parameters("@Country").Value = txtCountry.Text

        'easier to add in one go, and let the database deal with the data-type:
        cmd.Parameters.AddWithValue("@OrderDate", dtpOrderDate.Value)

        Dim adapter As New SqlDataAdapter(cmd)
        Dim ds As New DataSet()
        adapter.Fill(ds, "OrdersTable")

        dgvData.DataSource = ds
        dgvData.DataMember = "OrdersTable"
    End Sub

Notice, significantly, that we don't have to worry about formatting the DateTimePicker.Value.

Attached Image

(This could actually serve as a basic template or application for testing SQL statements and parameters.)

This post has been edited by andrewsw: 15 September 2014 - 12:41 PM


Is This A Good Question/Topic? 1
  • +

Replies To: Simple SQL Parameters Example

#2 andrewsw   User is offline

  • Bouncy!
  • member icon

Reputation: 6563
  • View blog
  • Posts: 26,615
  • Joined: 12-December 12

Re: Simple SQL Parameters Example

Posted 22 October 2014 - 11:57 AM

Running my snippet through this converter gives this C# code:
private void btnGo_Click(object sender, EventArgs e)
{
	string sConn = "Data Source=.\\SQLExpress;Initial Catalog=northwind;Integrated Security=True";

	string sSql = "SELECT * FROM Orders WHERE ShipCountry = @Country AND OrderDate > @OrderDate";

	SqlConnection conn = new SqlConnection(sConn);
	SqlCommand cmd = new SqlCommand(sSql, conn);

	//could configure parameters individually:
	cmd.Parameters.Add("@Country", SqlDbType.NVarChar);
	cmd.Parameters("@Country").Value = txtCountry.Text;

	//easier to add in one go, and let the database deal with the data-type:
	cmd.Parameters.AddWithValue("@OrderDate", dtpOrderDate.Value);

	SqlDataAdapter adapter = new SqlDataAdapter(cmd);
	DataSet ds = new DataSet();
	adapter.Fill(ds, "OrdersTable");

	dgvData.DataSource = ds;
	dgvData.DataMember = "OrdersTable";
}

This post has been edited by andrewsw: 22 October 2014 - 11:59 AM

Was This Post Helpful? 0
  • +
  • -

Page 1 of 1