- Posted by Admin on February 7, 2009
In the routine programming of database driven application, we may frequently need to access the a some rows/records of a particular table(s). Hence, it is better to develop one public function that retrieves data table whenever needed.
Here is the basic code that fetches the data table according to the SQL supplied.
public class ClassDataTable
{
string
ConnectionString = "Your connection string here";
public DataTable GetDataTable(string
Query)
{
SqlConnection
SqlConn = new SqlConnection(ConnectionString);
SqlCommand
SqlCmd = new SqlCommand(Query,
SqlConn);
SqlDataAdapter
SqlDataAdt = new SqlDataAdapter();
DataSet
ResultSet = new DataSet();
DataTable
Result = new DataTable();
try
{
SqlCmd.CommandType = CommandType.Text;
SqlCmd.CommandTimeout = 30;
SqlDataAdt.SelectCommand =
SqlCmd;
if
(SqlConn.State == ConnectionState.Open)
{
SqlDataAdt.Fill(ResultSet, "Result");
Result = ResultSet.Tables["Result"];
}
else
if (SqlConn.State == ConnectionState.Closed)
{
SqlConn.Open();
SqlDataAdt.Fill(ResultSet, "Result");
Result = ResultSet.Tables["Result"];
}
else
{
SqlConn.Close();
SqlConn.Open();
SqlDataAdt.Fill(ResultSet, "Result");
Result = ResultSet.Tables["Result"];
}
return
Result;
}
catch
(Exception ex)
{
Result.Dispose();
ResultSet.Dispose();
SqlDataAdt.Dispose();
SqlCmd.Dispose();
SqlConn.Close();
return
null;
}
finally
{
Result.Dispose();
ResultSet.Dispose();
SqlDataAdt.Dispose();
SqlCmd.Dispose();
SqlConn.Close();
}
}
}
the Above function will fetch the datatable according to the SQL supplied.