Method that returns Datatable object passing sql and connecting string in C#

Generic methods to return Datatable from sql query.

Generic method to return data from database:

With this method we fill a DataTable object with the result of a sql query, and additional parameter is passed to specify the database where we want extract data.

public DataTable GetDataTable(string cnString, string sql)

{

using (SqlConnection cn = new SqlConnection(cnString))

    {

     cn.Open();

 

        using (SqlDataAdapter da = new SqlDataAdapter(sql, cn))

        {

         da.SelectCommand.CommandTimeout = 120;

            DataSet ds = new DataSet();

            da.Fill(ds);

            return ds.Tables[0];

        }

    }

}

Usage:

string sql = "SELECT * FROM DBO.MY_TEST_DATA";

//MSSQL CONNECTION STRING

string myCnString = "Data Source=MYIPSERVER;Initial Catalog=my_database;

Persist Security Info=True;User ID=sa;Password=myPwd";

DataTable dt = GetDataTable(myCnString, sql);

//Todo: Use the table data

Advantages:

The method allow fast data extraction to application workspace, and then you can use DataTable methods as Compute and Select to make additional data processing.

Limitations:

I don't recommend this method if you need to devolve processed data to database, in this case prefer to use TableAdapters objects.