CODEWITHSUNDEEP
c#classsqlconnection
Xtreme
Xtreme

Reputation: 21

How do I put my SQL connection in a seperate class? C#

I am using this to connect to my SQL-database.

namespace WindowsFormsApplication1
{
    public partial class InvoiceGUI : Form
    {
        System.Data.SqlClient.SqlConnection con;
        System.Data.SqlClient.SqlDataAdapter da;
        DataSet ds1;
        DataRow dRow;

        int MaxRows = 0;
        int inc = 0;

        public InvoiceGUI()
        {
            InitializeComponent();
        }

        private void Form1_Load(object sender, EventArgs e)
        {
           con = new System.Data.SqlClient.SqlConnection();
           ds1 = new DataSet();

           con.ConnectionString = "Data Source=(local);Initial Catalog=invoice_db;Integrated Security=SSPI";

            con.Open();
            //MessageBox.Show("open");

            string sql = "select * from invoice";
            da = new System.Data.SqlClient.SqlDataAdapter(sql, con);

            da.Fill(ds1, "Invoice");
            NavigateInvoice();

            //Set the max rows
            MaxRows = ds1.Tables["Invoice"].Rows.Count;

            con.Close();
            //MessageBox.Show("closed");

        }

How do I move the connection to a separate class and how do I access my database from another class (Windows form)?
I guess it's best to have the the connection in one class and reach it from ex WindowsForm2

Please give me an example

Upvotes: 2

Views: 25282

Answers (2)

George Dgebuadze
George Dgebuadze

Reputation: 111

Try this:
1. Create class, for example ConnectionClass.cs
2. Put the following code into it: 

class clsConnectionClass:IDisposable
{
    public SqlConnection cnn;

    public clsConnectionClass()
    {
        if ((cnn = _cnn()) == null)
        {
            this.Dispose();
        }
    }

    private SqlConnection _cnn()
    {
        SqlConnection conn = null;
        string server =  "Your server address or name"             
        string db = "Your dadabase name";

        cnnString = string.Format("Server={0};Database={1};Trusted_Connection=SSPI;",server,db);
        try
        {
            conn = new SqlConnection();
            conn.ConnectionString = cnnString;
            conn.Open();
            return conn;
        }
        catch
        {
            conn.Dispose();
            return null;
        }
    }
    public void Dispose()
    {
        if (cnn != null)
        {
            cnn.Dispose();
        }
    }
}


3. In your Program class create static object of this class and initialize it in Main function.

  static class Program
{
    public static SystemParams.clsConnectionClass conn;
    [STAThread]
    static void Main()
    {
            conn = new SystemParams.clsConnectionClass();
            Application.EnableVisualStyles();
            Application.SetCompatibleTextRenderingDefault(false);
            Application.Run(new Shared.frmMain());           
    }
}


Now you can use this connection from any place of your program like Program.conn.cnn. It's kind an old answer, but still someone can use it.

Upvotes: 2

Krishna Prasad
Krishna Prasad

Reputation: 35

You should always move the data access related code to seperate classes/dll like data access layer.

Instead of writing your own data access code you can use readily available data access components like Enterprise Libray data access blocks / ORM frameworks which provide easy interfaces and configurations to manipulate various databases.

Refer the link for Enterprise Library info http://entlib.codeplex.com/

Using the enterprise library, all that you write is the following code in your data access layer and return entity objects (instead of datasets) back to the calling layer or UI.

 string sSql = "select * from customers"; 
    Database dbNorthwind = DatabaseFactory.CreateDatabase(); 
    DBCommandWrapper cmdCust = dbNorthwind.GetSqlStringCommandWrapper(sSql); 
    DataSet dsCust = dbNorthwind.ExecuteDataSet(cmdCust); 
    dataGrid1.DataSource = dsCust.Tables[0].DefaultView;

The connection string will be maintained in the configuration file so you will have the flexibility to change it.

Hope this helps. Krishna

Upvotes: 0

Related Questions