Solved

c# sql

Posted on 2007-03-17
12
564 Views
Last Modified: 2008-01-09
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
Comment
Question by:bman9111
  • 6
  • 3
  • 2
  • +1
12 Comments
 
LVL 17

Expert Comment

by:Daniel Reynolds
ID: 18742551
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
 
LVL 8

Author Comment

by:bman9111
ID: 18742607
even if I close the connection?

What are you recommending?

My first way?
0
 
LVL 8

Author Comment

by:bman9111
ID: 18742624
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
Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
LVL 8

Author Comment

by:bman9111
ID: 18742655
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
 
LVL 29

Expert Comment

by:Gautham Janardhan
ID: 18742665
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
 
LVL 8

Author Comment

by:bman9111
ID: 18742725
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
 
LVL 29

Expert Comment

by:Gautham Janardhan
ID: 18742729
s..u dont need to keep the command in the global class
0
 
LVL 8

Author Comment

by:bman9111
ID: 18744316
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
 
LVL 1

Accepted Solution

by:
nespa earned 500 total points
ID: 18745048
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
 
LVL 1

Expert Comment

by:nespa
ID: 18745057
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
 
LVL 8

Author Comment

by:bman9111
ID: 18745210
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
 
LVL 1

Expert Comment

by:nespa
ID: 18745824
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

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In order to hide the "ugly" records selectors (triangles) in the rowheaders, here are some suggestions. Microsoft doesn't have a direct method/property to do it. You can only hide the rowheader column. First solution, the easy way The first sol…
This article introduced a TextBox that supports transparent background.   Introduction TextBox is the most widely used control component in GUI design. Most GUI controls do not support transparent background and more or less do not have the…
Two types of users will appreciate AOMEI Backupper Pro: 1 - Those with PCIe drives (and haven't found cloning software that works on them). 2 - Those who want a fast clone of their boot drive (no re-boots needed) and it can clone your drive wh…

809 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question