CODEWITHSUNDEEP
c#sql-server
Diwakar Narasimman
Diwakar Narasimman

Reputation: 11

Need to connect database through app.config file in C#

I need to connect connect database through app.config file, stored procedure should get exceuted and the results should be stored in the datatable

App.config file:

<?xml version="1.0" encoding="utf-8" ?>
<configuration>
    <connectionStrings>
        <add name="Test" 
             connectionString="Data Source=servername; Initial Catalog=DBname; UserID=xx;password=xxx"    
             providerName="System.Data.SqlClient"/>
    </connectionStrings>
</configuration>

C#

SqlConnection DB = new SqlConnection(); 
DB.ConnectionString=ConfigurationManager.ConnectionStrings["Test"].ConnectionString;

SqlCommand cmd = new SqlCommand("[SP_active_user_profiles]", DB); 
cmd.CommandType = CommandType.StoredProcedure;  

DataTable dt = new DataTable();  

SqlDataAdapter adapter = new SqlDataAdapter(cmd); 

adapter.Fill(dt);

My problem is: I could n't connect to database itself, it throws null reference exception and the stored procedure results to be stored in the datatable.

where is my mistake?

Upvotes: 0

Views: 3372

Answers (2)

mojo
mojo

Reputation: 184

Connection string should be like this <add name="Test" connectionString="Data Source=servername;Initial Catalog=DBname; User ID=xx;Password=xxx;Integrated Security=SSPI; " providerName="System.Data.SqlClient"/>

Upvotes: 1

SᴇM
SᴇM

Reputation: 7213

use it like this:

private string connectionString = ConfigurationManager.ConnectionStrings["Test"].ConnectionString;

public void YourMethod()
{
   DataTable table = null;
   try
   {
       using (SqlConnection connection = new SqlConnection(this.connectionString))
       {
           connection.Open();
           SqlCommand cmd = connection.CreateCommand();

           cmd.CommandText = "SP_active_user_profiles";
           cmd.CommandType = CommandType.StoredProcedure;

           using (SqlDataAdapter adapter = new SqlDataAdapter(cmd))
           {
               table = new DataTable();
               adapter.Fill(table);
           }
       }

   }
   catch (Exception ex)
   {
       //Handle your exeption
   }
}

Upvotes: 1

Related Questions