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); }