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

            try
            {
                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.");
                Console.WriteLine(E.Message);
                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();
logoblog

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

Previous
« Prev Post

2 comments:

How to send email from current Outlook instance using Java + VBScript

If we are using .Net, we can easily access outlook and send emails using its own libraries. But what if we are using Java? Here we can seek ...