5 Replies - 751 Views - Last Post: 06 April 2010 - 08:46 AM Rate Topic: -----

#1 megglz   User is offline

  • D.I.C Regular
  • member icon

Reputation: 11
  • View blog
  • Posts: 414
  • Joined: 22-August 08

making code more efficient

Posted 05 April 2010 - 05:49 AM

Hi, I've coded the following simple form in order to get myself used to making web apps with sql stuff. I'm just wondering if there are alternative/shorter ways that I could have coded it or is it efficient as it is?

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Data.Common;
using System.IO;
using System.Data.SqlClient;

namespace WindowsFormsApplication2
{
    public partial class Form_myForm : Form
    {
        public SqlConnection conn; 

        public Form_myForm()
        {
            InitializeComponent();
        }

        public void connectToDatabase(String dataSource, String initialCat, String userID, String password)
        {
            SqlConnectionStringBuilder csb = new SqlConnectionStringBuilder();
            csb.DataSource = dataSource;
            csb.InitialCatalog = initialCat;
            csb.UserID = userID;
            csb.Password = password;

            conn = new SqlConnection(csb.ToString());
        }

        public void Buttonsubmit_Click(object sender, EventArgs e)
        {
            connectToDatabase("MY-PC\\SQLEXPRESS", "tempdb", "sa", "mypassword");
            String firstName = textBox1.Text;
            String surname = textBox2.Text;
            String city = textBox3.Text;
            String query = "INSERT INTO test VALUES ('" + firstName +"', '" + surname + "', '" + city + "')";
            SqlCommand cmd = new SqlCommand(query, conn);
            SqlDataAdapter da = new SqlDataAdapter(cmd);
            DataTable table = new DataTable();
            da.Fill(table);
        }   
    }
}




Is This A Good Question/Topic? 0
  • +

Replies To: making code more efficient

#2 SwiftStriker00   User is offline

  • No idea why my code works
  • member icon

Reputation: 439
  • View blog
  • Posts: 1,617
  • Joined: 25-December 08

Re: making code more efficient

Posted 05 April 2010 - 06:03 AM

You will be vulnerable to SQL injections if you dont purify the textboxes, but aside from that theres not much more you can do to improve it
Was This Post Helpful? 2
  • +
  • -

#3 eclipsed4utoo   User is offline

  • Not Your Ordinary Programmer
  • member icon

Reputation: 1536
  • View blog
  • Posts: 5,972
  • Joined: 21-March 08

Re: making code more efficient

Posted 05 April 2010 - 06:12 AM

The biggest problem here is that you are opening yourself up to SQL Injection attacks because you take plain text directly from the textboxes and put it into the query.

Here is another way of doing it...

public string connectToDatabase(String dataSource, String initialCat, String userID, String password)
{
    SqlConnectionStringBuilder csb = new SqlConnectionStringBuilder();
    csb.DataSource = dataSource;
    csb.InitialCatalog = initialCat;
    csb.UserID = userID;
    csb.Password = password;

    return csb.ToString();
}

public void Buttonsubmit_Click(object sender, EventArgs e)
{
     using (SqlConnection conn = new SqlConnection(connectToDatabase("MY-PC\\SQLEXPRESS", "tempdb", "sa", "mypassword"))
     {
         using (SqlCommand cmd = conn.CreateCommand())
         {
             cmd.CommandText = "INSERT INTO test VALUES (@FirstName, @SurName, @City)";
             cmd.CommandType = CommandType.Text;

             cmd.Parameters.AddWithValue("@FirstName", textBox1.Text);
             cmd.Parameters.AddWithValue("@SurName", textBox2.Text);
             cmd.Parameters.AddWithValue("@City", textBox3.Text);

             conn.Open();

             // runs the query.  It will return the number of records modified(if needed)
             cmd.ExecuteNonQuery();
         }
     }
}  



Things that happened here:

1. Use of a parameterized query. This will help against the SQL Injection.
2. Since you aren't returning anything, no real need to use a DataAdapter, so using the SqlConnection and SqlCommand classes are easier. When you are only doing INSERT and UPDATE, you aren't returning anything. Therefore, using a DataAdapter is overkill.
3. The use of the "using" statement means that after the object has completed it's intended function, it will be disposed of.
4. Since I am using the "using" statement, the SqlConnection object should be local to the event instead of a class variable.
5. Changed the connectToDatabase method to return the connectionString.

This post has been edited by eclipsed4utoo: 05 April 2010 - 06:16 AM

Was This Post Helpful? 3
  • +
  • -

#4 SwiftStriker00   User is offline

  • No idea why my code works
  • member icon

Reputation: 439
  • View blog
  • Posts: 1,617
  • Joined: 25-December 08

Re: making code more efficient

Posted 05 April 2010 - 06:23 AM

Thanks for elaborating for me i had to run to a scrum
Was This Post Helpful? 0
  • +
  • -

#5 megglz   User is offline

  • D.I.C Regular
  • member icon

Reputation: 11
  • View blog
  • Posts: 414
  • Joined: 22-August 08

Re: making code more efficient

Posted 05 April 2010 - 06:48 AM

Thanks very much to both of you :)
Was This Post Helpful? 0
  • +
  • -

#6 Yakyb   User is offline

  • New D.I.C Head

Reputation: 3
  • View blog
  • Posts: 21
  • Joined: 04-June 08

Re: making code more efficient

Posted 06 April 2010 - 08:46 AM

personally i would have used LinqtoSQL


Scott Gu useful blog
Was This Post Helpful? 1
  • +
  • -

Page 1 of 1