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

C# Calling a SQL connectionstring from a procedure

I have created a connectionstring and put it in a procedure :
public static void getConn()
        {
            SqlConnection conn = null;
            SqlDataReader rdr = null;

            conn = new SqlConnection("Server=xxx;DataBase=myDB;Integrated Security=SSPI");
            conn.Open();
            SqlCommand cmd = new SqlCommand("sp_Test", conn);
            cmd.CommandType = CommandType.StoredProcedure;
            rdr = cmd.ExecuteReader();
        }

I now want to call that connection to use in a new procedure.  I am not sure how to do this.
0
Jasmin01
Asked:
Jasmin01
1 Solution
 
Arthur_WoodCommented:
your connection objct is declared as being LOCAL to the procedure.  That cannot be referenced anywhere else in your code.

You need to declare the  connection as a Static variable within the module where you want to use it:

private static  SqlConnection conn ;

public static void getConn()
        {
            conn = null;
            SqlDataReader rdr = null;

            conn = new SqlConnection("Server=xxx;DataBase=myDB;Integrated Security=SSPI");
            conn.Open();
            SqlCommand cmd = new SqlCommand("sp_Test", conn);
            cmd.CommandType = CommandType.StoredProcedure;
            rdr = cmd.ExecuteReader();
        }

Then you can use it just like any other variable anywhere elese in the same class.
You just need to make sure that  GetConn is being called BEFORE you want to use the connection object in another priocedure.

Also, GetConn is probably not the best name for the procedure you have since you are creating the Connnection and using it to get data, which is NEVER going to be used, since GetConn returns a void, and rdr is not being used for anything.

It would be much better to code it like this (This is called a SINGLETON pattern):

private static  SqlConnection conn ;

public static void getConn()
        {
// this will treat conn as a SINGLETON objext - one and only one connection object will ever exist in your code, since it will only be created if it does not already exist
           if (conn == null){            
                  conn = new SqlConnection("Server=xxx;DataBase=myDB;Integrated Security=SSPI");
          }  
          // Open the connection only if it is not already open
          if (!conn.State == ConnectionState.Open)
          {
                  conn.Open();
           }
        }

AW
0
 
Jasmin01Author Commented:
Thanks,  I have written it using your idea.  But how do I call the stored procedure from another function now?
0
 
Naman GoelCommented:
Do something like this:

  public static void getConn()
        {
            SqlConnection conn = null;
            SqlDataReader rdr = null;

            conn = GetConnection();
            SqlCommand cmd = new SqlCommand("sp_Test", conn);
            cmd.CommandType = CommandType.StoredProcedure;
            rdr = cmd.ExecuteReader();
        }

        private static SqlConnection GetConnection()
        {
            SqlConnection conn = null;
            conn = new SqlConnection("Server=xxx;DataBase=myDB;Integrated Security=SSPI");
            conn.Open();
            return conn;
        }

Open in new window


You can use GetConnection method in other places an you can call any stored procedure using that given connction
0

Featured Post

New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

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