Solved

c# sql

Posted on 2007-03-17
12
533 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:xDJR1875
Comment Utility
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
Comment Utility
even if I close the connection?

What are you recommending?

My first way?
0
 
LVL 8

Author Comment

by:bman9111
Comment Utility
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
 
LVL 8

Author Comment

by:bman9111
Comment Utility
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
Comment Utility
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
Comment Utility
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
What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

 
LVL 29

Expert Comment

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

Author Comment

by:bman9111
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

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…
Sending a Secure fax is easy with eFax Corporate (http://www.enterprise.efax.com). First, Just open a new email message.  In the To field, type your recipient's fax number @efaxsend.com. You can even send a secure international fax — just include t…
Here's a very brief overview of the methods PRTG Network Monitor (https://www.paessler.com/prtg) offers for monitoring bandwidth, to help you decide which methods you´d like to investigate in more detail.  The methods are covered in more detail in o…

762 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

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now