how to setup connectionstring and use in console app

Question

connectionstring configured in app.config how to use in main program console app I'm using .net 6.0 app.config program.cs string connect = ConfigurationManager.ConnectionStrings["Server"].ConnectionString; connect.Open(); SqlCommand command = new SqlCommand(); DataSet dataSet = new DataSet(); command.CommandText = "SpData"; command.CommandType = CommandType.StoredProcedure; SqlDataAdapter Adapter = new SqlDataAdapter(command.CommandText, connect); Adapter.Fill(dataSet, "SpData"); DataTable t1 = dataSet.Tables["SpData"]; foreach (DataColumn column in t1.Columns) { value += column.ColumnName + '^'; } value += "
"; if (t1 != null & t1.Rows.Count > 0) { foreach (DataRow row in t1.Rows) { foreach (DataColumn column in t1.Columns) { string cellValue = row[column.ColumnName].ToString() + '^'; value += cellValue; } value += "
"; }

Tuhin Paul · Answer

You can check this improved version: using System; using System.Configuration; using System.Data; using System.Data.SqlClient; class Program { static void Main(string[] args) { try { // Get the connection string from app.config string connectionString = ConfigurationManager.ConnectionStrings["Server"].ConnectionString; // Create a SqlConnection object using the connection string using (SqlConnection connection = new SqlConnection(connectionString)) { // Open the connection connection.Open(); // Create a SqlCommand object and set its properties using (SqlCommand command = new SqlCommand()) { command.Connection = connection; command.CommandText = "SpData"; command.CommandType = CommandType.StoredProcedure; // Set the parameters if any // command.Parameters.AddWithValue("@param1", value1); // Create a SqlDataAdapter object and use it to fill a DataSet using (SqlDataAdapter adapter = new SqlDataAdapter(command)) { DataSet dataSet = new DataSet(); adapter.Fill(dataSet); // Loop through the results and print them to the console foreach (DataRow row in dataSet.Tables[0].Rows) { foreach (DataColumn column in dataSet.Tables[0].Columns) { Console.Write(row[column].ToString() + "	"); } Console.WriteLine(); } } } } } catch (Exception ex) { Console.WriteLine("Error: " + ex.Message); } // Wait for user input before closing the console window Console.ReadLine(); } } By default, SqlConnection objects are pooled by the .NET Framework. This means that after the SqlConnection is closed, it is not immediately terminated but instead returned to the connection pool for reuse. You can set the connection string property "Pooling" to false if you want to disable connection pooling. Alos you can use the using statement for the SqlCommand and SqlDataAdapter objects to ensure that they are properly disposed of when they are no longer needed.

Naimish Makwana · Answer

You can use like below: using System; using System.Configuration; using System.Data; using System.Data.SqlClient; class Program { static void Main(string[] args) { // Get the connection string from app.config string connectionString = ConfigurationManager.ConnectionStrings["Server"].ConnectionString; // Create a SqlConnection object using the connection string using (SqlConnection connection = new SqlConnection(connectionString)) { // Open the connection connection.Open(); // Create a SqlCommand object and set its properties SqlCommand command = new SqlCommand(); command.Connection = connection; command.CommandText = "SpData"; command.CommandType = CommandType.StoredProcedure; // Create a SqlDataAdapter object and use it to fill a DataSet SqlDataAdapter adapter = new SqlDataAdapter(command); DataSet dataSet = new DataSet(); adapter.Fill(dataSet); // Loop through the results and print them to the console foreach (DataRow row in dataSet.Tables[0].Rows) { foreach (DataColumn column in dataSet.Tables[0].Columns) { Console.Write(row[column].ToString() + "	"); } Console.WriteLine(); } } // Wait for user input before closing the console window Console.ReadLine(); } } Thanks

Tuhin Paul · Answer

Based on the accepted answer, I am tring to add some improvements for example 1. Use a try-catch block to handle exceptions that may occur while executing the code. 2. Add parameterized queries instead of using a plain text command, which can prevent SQL injection attacks.

Jignesh Kumar · Answer

Hello Meghana, It looks correct to me. Are you facing any issue for above code ? string connect = ConfigurationManager.ConnectionStrings["Server"].ConnectionString; command.CommandText = "SpData"; command.CommandType = CommandType.StoredProcedure; SqlDataAdapter Adapter = new SqlDataAdapter(command.CommandText, connect); Adapter.Fill(dataSet, "SpData"); DataTable t1 = dataSet.Tables["SpData"]; SqlCommand cmd = new SqlCommand(); SqlDataAdapter da = new SqlDataAdapter(); DataTable dt = new DataTable(); try { cmd = new SqlCommand("SpData", connect.Open())); cmd.CommandType = CommandType.StoredProcedure; da.SelectCommand = cmd; da.Fill(dt); foreach (DataRow row in dt.Rows) { //Accessing using string Key Name // Console.WriteLine(row["Column1"] + ", " + row["comumname2"] ); //Accessing using integer index position Console.WriteLine(row[0] + ", " + row[1] + ", " + row[2]); } } catch (Exception ex) { throw ex.Message; } finally { cmd.Dispose(); connect.Close(); }