CODEWITHSUNDEEP
c#asp.netsqlconnection
user2454370
user2454370

Reputation: 21

What is the optimal / standard method of using a sql connection?

protected void populateDataGrid()
{
    string connectionString = configurationManager.ConnectionStrings["myConnectionString"].ConnectionString;
    string command = "select * from student";

    SqlDataAdapter dataAdapter = new SqlDataAdapter(command, connectionString);
    DataSet data = new DataSet();

    dataAdapter.Fill(data);
    GridView1.DataSource = data;
    GridView1.DataBind();
}

protected void Button2_Click(object sender, EventArgs e)
{
    string connectionString = ConfigurationManager.ConnectionStrings["sqlstudentConnectionString"].ConnectionString;
    string command = @"INSERT INTO [student] (studentID, studentFirstName, studentLastName) 
                       VALUES (" + TextID.Text + ", '" + TextFirstName.Text + "', '" + TextLastName.Text + "')";
    SqlConnection sqlConnection = new SqlConnection(connectionString);

    SqlCommand cmd = new SqlCommand();
    cmd.CommandType = System.Data.CommandType.Text;
    cmd.CommandText = command;
    cmd.Connection = sqlConnection;

    sqlConnection.Open();
    cmd.ExecuteNonQuery();
    sqlConnection.Close();

    TextID.Text = "";
    TextFirstName.Text = "";
    TextLastName.Text = "";
    populateDataGrid();
}

The first function gets all the table data and dumps it to a gridview. The second function takes input and inserts it into the database. How can these functions be condensed or simplified?

Upvotes: 2

Views: 159

Answers (2)

Freelancer
Freelancer

Reputation: 9074

In Button2_Click(object sender, EventArgs e) method , you need to use parametrized query to avoid SQL Injection. That is the standard way.

protected void Button2_Click(object sender, EventArgs e)
{
    string connectionString = ConfigurationManager.ConnectionStrings["sqlstudentConnectionString"].ConnectionString;
    string command = @"INSERT INTO [student] (
        studentID, studentFirstName, studentLastName
    ) VALUES (
        @studID, @FName, @LName
    )";

    using (SqlConnection sqlConnection = new SqlConnection(connectionString))
    using (SqlCommand cmd = new SqlCommand())
    {
        cmd.CommandType = System.Data.CommandType.Text;
        cmd.CommandText = command;
        cmd.Parameters.AddWithValue("@studID", TextID.Text);
        cmd.Parameters.AddWithValue("@FName", TextFirstName.Text);
        cmd.Parameters.AddWithValue("@LName", TextLastName.Text);
        cmd.Connection = sqlConnection;

        sqlConnection.Open();
        cmd.ExecuteNonQuery();
        sqlConnection.Close();
    }

    TextID.Text = "";
    TextFirstName.Text = "";
    TextLastName.Text = "";
    populateDataGrid();
}

Hope Its Helpful.

Upvotes: 3

Jon Skeet
Jon Skeet

Reputation: 1503469

How can these functions be condensed or simplified?

I would focus on correctness before simplification. Currently I can see at least two problems with the code:

  • You should absolutely use parameterized SQL instead of putting the values into the SQL itself. Your current code is prone to SQL injection attacks.
  • You should use using statements so that connection and command are both closed automatically even if exceptions are thrown.

Then in terms of simplification:

  • You can use the SqlCommand constructor which takes the text and connection - the type defaults to Text anyway.
  • I would personally try to separate the UI code from the storage code, at least for a non-trivial project. You should look at ASP.NET MVC, at least to get some idea of separation, even if you don't change to start using it.

Upvotes: 5

Related Questions