Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

c# sql

Posted on 2007-03-17
12
Medium Priority
?
585 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
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 2000 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

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

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

We all know that functional code is the leg that any good program stands on when it comes right down to it, however, if your program lacks a good user interface your product may not have the appeal needed to keep your customers happy. This issue can…
The article shows the basic steps of integrating an HTML theme template into an ASP.NET MVC project
Video by: ITPro.TV
In this episode Don builds upon the troubleshooting techniques by demonstrating how to properly monitor a vSphere deployment to detect problems before they occur. He begins the show using tools found within the vSphere suite as ends the show demonst…
Please read the paragraph below before following the instructions in the video — there are important caveats in the paragraph that I did not mention in the video. If your PaperPort 12 or PaperPort 14 is failing to start, or crashing, or hanging, …
Suggested Courses

610 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