What is the correct syntax for establishing an SQL connection and creating SQL Commands with VS2012?

Question

I'm not really sure where the SqlConnection, SqlCommand and the Open()/Close() goes. I want to use just the single variable cmd throughout the program, hence not using the SqlCommand cmd = new SqlCommand('SELCT * FROM blabla); format.

EDIT: My code below results to the textbox having the text "System.Data.SqlClient.SqlCommand" when i click the button.

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;
using System.Data.SqlClient;
using System.Data.SqlTypes;
namespace WindowsFormsApplication2
{
    public partial class Form1 : Form
    {
        SqlConnection con = new SqlConnection(@"Data Source=EDIOTH\SQLEXPRESS;
        Initial Catalog=Try; Integrated Security=SSPI");
        SqlCommand cmd = new SqlCommand();


        public Form1()
        {            

            InitializeComponent();

        }

        private void button1_Click(object sender, EventArgs e)
        {
            con.Open();
            cmd.CommandText = "SELECT Pnt_Lname FROM PATIENT WHERE Pnt_ID = 1;";
            txtBox1.Text = cmd.ToString();
            con.Close();
        }
    }
}

Damith · Accepted Answer · 2013-12-30 03:56:36Z

1

you can create constant string to hold the connection string and then you can do as below in your button1_Click

you don't need to call the close method of sql connection when you use using block as below

using(SqlConnection con = new SqlConnection(connectionString))
using(SqlCommand cmd = con.CreateCommand())
{
   cmd.CommandText = "SELECT Pnt_Lname FROM PATIENT WHERE Pnt_ID = 1";
   con.Open();
   txtBox1.Text =cmd.ExecuteScalar() as string;
}

And also if you need to read Pnt_Lname from database you better use ExecuteScalar method

edited Dec 30, 2013 at 3:56

answered Dec 30, 2013 at 3:47

Damith

63.2k13 gold badges106 silver badges154 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Shrumms Dorke Over a year ago

Yes that sounds right, although I think I might be missing a library or something. Error 1 'System.Data.SqlClient.SqlConnection' does not contain a definition for 'ExecuteScalar' and no extension method 'ExecuteScalar' accepting a first argument of type 'System.Data.SqlClient.SqlConnection' could be found (are you missing a using directive or an assembly reference?)

Damith Over a year ago

@ShrummsDorke it should be cmd.ExecuteScalar()

Szymon · Accepted Answer · 2013-12-30 03:49:24Z

0

You can use this structure. Use using to properly close and dispose of SqlConnection.

Also, you can define the connection string in your config file and use it from there.

using (SqlConnection conn = new SqlConnection(@"Data Source=EDIOTH\SQLEXPRESS;
    Initial Catalog=Try; Integrated Security=SSPI"))
{
    conn.Open();

    SqlCommand command = conn.CreateCommand();
    command.CommandText = "SELECT Pnt_Lname FROM PATIENT WHERE Pnt_ID = 1";

    txtBox1.Text = (String)command.ExecuteScalar();

}

answered Dec 30, 2013 at 3:49

Szymon

43k16 gold badges99 silver badges115 bronze badges

Comments

Shrumms Dorke · Accepted Answer · 2014-01-04 02:33:27Z

In case this would be of help to anyone, this is the answer to my question:

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;
using System.Data.SqlClient;

namespace Book
{
    public partial class frmBook : Form
    {
        SqlConnection con =
            new SqlConnection(@"Data Source=EDIOTH\SQLEXPRESS;
                Initial Catalog=XXDB; Integrated Security=SSPI");
        SqlCommand cmd;
        public frmBook()
        {
            InitializeComponent();
        }

        private void frmBook_Load(object sender, EventArgs e)
        {
            con.Open();
            cmd = new SqlCommand("SELECT min(Book_ID) FROM Book;",con);
            txtID.Text = cmd.ExecuteScalar().ToString();
            cmd = new SqlCommand("SELECT title FROM Book WHERE Book_ID = '" 
                + txtID.Text + "'", con);
            txtTitle.Text = cmd.ExecuteScalar().ToString();
            con.Close();
            btnSave.Enabled = false;
        }

        private void btnNext_Click(object sender, EventArgs e)
        {
            int count = int.Parse(txtID.Text) + 1;
            con.Open();
            cmd = new SqlCommand("SELECT title FROM Book WHERE Book_ID = '"
                + count.ToString() +"'", con);
            txtTitle.Text = cmd.ExecuteScalar().ToString();
            txtID.Text = count.ToString();
            con.Close();
        }

        private void btnNew_Click(object sender, EventArgs e)
        {
            txtID.Text = "";
            txtTitle.Text = "";
            txtAuthor.Text = "";
            btnNew.Enabled = false;
            btnSave.Enabled = true;
        }

        private void btnSave_Click(object sender, EventArgs e)
        {
            con.Open();
            cmd = new SqlCommand("INSERT INTO Book (Book_ID, Title, Author) " +
                "VALUES ('"+ txtID.Text +
                "','"+ txtTitle.Text +
                "','"+ txtAuthor.Text +"');", con);
            cmd.ExecuteNonQuery();
            con.Close();
            MessageBox.Show("Data saved!");
            btnSave.Enabled = false;
        }

        private void btnClose_Click(object sender, EventArgs e)
        {
            this.Close();
        }
    }
}

Collectives™ on Stack Overflow

What is the correct syntax for establishing an SQL connection and creating SQL Commands with VS2012?

3 Answers 3

2 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related