How to write this function ExecutedateTable with block using ?

[engahmedbarbary]

I work on csharp 5.0 I need to custom this function as best practice by using block to destroy connection after finish

so How to use Using block as below

using(sql connection=new sql connection)

{

}

my function i need to customize as below :

public static DataTable ExecuteDataTablesp(string sql, SqlConnection dbConnection, DbParameter[] @params = null, CommandType cmdType = CommandType.StoredProcedure)
{


SqlCommand cmd = null;
SqlDataAdapter da = null;

if (dbConnection == null)
{
dbConnection = (SqlConnection)InitializeConnection();
}


switch (Provider)
{
case Providers.SQLServer:

cmd = new SqlCommand("", dbConnection as SqlConnection);
da = new SqlDataAdapter("", dbConnection as SqlConnection);

break;


default:
break;
}
if (sql == "") return new DataTable();
DataSet ds = new DataSet();
try
{
lock (synObj)
{

sql = AnalyizeBooleanFields(sql);
cmd.CommandText = sql;
cmd.CommandType = cmdType;
cmd.Parameters.Clear();

if (@params != null && @params.Length > 0)
{
cmd.Parameters.AddRange(@params);

}


if (dbConnection.State != ConnectionState.Open)
dbConnection.Open();


cmd.CommandTimeout = 0;
da.SelectCommand = cmd;
da.Fill(ds);




}

}
catch
{

}

return ds.Tables[0];
}

static IDbConnection InitializeConnection()
{
switch (_Provider)
{
case Providers.SQLServer:

return new SqlConnection(GlobalVariables.con);



}
return new SqlConnection();
}

Continue reading...