• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 590
  • Last Modified:

c# sql

I am writing a database program with many forms. Is it better to declare the Sqlclient variables in 1 class that all forms will reference or do I want to keep doing this everytime I call a form:

Way I use to do it for each individual form.
SqlConnection cnn = Connection();
SqlCommand cmd = new SqlCommand("SELECT * from table1",cnn);
cnn.Open();

Right now I am doing this (Not sure if this is better or worse) Seems to be better to no redecclare a variable for the amount of forms that use the same variable. So this is what I have

Class1
public static SqlConnection cnn = new SqlConnection();
public static SqlCommand cmd = new SqlCommand();

then in all my forms i am doing this
Class1.cmd.connection = Class1.cnn;
Class1.cmd.commandtext = "Select * from table1";
Class1.cmd.ExecuteNonQuery();

Is this procedure ok or is it better the first way I suggested?
0
bman9111
Asked:
bman9111
  • 6
  • 3
  • 2
  • +1
1 Solution
 
Daniel ReynoldsSoftware Applications Developer / IntegratorCommented:
Just be aware that if you keep several forms open at one time, you could have several of your sql classes going at once eating memory/etc.
0
 
bman9111Author Commented:
even if I close the connection?

What are you recommending?

My first way?
0
 
bman9111Author Commented:
if the first way is proper for each form should I do this:
everytime in the form I need to call the database do this
SqlCommand cmd = new SqlCommand("SELECT * from table1",cnn);

or

beginning of form do this
SqlCommand cmd = new SqlCommand();

then in the form this
cmd.Commandtext  = "SELECT * from table1";



0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
bman9111Author Commented:
I think this way is the best preferred so that I am not calling to the database everytime a new form is opened.

Class1
public static SqlConnection cnn = new SqlConnection();
public static SqlCommand cmd = new SqlCommand();

then in all my forms i am doing this
Class1.cmd.connection = Class1.cnn;
Class1.cmd.commandtext = "Select * from table1";
Class1.cmd.ExecuteNonQuery();
0
 
Gautham JanardhanCommented:
i would suggest creating multiple instance of coneection i would go with the second method of creatig a gloabal class which would cater all my connection need like

public class DBConnection
{
  sqlconnection FCon;
  private static DBConnection FHandle;
   private void DBConnection()
   {
       //private contstrucotr so that no one can create objects
       FCon = new SqlConnection();
       FCon.Open();
    }

    public static DBConnection   Handle
    {
                 get
                  {
                          if(FHandle == null)
                                  FHandle = new DBConnection();
                          return FHandle;
                   }
    }
}

u can crete commands in the respective forms and assign this connection to it or else u coud write a method here that would return a command with the connection assigned

u would Access this object like

DBConnection.Handle // first time this is  called object is createf and then on when called the already created object is returned
0
 
bman9111Author Commented:
so basically this method that I was first using

Class1
public static SqlConnection cnn = new SqlConnection();
public static SqlCommand cmd = new SqlCommand();

then in all my forms i am doing this
Class1.cmd.connection = Class1.cnn;
Class1.cmd.commandtext = "Select * from table1";
Class1.cmd.ExecuteNonQuery();
0
 
Gautham JanardhanCommented:
s..u dont need to keep the command in the global class
0
 
bman9111Author Commented:
1. does it hurt if I do or is it better not too for the reader and command?

meaning do this in every form.

Either A:
SqlConnection cnn = Connection();
SqlCommand cmd = new SqlCommand("SELECT * from table1",cnn);

or B:
declare at the top of the form like
SqlCommand cmd = new SqlCommand();

then every time I want to call it do this:

 cmd.commandtext ="SELECT * from table1";

or doesnt it matter.



0
 
nespaCommented:
I would recommend:

C) put the database logic (connection string, database access) in one class & call that function from each of your forms.   I wouldn't keep a connection open (unless you're opening 100's of forms at once or some crazy thing like that) it's OK for each form to keep requesting a connection every time because those connections are pooled; this should ensure fast access, without keeping connections open.  

Each form that accesses this class *will* need some reference to a database type, but you don't have to include at the top of the class if you don't want to:
instead of:
  System.Data;
  System.Data.SqlClient;
  DataTable = DataServices.GetDataTable("Select * from MyTable");

