Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 253
  • Last Modified:

ADO.NET SqlConnection

Hello Experts,

I have a question about the following code below. I have a project that I'm creating and the project requires that I need to perform multile Create, Retrieve, Update, and Delete (CRUD) commands that I need to create. I seem to be using the following code below a lot. I was wondering if there was a way to create a class that has the following code below. The only think though that i would need to do if it was possible to create a class with the code below was how would I be able to add parameters and names of my stored procedures to this class since i have to create so many.

    protected void RetrieveDataName()
    {
        SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["TMF"].ConnectionString);

        SqlCommand cmd = new SqlCommand();
        cmd.CommandText = "NameOfStoredProcedure";
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.Connection = conn;

        try
        {
            conn.Open();

            //handle binding logic here
        }

        catch (Exception ex)
        {
            throw ex;
        }

        finally
        {
            conn.Close();
        }
    }

Open in new window

0
asp_net2
Asked:
asp_net2
  • 4
  • 3
1 Solution
 
Arthur_WoodCommented:
" how would I be able to add parameters and names of my stored procedures to this class since I have to create so many"

Those would be properties of your class.

The could be a Connection Class.

AW
0
 
asp_net2Author Commented:
Hi Aurthur_Wood,

Sorry, but I'm a little new to making a class and not sure what you can or cannot do with them. I usually just code ADO.NET in the CodeBehind and just now thinking it would be easier to manage if there was a way that I could just make an SqlConnection Class and just manage/update that class for all the Events.

Not sure what you mean by "Those would be properties of your class" and "The could be a Connection Class". Could you explain to me a little better since I'm still learning.
0
 
Arthur_WoodCommented:
Like this (air code)

public class MyConnection
      sqlConnection _conn;
      string _spName;
      sqlCommand _cmd;
      public sqlConnection connection
         {
              get { return _conn};
              set{_conn = value};
          }
       
     public string spName
       {
            get { return _spName;}
            set { _spName = value;}
       }
     
     public MyConnection(sqlConnection conn, string spName)
     {
        _conn  = conn;
        _spName = spName;
        _conn.Open();
        _cmd = new sqlCommand();
        _cmd.Connection = _conn;
        _cmd.CommandText = _spName;
}
       
       
AW
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!

 
asp_net2Author Commented:
Ok, I somewhat understand that. Now how would that be called with the ADO.NET code that I initially supplied above in the RetrieveDataName() Event?
0
 
Arthur_WoodCommented:
RetrieveDataName is not an Event.  It is a method that does not return a value (which makes it almost useless, in your example)


SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["TMF"].ConnectionString);

MyConnection myConn = new MyConnecton(conn, spName);
sqlDataReader rdr = myConn.cmd.ExecuteDataReader();
 and so on.
0
 
asp_net2Author Commented:
Ok, so it's basically useless to create an SqlConnection Class then if I still need to call the following below for every one of my 100+ pages??


SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["TMF"].ConnectionString);

MyConnection myConn = new MyConnecton(conn, spName);
sqlDataReader rdr = myConn.cmd.ExecuteDataReader();
and so on.
0
 
Arthur_WoodCommented:
basically, yes, since each page is essentially a separate 'stand-alone' mini-app.  Pages do not maintain 'state'.

AW
0
 
jonnidipCommented:
ages do not maintain 'state'
..but maybe that your SQL Server maintains a pool, so that different client connections from the same source are essentially served from the same server connection.
I would not worry about it...

Regards.
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

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.

  • 4
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now