none
Is there a simpler way of a SQL Connection ? RRS feed

  • Question

  • using

     

     

    System;

    using

     

     

    System.Collections.Generic;

    using

     

     

    System.Linq;

    using

     

     

    System.Text;

    using

     

     

    System.Data.SqlClient;

    namespace

     

     

    ConsoleApplication2

    {

     

     

    class Program

    {

     

     

    static void Main(string[] args)

    {

     

     

    SqlConnection dataConnection = new SqlConnection();

     

     

    try

    {

    dataConnection.ConnectionString =

     

    "Integrated Security=true;Initial Catalog=Northwind;Data Source=atak-pc\\SQLExpress";

    dataConnection.Open();

     

     

    Console.Write("enter a customer id");

     

     

    string customerId = Console.ReadLine();

     

     

    SqlCommand dataCommand = new SqlCommand();

    dataCommand.Connection = dataConnection;

    dataCommand.CommandText =

     

    "SELECT OrderID, OrderDate, ShippedDate, ShipName, ShipAddress, ShipCity, ShipCountry FROM Orders WHERE CustomerID='" + customerId + "'";

     

     

    Console.WriteLine("About to execute:{0}\n\n", dataCommand.CommandText);

     

     

    SqlDataReader dataReader = dataCommand.ExecuteReader();

     

     

     

    while (dataReader.Read())

    {

     

     

    int orderId = dataReader.GetInt32(0);

     

     

    DateTime orderDate = dataReader.GetDateTime(1);

     

     

    DateTime shipDate = dataReader.GetDateTime(2);

     

     

    string shipName = dataReader.GetString(3);

     

     

    string shipAddress = dataReader.GetString(4);

     

     

    string shipCity = dataReader.GetString(5);

     

     

    string shipCountry = dataReader.GetString(6);

     

     

    Console.WriteLine("Order: {0}\nPlaced:{1}\nShipped:{2}\n" +

     

     

    "To Address: {3}\n{4}\n{5}\n{6}\n\n", orderId, orderDate, shipDate, shipName, shipAddress, shipCity, shipCountry);

    }

    dataReader.Close();

     

     

    Console.ReadKey();

    }

     

     

    catch (SqlException e)

    {

     

     

    Console.WriteLine("error", e.Message);

    }

     

     

    finally

    {

    dataConnection.Close();

    }

    }

    }

    }

    Wednesday, December 30, 2009 5:57 AM

Answers

  • Hi.

    You can shorten these lines:
    SqlCommand dataCommand = new SqlCommand();
    dataCommand.Connection = dataConnection;
    dataCommand.CommandText = "SELECT OrderID, OrderDate, ShippedDate, ShipName, ShipAddress, ShipCity, ShipCountry FROM Orders WHERE CustomerID='" + customerId + "'";

    to just one line:
    SqlCommand dataCommand = 
    new SqlCommand("SELECT OrderID, OrderDate, ShippedDate, ShipName, ShipAddress, ShipCity, ShipCountry FROM Orders WHERE CustomerID='" + customerId + "'", dataConnection);
    Noam B
    _________________________________________________________

    Do not Forget to Vote as Answer/Helpful, please. It encourages us to help you...

    • Marked as answer by LAPLACE777 Wednesday, December 30, 2009 9:09 AM
    Wednesday, December 30, 2009 8:33 AM

All replies

  • Not that I've seen. Although your code is a little different than mine I believe that all of those pieces are necessary in establishing the connection and retrieving the data.
    • Proposed as answer by Rohan W Wednesday, December 30, 2009 8:26 AM
    Wednesday, December 30, 2009 6:12 AM
  • Yes, I agree with Zero_gt,
    The stesp which you have jolted down are the bare minimum to connect to SQL and get some data from it.

    To summerise :
    Steps are -
    - Create SQLConnection object
    - Supply a connection string to it
    - Create a SQLCommand object and specify the SQL command and the connection it will use.
    - Open the connection
    - Execute the command

    Wednesday, December 30, 2009 8:32 AM
  • Hi.

    You can shorten these lines:
    SqlCommand dataCommand = new SqlCommand();
    dataCommand.Connection = dataConnection;
    dataCommand.CommandText = "SELECT OrderID, OrderDate, ShippedDate, ShipName, ShipAddress, ShipCity, ShipCountry FROM Orders WHERE CustomerID='" + customerId + "'";

    to just one line:
    SqlCommand dataCommand = 
    new SqlCommand("SELECT OrderID, OrderDate, ShippedDate, ShipName, ShipAddress, ShipCity, ShipCountry FROM Orders WHERE CustomerID='" + customerId + "'", dataConnection);
    Noam B
    _________________________________________________________

    Do not Forget to Vote as Answer/Helpful, please. It encourages us to help you...

    • Marked as answer by LAPLACE777 Wednesday, December 30, 2009 9:09 AM
    Wednesday, December 30, 2009 8:33 AM
  • For security reasons I suggest to use SqlCommand.Parameters.AddWithValue() function rather than passing value directly to your query. If you are making a web application you might end up a open hackable code.
    Sumit Gupta http://www.vikasumit.com http://www.sumitgupta.net
    Wednesday, December 30, 2009 10:45 AM