use:
   System.Data.DataTable = DataServices.GetDataTable("Select * from MyTable");

Also it would be good to look into creating Stored Procedures (instead of using queries) which pull the data back - they perform better / faster since they cache the request/results.  And you're not keeping a bunch of TSQL in forms.  Then you can do this:

   System.Data.DataTable = DataServices.GetDataTable(spGetMyTableInfo);

Finally, a sample DataServices class which does what we're talking about:



using System;
using System.Collections.Generic;
using System.Text;

using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;

namespace Test_DbAccess
{
      class _START_HERE
      {
            public static void Main(string[] args)
            {
                  try
                  {
                        //use the DataServices class to get some results
                        DataTable dt = DataServices.GetDataTable("select * from MyTable");

                        //write the results to the console
                        foreach(DataRow dr in dt.Rows){
                              foreach(DataColumn dc in dt.Columns){
                                    Console.WriteLine(dr[dc].ToString());
                              }
                        }
                  }
                  catch (Exception ex)
                  {
                        Console.WriteLine(ex.Message + " " + ex.StackTrace);
                  }

            }
      }

      class DataServices
      {
            public static DataTable GetDataTable(string query)
            {
                  try
                  {
                        //you can keep the connection string here or in an app.config or web.config file
                        string connStr = "Data Source=.\\SQLEXPRESS; Integrated Security=False;initial catalog=myDatabase;user id=myUserName;password=myPassword;";

                        //create a new dataAdapter & dataTable
                        SqlDataAdapter da = new SqlDataAdapter(query, connStr);
                        DataTable dt = new DataTable();

                        //use the dataAdapter to run the query & fill a dataTable w/ results
                        da.Fill(dt);

                        //if it's a valid dataTable pass it back...otherwise throw an error
                        if (dt == null)
                        {
                              throw new System.Exception("Unable to get data.");
                        }
                        else
                        {
                              return dt;
                        }
                  }
                  catch(Exception ex)
                  {
                        Console.WriteLine(ex.Message + " " + ex.StackTrace);
                        throw; //include the stack trace & all helpful info
                  }
            }
      }
}
0
 
nespaCommented:
By the following I simply meant fully-qualify it using System.Data.DataTable, if you don't want Sql types at the top of your class:

instead of this
(top of class)
  System.Data;
  System.Data.SqlClient;
(in class)
  DataTable = DataServices.GetDataTable("Select * from MyTable");

use this fully-qualified reference:
   System.Data.DataTable = DataServices.GetDataTable("Select * from MyTable");
0
 
bman9111Author Commented:
could this be answered?
because I am already storing alot of the data in a dataset.
1. does it hurt if I do or is it better not too for the reader and command?

meaning do this in every form.

Either A:
SqlConnection cnn = Connection();
SqlCommand cmd = new SqlCommand("SELECT * from table1",cnn);

or B:
declare at the top of the form like
SqlCommand cmd = new SqlCommand();

then every time I want to call it do this:

 cmd.commandtext ="SELECT * from table1";
0
 
nespaCommented:
I wouldn't store any data objects in the form.  

The idea is that you want to abstract the process of getting data from the forms.

That way you could switch out your database logic at some point.    You can still keep the queries that need to be run w/ the forms:

(in form1)
(myCommandText = "select * from table1");

and then pass that to the database class to actually get the data.  

but it's bad design to have too many database-specific objects in each form, if only for the sake of that nice layer of abstraction (which again, would allow you to keep all database access and database logic in your database class).

by the way - two other important issues:

1) you should look into the concept of Data Access Layers (DALs) - some good reading on this & it's the best way to do things.   It's very similar to creating the data access class, except that it's a step further - you can even auto-generate some classes so that working with the data structures is exactly like working with a class; create a data access object & tell it to insert a row, delete a row, etc. like this:

myDataAccessObject dao = new myDataAccessObject();  
dao.Insert( ...a row of data here);
... where dao is an object that was generated by adding a new DataSet in (which creates an XSD file relating to your data table in the database) see .NET 2.0 for this.
dao.Insert("

2) you can bind DataTables to UI objects, such as a DataList (in .net 2.0)...or dataGrid (.net 1.4x)   Updates to the table can be sent back to the database easily (.Update method) and this keeps everything easily updated / inserted in the database.

hope that helps!
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 6
  • 3
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now