public static object ExecSP(VietconDataContext pContext, string pSPName, List <SqlParameter> pParams)
{
object result = 0;
pContext = new VietconDataContext();
try
{
SqlConnection conn = new SqlConnection(pContext == null ? connectionStr : pContext.Connection.ConnectionString);
SqlCommand cmd = new SqlCommand(pSPName, conn);
cmd.CommandType = CommandType.StoredProcedure;
//add parameters
SqlParameter returnParam = new SqlParameter();
if (pParams != null)
{
foreach (SqlParameter param in pParams)
{
cmd.Parameters.Add(param);
if (param.Direction == ParameterDirection.ReturnValue)
{
returnParam = param;
}
}
}
try
{
if (cmd.Connection.State == ConnectionState.Open)
{
cmd.Connection.Close();
}
cmd.Connection.Open();
cmd.ExecuteNonQuery();
result = returnParam.Value == null ? -1000 : returnParam.Value;
}
catch (Exception ex)
{
throw ex;
}
finally
{
if (cmd.Connection.State == ConnectionState.Open)
{
cmd.Connection.Close();
}
cmd.Dispose();
}
}
catch (Exception ex)
{
throw ex;
}
return(result);
}
public static DataSet ExecDataSetSP(VietconDataContext pContext, string pSPName, List <SqlParameter> pParams)
{
pContext = new VietconDataContext();
DataSet ds = new DataSet();
try
{
SqlConnection conn = new SqlConnection(pContext == null ? connectionStr : pContext.Connection.ConnectionString);
SqlCommand cmd = new SqlCommand(pSPName, conn);
cmd.CommandType = CommandType.StoredProcedure;
SqlDataAdapter da = new SqlDataAdapter();
da.SelectCommand = cmd;
//add parameters
if (pParams != null)
{
foreach (SqlParameter param in pParams)
{
cmd.Parameters.Add(param);
}
}
try
{
if (cmd.Connection.State == ConnectionState.Open)
{
cmd.Connection.Close();
}
cmd.Connection.Open();
da.Fill(ds);
}
catch (Exception ex)
{
throw ex;
}
finally
{
if (cmd.Connection.State == ConnectionState.Open)
{
cmd.Connection.Close();
}
cmd.Dispose();
da.Dispose();
}
}
catch (Exception ex)
{
throw ex;
}
return(ds);
}
public static DataTable ExecDataTableQuery(VietconDataContext pContext, string pQueryString)
{
pContext = new VietconDataContext();
DataTable dt = new DataTable();
try
{
SqlConnection conn = new SqlConnection(pContext == null ? connectionStr : pContext.Connection.ConnectionString);
SqlCommand cmd = new SqlCommand(pQueryString, conn);
cmd.CommandType = CommandType.Text;
SqlDataAdapter da = new SqlDataAdapter();
da.SelectCommand = cmd;
try
{
if (cmd.Connection.State == ConnectionState.Open)
{
cmd.Connection.Close();
}
cmd.Connection.Open();
//da.FillSchema(dt, SchemaType.Mapped);
da.Fill(dt);
}
catch (Exception ex)
{
throw ex;
}
finally
{
if (cmd.Connection.State == ConnectionState.Open)
{
cmd.Connection.Close();
}
cmd.Dispose();
da.Dispose();
}
}
catch (Exception ex)
{
throw ex;
}
return(dt);
}
public static DataTable ExecDataTable(VietconDataContext pContext, object pLINQQuery)
{
DataTable dt = new DataTable();
try
{
if (pLINQQuery == null)
{
throw new ArgumentNullException("pLINQQuery");
}
IDbCommand cmd = pContext.GetCommand(pLINQQuery as IQueryable);
SqlDataAdapter da = new SqlDataAdapter();
da.SelectCommand = (SqlCommand)cmd;
try
{
cmd.Connection.Open();
da.FillSchema(dt, SchemaType.Source);
da.Fill(dt);
}
catch (Exception ex)
{
throw ex;
}
finally
{
if (cmd.Connection.State == ConnectionState.Open)
{
cmd.Connection.Close();
}
cmd.Dispose();
da.Dispose();
}
}
catch (Exception ex)
{
throw ex;
}
return(dt);
}
public static int ExecQuery(VietconDataContext pContext, string pQueryString)
{
int result = 0;
pContext = new VietconDataContext();
try
{
SqlConnection conn = new SqlConnection(pContext == null ? connectionStr : pContext.Connection.ConnectionString);
SqlCommand cmd = new SqlCommand(pQueryString, conn);
cmd.CommandType = CommandType.Text;
try
{
if (cmd.Connection.State == ConnectionState.Open)
{
cmd.Connection.Close();
}
cmd.Connection.Open();
result = cmd.ExecuteNonQuery();
}
catch (Exception ex)
{
throw ex;
}
finally
{
if (cmd.Connection.State == ConnectionState.Open)
{
cmd.Connection.Close();
}
cmd.Dispose();
}
}
catch (Exception ex)
{
throw ex;
}
return(result);
}
