How to get connection strings to various databases in ASP.NET 2.0


How to use connection strings to various Databases in ASP.NET 2.0

Some of the values and strings are used across all the pages of a website. It is a tedious process to code those values and strings in each page of the website.

The connection string will be used in many pages of the web site if you are developing a database driven website. Let us say that you have hard coded the connection string in all the pages, around 30 pages. Now if the value of the connection string changes, you have a headache of changing all the values of the connection string in all the 30 pages. This can’t be done easily and if you are to change the string again after about one month, it is going to be a tedious task.

Hence in such scenarios it is a practice to store those values in a central repository from where you can retrieve it and use it in all the pages. For example if you store the connection string in a central place like a web.config file you can retrieve those connection strings from the web.config file and use it. Previous to ASP.Net 2.0 the connection strings were stored in web.config file as given below:

<configuration>
<appSettings>
<add key=”connectionString” value=”connection_string_value_here” />
</appSettings>

<system.web>

</system.web>
</configuration>

The connection string is stored as key and value pair in the <appSettings> element of the web.config file. This value can be retrieved by writing code like:

ConfigurationSettings.AppSettings(“connectionString”)

Instead of using the <appSettings> section you can also add your own sections in the web.config file to have all your string value that you will be using in your application.

ASP.Net 2.0 provides with a special class for retrieving those customized string values.

This class is called the ConfigurationManager class.

Using this class you will be retrieving values by writing the following code for the above config file.

ConfigurationManager.AppSettings[“connectionString”]

To open the ConnectionStrings section of the web.config file, you can use code like

ConfigurationManager.ConnectionStrings[“connectionString”].ConnectionString

The above code is used to retrieve ConnectionStrings from the web.config file as given below:

<connectionStrings>
<add name=”connectionString” connectionString=”Data Source=.;Initial Catalog=db_products;Integrated Security=True” providerName=”System.Data.SqlClient” />
</connectionStrings>

This code comes under the <configuration> section of the web.config file.

Here you can use the connectionStrings tag to store multiple Connection string to multiple databases and use it at your might.

Like:

<connectionStrings>
<add name=”connectionString” connectionString=”Connect_String_Value” providerName=”Connection_Provider” />

<add name=”myCString” connectionString=”Connect_String_Value” providerName=”Connection_Provider” />

<add name=”newCString” connectionString=”Connect_String_Value” providerName=”Connection_Provider” />
</connectionStrings>

You can use it in you code like :

1. ConfigurationManager.ConnectionStrings[“connectionString”].ConnectionString

2. ConfigurationManager.ConnectionStrings[“myCString”].ConnectionString

3. ConfigurationManager.ConnectionStrings[“newCString”].ConnectionString


Happy Connecting to database 🙂

A Utility Method to Run stored procedures and Get results in a Data Table in C# ASP.NET


Hi,

I have just came into a problem where I need to execute many different stored procedures which may output in different ways. Instead of writing seperate codes for each of the stored procedures I build a utility method which will take the stored procedure name and the parameter list as input and it will return a Datatable objec populated as result.

Now we will go through the code:

//This procedure takes Stored procedure name and a Arraylist of parameters as argument.
//The array list is a colection of SqlParameter type of onjects
//I will explain it later

public DataTable GetData(string StoreProcName,ArrayList parameters)
{
SqlConnection _sqlConn = null;
DataTable _dt = new DataTable();
SqlDataAdapter _sqDa = null;

try
{

if (null == _connection)  //I have stored the connection string in this variable
{
throw new Exception(“Connection Null”);
}

//Open a new connection
_sqlConn = new SqlConnection(_connection);

_sqlConn.Open();

SqlCommand cmd = new SqlCommand();
cmd.Connection = _sqlConn;
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = StoreProcName; //Specify the stored procedure name

//If parameter list is non null the fill the parameter data for this
//Stored procedure.

if (null != parameters)
{
foreach (Object obj in parameters)
{
cmd.Parameters.Add((SqlParameter)obj);
}
}

//Open a new SQL Data Adapter type of object to populate the data table

_sqDa = new SqlDataAdapter(cmd);
_sqDa.Fill(_dt); //Fill the data table

_sqlConn.Close(); //Close the connection

return _dt;
}
catch (Exception ex)
{
return null;
}
finally
{
if (ConnectionState.Open == _sqlConn.State)
{
_sqlConn.Close();
}
}
}

Now I will let you know how to call the stored procedure.

ArrayList _parameterList = new ArrayList(); //Declare a Array List

SqlParameter parameter1 = new SqlParameter(); //Declare a SQL Parameter object

parameter1.ParameterName = “@fisrtinput”; // Give the parameter name
parameter1.SqlDbType = SqlDbType.VarChar;     // Give the data type
parameter1.Value = “First Value”; //Give the value
_parameterList.Add(parameter1); // Add it to the array list

SqlParameter parameter2 = new SqlParameter(); // Declare a second variable
parameter2.ParameterName = “@secondinput”; //Populate as before
parameter2.SqlDbType = SqlDbType.VarChar;
parameter2.Value = “Second_Value”;
_parameterList.Add(parameter2); //Add it to the Array list

Now the Array list has been populated. We will just call the Method with the Array List.

DataTable _dt = GetData(“PROCEDURE_NAME”,  _parameterList);

Now the data table is populated. You can work with that as you like.

Happy Coding 🙂