December 30, 2019

How to Read data from MsSQL database using C#

  December 30, 2019
In any automation framework, a DB validation is a very important factor. For getting data from DB , at first we need to make a connection and execute the query.
This will get all the result set into a data table.

If we are using Microsoft SQL we have two methods to retrieve data from DB. For both these methods, the connection string will be different.

1.SQL server Authentication

Connection String:

string connetionString =@"Data Source=; Initial Catalog=; User ID=; Password=";

2.Windows Authentication

Connection String:

string connetionString = @"Data Source=; Initial Catalog=;Integrated Security=True";

Now we need a method to make connections and to read data from DB. The below method will serve this purpose.

        public DataTable GetQueryResult(String vConnectionString, String vQuery)
            SqlConnection Connection;  // It is for SQL connection
            DataSet ds = new DataSet();  // it is for store query result

                Connection = new SqlConnection(vConnectionString);  // Declare SQL connection with connection string 
                Connection.Open();  // Connect to Database
                Console.WriteLine("Connection with database is done.");

                SqlDataAdapter adp = new SqlDataAdapter(vQuery, Connection);  // Execute query on database 
                adp.Fill(ds);  // Store query result into DataSet object   
                Connection.Close();  // Close connection 
                Connection.Dispose();   // Dispose connection             
            catch (Exception E)
                Console.WriteLine("Error in getting result of query.");
                return new DataTable();
            return ds.Tables[0];

Now we can get data from the data table below.

var dDataTableUsersd = GetQueryResult(connetionString, "Select SETID +','+EMPLID as dffs from DBname");
string userName = dDataTableUsersd.Rows[0]["Name"].ToString();

Thanks for reading How to Read data from MsSQL database using C#

« Prev Post


Bookmark this website for more workarounds and issue fixes.

Verify Zip file contents without extracting using C# + Selenium

While doing automation testing, we may get a scenario where we need to download a zip file and to validate the contents inside it. One way t...