internal LocalCommand(string commandText, SqlParameterCollection parameters, int returnParameterIndex, CommandType cmdType) {
Debug.Assert(0 <= commandText.Length, "no text");
this.CommandText = commandText;
this.Parameters = parameters;
this.ReturnParameterIndex = returnParameterIndex;
this.CmdType = cmdType;
}
public DataSet GenericStatementReturnDataSet(string strStoredProcedure, SqlParameterCollection sqlparCollection)
{
DataSet ds = new DataSet();
try
{
OpenConnection();
sqlcmd = new SqlCommand(strStoredProcedure, sqlcon);
sqlcmd.CommandType = CommandType.StoredProcedure;
foreach (SqlParameter param in sqlparCollection)
{
sqlcmd.Parameters.AddWithValue(param.ParameterName, param.SqlValue);
}
sqlAdp = new SqlDataAdapter(sqlcmd);
sqlAdp.Fill(ds);
CloseConnection();
}
catch (Exception ex)
{
CloseConnection();
cErrorLog.LogError(ex);
}
return ds;
}
public override void PersistAddExtraParameters(SqlParameterCollection parameters, Employee employee)
{
var emp = employee as EmployeeFullTime;
if (emp != null)
{
parameters.AddWithValue("@AnnualSalary", emp.AnnualSalary);
}
}
internal static IEnumerable<SqlParameter> CopyParameters( SqlParameterCollection sqlParameterCollection )
{
foreach ( SqlParameter sqlParameter in sqlParameterCollection )
{
yield return new SqlParameter( sqlParameter.ParameterName, sqlParameter.SqlValue )
{
SqlValue = sqlParameter.SqlValue
//, Direction = sqlParameter.Direction
};
}
}
/// <summary>
/// Iterates through a SqlParameterCollection to output the names and values in one line.
/// </summary>
/// <param name="sqlParameterCollection"></param>
/// <returns></returns>
public static string DumpSqlParameters(SqlParameterCollection sqlParameterCollection)
{
var buffer = new StringBuilder();
foreach (SqlParameter p in sqlParameterCollection)
{
buffer.Append('{').Append("param_name=").Append(p.ParameterName).Append(',');
buffer.Append("param_value=").Append(p.Value).Append('}');
}
return buffer.ToString();
}
public override void PersistAddExtraParameters(SqlParameterCollection parameters, Employee employee)
{
var emp = employee as EmployeePartTime;
if (emp != null)
{
parameters.AddWithValue("@HourlyPay", emp.HourlyPay);
parameters.AddWithValue("@HoursWorked", emp.HoursWorked);
}
}
private static void AddSerializedProperty(ArraySegment<byte> source, SqlParameterCollection parameters, string parameterName)
{
int num = (source.Count > 0x1f40) ? source.Count : -1;
object obj2 = ((num == -1) ? ((bool) GenerateByteArray(source)) : ((bool) source.Array)) ?? DBNull.Value;
SqlParameter parameter = new SqlParameter {
ParameterName = parameterName,
SqlDbType = SqlDbType.VarBinary,
Size = num,
Value = obj2
};
parameters.Add(parameter);
}
public int ExecuteInsert(string sProc, SqlParameterCollection oArrParam)
{
int iRecordsAffected = 0;
if (CheckDBConnStatus() == true)
{
SqlTransaction oTrans;
oTrans = oConn.BeginTransaction();
try
{
SqlCommand oCmd = new SqlCommand(sProc, oConn, oTrans);
oCmd.CommandType = CommandType.StoredProcedure;
foreach (SqlParameter oParam in oArrParam)
{
oCmd.Parameters.Add(oParam.ParameterName, oParam.SqlDbType).Value = oParam.Value;
}
iRecordsAffected = oCmd.ExecuteNonQuery();
oTrans.Commit();
oTrans.Dispose();
oTrans = null;
oCmd.Dispose();
oCmd = null;
oConn.Close();
return iRecordsAffected;
}
catch (SqlException sqlerr)
{
oTrans.Rollback();
sErrMessage = "SQL Error: Number - " + sqlerr.Number + ", " + sqlerr.Message;
return iRecordsAffected;
}
catch (Exception err)
{
oTrans.Rollback();
sErrMessage = " Runtime Error: " + err.Message;
return iRecordsAffected;
}
}
else
{
return iRecordsAffected;
}
}
/// <summary>
/// cập nhật chứng từ
/// </summary>
/// <param name="iID_MaCapPhat"></param>
/// <param name="Params"></param>
/// <param name="sMaND"></param>
/// <param name="sIPSua"></param>
/// <returns></returns>
public static Boolean CapNhatBanGhi(String iID_MaCapPhat, SqlParameterCollection Params, String sMaND, String sIPSua)
{
Bang bang = new Bang("CP_CapPhat");
bang.MaNguoiDungSua = sMaND;
bang.IPSua = sIPSua;
bang.GiaTriKhoa = iID_MaCapPhat;
bang.DuLieuMoi = false;
for (int i = 0; i < Params.Count; i++)
{
bang.CmdParams.Parameters.AddWithValue(Params[i].ParameterName, Params[i].Value);
}
bang.Save();
return false;
}
private static void BuildQueryParameters (IDbCommand cmd,SqlParameterCollection parameterCollection){
if (parameterCollection != null && parameterCollection.Count > 0) {
foreach (SqlParameter par in parameterCollection) {
var cmdPar = cmd.CreateParameter();
cmdPar.ParameterName = par.ParameterName;
if (par.DataType != System.Data.DbType.Binary) {
cmdPar.DbType = par.DataType;
cmdPar.Value = par.ParameterValue;
} else {
cmdPar.DbType = System.Data.DbType.Binary;
}
cmdPar.Direction = par.ParameterDirection;
cmd.Parameters.Add(cmdPar);
}
}
}
public SqlDataReader ExecuteInsertWithIdentity(string sProc, SqlParameterCollection oArrParam)
{
SqlDataReader oRd = null;
if (CheckDBConnStatus() == true)
{
try
{
SqlCommand oCmd = new SqlCommand(sProc, oConn);
oCmd.CommandType = CommandType.StoredProcedure;
foreach (SqlParameter oParam in oArrParam)
{
oCmd.Parameters.Add(oParam.ParameterName, oParam.SqlDbType).Value = oParam.Value;
}
if (oConn.State == ConnectionState.Closed)
{
oConn.ConnectionString = this.FGetConnString();
oConn.Open();
}
oRd = oCmd.ExecuteReader();
return oRd;
}
catch (SqlException sqlerr)
{
sErrMessage = "SQL Error: Number - " + sqlerr.Number + ", " + sqlerr.Message;
return oRd;
}
catch (Exception err)
{
sErrMessage = " Runtime Error: " + err.Message;
return oRd;
}
}
else
{
return oRd;
}
}
public void ExecuteNonQuery(string sqlText, SqlParameterCollection spc)
{
try
{
if (objConn.State != ConnectionState.Open)
{
OpenConnection();
}
this.cm = new SqlCommand(sqlText, this.objConn);
foreach (SqlParameter sp in spc)
{
this.cm.Parameters.Add(sp);
}
cm.ExecuteNonQuery();
}
catch (Exception e)
{
throw new Exception("Error in ExecuteQuery!!! Source:" + e.Source.ToString() + " Message:" + e.Message.ToString());
}
}
internal DataTable ConvertOutputParametersToDataTable(System.Data.SqlClient.SqlParameterCollection parameters)
{
DataTable outputParameters = new DataTable("OutputParameters");
for (int iterator = 0; iterator <= parameters.Count - 1; iterator++)
{
if (parameters[iterator].Direction.Equals(ParameterDirection.Output))
{
outputParameters.Columns.Add(parameters[iterator].ParameterName.Replace("@", ""));
}
}
DataRow row = outputParameters.NewRow();
foreach (DataColumn column in outputParameters.Columns)
{
row[column.ColumnName] = parameters["@" + column.ColumnName].Value;
}
outputParameters.Rows.Add(row);
return(outputParameters);
}
static void SetParameter(IEdmFunction func, JObject parameterValues, IEdmType edmType, SqlParameterCollection pars)
{
if (parameterValues == null)
return;
JToken token = null;
Type colType = null;
foreach (var p in func.Parameters)
{
if (parameterValues.TryGetValue(p.Name, out token))
{
if (p.Type.Definition.TypeKind == EdmTypeKind.Complex)
{
DataTable dt = new DataTable();
var c = p.Type.AsComplex();
foreach (var item in c.StructuralProperties())
{
dt.Columns.Add(item.Name, item.Type.PrimitiveKind().ToClrType());
}
foreach (var item in token)
{
DataRow dr = dt.NewRow();
foreach (JProperty col in item)
{
if (!dt.Columns.Contains(col.Name))
continue;
colType = dt.Columns[col.Name].DataType;
if (colType == typeof(Boolean))
{
dr.SetField(col.Name, col.Value.ToString() == "0" ? false : true);
}
else
dr.SetField(col.Name, col.Value.ToString().ChangeType(colType));
}
dt.Rows.Add(dr);
}
pars.AddWithValue(p.Name, dt);
}
else
{
if (string.IsNullOrEmpty(token.ToString()))
pars.AddWithValue(p.Name, DBNull.Value);
else
pars.AddWithValue(p.Name, token.ToString().ChangeType(p.Type.PrimitiveKind()));
}
}
}
if (edmType.TypeKind == EdmTypeKind.Entity)
{
foreach (var outp in (edmType as EdmEntityType).Properties())
{
if (outp.Name == "$Results")
continue;
if (pars.Contains(outp.Name))
{
pars[outp.Name].Direction = ParameterDirection.Output;
}
else
{
pars.AddWithValue(outp.Name, DBNull.Value);
}
}
}
}
void view_Deleting (object sender, SqlDataSourceCommandEventArgs e)
{
SqlDataSourceTest.CustomEventParameterCollection = (SqlParameterCollection) e.Command.Parameters;
SqlDataSourceTest.PassedParameters = FormatParameters (SqlDataSourceTest.CustomEventParameterCollection);
e.Cancel = true;
}
public DataSet executeQuery(string db, string q, SqlParameterCollection p)
{
using (con = __initConnection(false)) {
con.Open();
if (!String.IsNullOrEmpty(db)) con.ChangeDatabase(db);
if (this.showPlan) {
using (com = new SqlCommand("SET SHOWPLAN_ALL ON", con)) {
com.ExecuteNonQuery();
}
}
using (com = new SqlCommand(q, con)) {
if (p != null) for (int i = 0, l = p.Count; i < l; i++) com.Parameters.AddWithValue(p[i].ParameterName, p[i].Value);
SqlDataReader r = com.ExecuteReader();
return __sqlDataReaderToDataSet(ref r, q.TrimEnd(new char[] {';'}).Split(new char[] {';'}).Length);
}
}
}
private SqlCommand(string commandText, SqlConnection connection, SqlTransaction transaction, CommandType commandType, UpdateRowSource updatedRowSource, bool designTimeVisible, int commandTimeout, SqlParameterCollection parameters)
{
this.commandText = commandText;
this.connection = connection;
this.transaction = transaction;
this.commandType = commandType;
this.updatedRowSource = updatedRowSource;
this.designTimeVisible = designTimeVisible;
this.commandTimeout = commandTimeout;
this.parameters = new SqlParameterCollection(this);
for (int i = 0; i < parameters.Count; i++)
{
this.parameters.Add(((ICloneable)parameters[i]).Clone());
}
}
public XmlReader ExecuteSPXmlReader(string procedureName)
{
XmlReader reader;
SqlCommand cmd = new SqlCommand();
this.Connect();
cmd.CommandTimeout = this.CommandTimeout;
cmd.CommandText = procedureName;
cmd.Connection = _connection;
if (_transaction != null) cmd.Transaction = _transaction;
cmd.CommandType = CommandType.StoredProcedure;
this.CopyParameters(cmd);
reader = cmd.ExecuteXmlReader();
_parameterCollection = cmd.Parameters;
cmd.Dispose();
return reader;
}
public void ExecuteSPDataSet(ref DataSet dataSet, string procedureName, string tableName)
{
SqlCommand cmd = new SqlCommand();
this.Connect();
SqlDataAdapter da = new SqlDataAdapter();
cmd.CommandTimeout = this.CommandTimeout;
cmd.CommandText = procedureName;
cmd.Connection = _connection;
if (_transaction != null) cmd.Transaction = _transaction;
cmd.CommandType = CommandType.StoredProcedure;
this.CopyParameters(cmd);
da.SelectCommand = cmd;
da.Fill(dataSet, tableName);
_parameterCollection = cmd.Parameters;
da.Dispose();
cmd.Dispose();
if (this.AutoCloseConnection) this.Disconnect();
}
private SqlCommand(string commandText, SqlConnection connection, SqlTransaction transaction, CommandType commandType, UpdateRowSource updatedRowSource, bool designTimeVisible, int commandTimeout, SqlParameterCollection parameters)
{
this.commandText = commandText;
this.connection = connection;
this.transaction = transaction;
this.commandType = commandType;
this.updatedRowSource = updatedRowSource;
this.designTimeVisible = designTimeVisible;
this.commandTimeout = commandTimeout;
this.parameters = new SqlParameterCollection(this);
for (int i = 0;i < parameters.Count;i++)
this.parameters.Add(((ICloneable)parameters[i]).Clone());
}
public static void ThemThongTinChiTietTaiChinhDoanhNghiepLayTruongTien(DataRow RMucLucChiTieu, DataTable dtDauKy, SqlParameterCollection Params)
{
//<--Thêm tham số từ bảng MucLucNganSach
String strDSTruong = "sKyHieu,sTen,sThuyetMinh";
String sXauNoiMa = "";
String[] arrDSTruong = strDSTruong.Split(',');
if (Params.IndexOf("@iID_MaChiTieu") >= 0)
{
Params["@iID_MaChiTieu"].Value = RMucLucChiTieu["iID_MaChiTieu"];
Params["@iID_MaChiTieu_Cha"].Value = RMucLucChiTieu["iID_MaChiTieu_Cha"];
Params["@bLaHangCha"].Value = RMucLucChiTieu["bLaHangCha"];
}
else
{
Params.AddWithValue("@iID_MaChiTieu", RMucLucChiTieu["iID_MaChiTieu"]);
Params.AddWithValue("@iID_MaChiTieu_Cha", RMucLucChiTieu["iID_MaChiTieu_Cha"]);
Params.AddWithValue("@bLaHangCha", RMucLucChiTieu["bLaHangCha"]);
}
String iID_MaChiTieu = Convert.ToString(RMucLucChiTieu["iID_MaChiTieu"]);
String iID_MaChiTieu1 = "";
for (int i = 0; i < dtDauKy.Rows.Count; i++)
{
iID_MaChiTieu1 = Convert.ToString(dtDauKy.Rows[i]["iID_MaChiTieu"]);
if (iID_MaChiTieu.Equals(iID_MaChiTieu1))
{
if (Params.IndexOf("@rSoDauNam") >= 0)
{
Params["@rSoDauNam"].Value = dtDauKy.Rows[i]["rSoCuoiNam"];
}
else
{
Params.AddWithValue("@rSoDauNam", dtDauKy.Rows[i]["rSoCuoiNam"]);
}
dtDauKy.Rows.RemoveAt(i);
break;
}
}
for (int i = 0; i < arrDSTruong.Length; i++)
{
if (Params.IndexOf("@" + arrDSTruong[i]) >= 0)
{
Params["@" + arrDSTruong[i]].Value = RMucLucChiTieu[arrDSTruong[i]];
}
else
{
Params.AddWithValue("@" + arrDSTruong[i], RMucLucChiTieu[arrDSTruong[i]]);
}
if (i < arrDSTruong.Length - 1 && String.IsNullOrEmpty(Convert.ToString(RMucLucChiTieu[arrDSTruong[i]])) == false)
{
if (sXauNoiMa != "") sXauNoiMa += "-";
sXauNoiMa += Convert.ToString(RMucLucChiTieu[arrDSTruong[i]]);
}
}
if (Params.IndexOf("@sXauNoiMa") >= 0)
{
Params["@sXauNoiMa"].Value = sXauNoiMa;
}
else
{
Params.AddWithValue("@sXauNoiMa", sXauNoiMa);
}
}
internal static string sqlParamsToQueryString(System.Data.SqlClient.SqlParameterCollection sqlParams)
{
return(string.Join("&", (from System.Data.SqlClient.SqlParameter param in sqlParams select String.Concat(param.ParameterName, "=", param.SqlValue)).ToArray()));
}
internal void DeriveParameters()
{
if (commandType != CommandType.StoredProcedure)
{
throw new InvalidOperationException(String.Format("SqlCommand DeriveParameters only supports CommandType.StoredProcedure, not CommandType.{0}", commandType));
}
ValidateCommand("DeriveParameters", false);
string procName = CommandText;
string schemaName = String.Empty;
int dotPosition = procName.LastIndexOf('.');
// Procedure name can be: [database].[user].[procname]
if (dotPosition >= 0)
{
schemaName = procName.Substring(0, dotPosition);
procName = procName.Substring(dotPosition + 1);
if ((dotPosition = schemaName.LastIndexOf('.')) >= 0)
{
schemaName = schemaName.Substring(dotPosition + 1);
}
}
procName = EscapeProcName(procName, false);
schemaName = EscapeProcName(schemaName, true);
SqlParameterCollection localParameters = new SqlParameterCollection(this);
localParameters.Add("@procedure_name", SqlDbType.NVarChar, procName.Length).Value = procName;
if (schemaName.Length > 0)
{
localParameters.Add("@procedure_schema", SqlDbType.NVarChar, schemaName.Length).Value = schemaName;
}
string sql = "sp_procedure_params_rowset";
try {
Connection.Tds.ExecProc(sql, localParameters.MetaParameters, 0, true);
} catch (TdsTimeoutException ex) {
Connection.Tds.Reset();
throw SqlException.FromTdsInternalException((TdsInternalException)ex);
} catch (TdsInternalException ex) {
Connection.Close();
throw SqlException.FromTdsInternalException((TdsInternalException)ex);
}
SqlDataReader reader = new SqlDataReader(this);
parameters.Clear();
object[] dbValues = new object[reader.FieldCount];
while (reader.Read())
{
reader.GetValues(dbValues);
parameters.Add(new SqlParameter(dbValues));
}
reader.Close();
if (parameters.Count == 0)
{
throw new InvalidOperationException("Stored procedure '" + procName + "' does not exist.");
}
}
internal void Append(SqlCommand command)
{
SqlParameterCollection parameters;
int num7;
ADP.CheckArgumentNull(command, "command");
Bid.Trace("<sc.SqlCommandSet.Append|API> %d#, command=%d, parameterCount=%d\n", this.ObjectID, command.ObjectID, command.Parameters.Count);
string commandText = command.CommandText;
if (ADP.IsEmpty(commandText))
{
throw ADP.CommandTextRequired("Append");
}
CommandType commandType = command.CommandType;
switch (commandType)
{
case CommandType.Text:
case CommandType.StoredProcedure:
{
parameters = null;
SqlParameterCollection parameters2 = command.Parameters;
if (0 < parameters2.Count)
{
parameters = new SqlParameterCollection();
for (int i = 0; i < parameters2.Count; i++)
{
SqlParameter destination = new SqlParameter();
parameters2[i].CopyTo(destination);
parameters.Add(destination);
if (!SqlIdentifierParser.IsMatch(destination.ParameterName))
{
throw ADP.BadParameterName(destination.ParameterName);
}
}
foreach (SqlParameter parameter in parameters)
{
object obj2 = parameter.Value;
byte[] src = obj2 as byte[];
if (src != null)
{
int offset = parameter.Offset;
int size = parameter.Size;
int num5 = src.Length - offset;
if ((size != 0) && (size < num5))
{
num5 = size;
}
byte[] dst = new byte[Math.Max(num5, 0)];
Buffer.BlockCopy(src, offset, dst, 0, dst.Length);
parameter.Offset = 0;
parameter.Value = dst;
}
else
{
char[] chArray2 = obj2 as char[];
if (chArray2 != null)
{
int srcOffset = parameter.Offset;
int num4 = parameter.Size;
int num3 = chArray2.Length - srcOffset;
if ((num4 != 0) && (num4 < num3))
{
num3 = num4;
}
char[] chArray = new char[Math.Max(num3, 0)];
Buffer.BlockCopy(chArray2, srcOffset, chArray, 0, chArray.Length * 2);
parameter.Offset = 0;
parameter.Value = chArray;
}
else
{
ICloneable cloneable = obj2 as ICloneable;
if (cloneable != null)
{
parameter.Value = cloneable.Clone();
}
}
}
}
}
num7 = -1;
if (parameters != null)
{
for (int j = 0; j < parameters.Count; j++)
{
if (ParameterDirection.ReturnValue == parameters[j].Direction)
{
num7 = j;
break;
}
}
}
break;
}
case CommandType.TableDirect:
throw SQL.NotSupportedCommandType(commandType);
default:
throw ADP.InvalidCommandType(commandType);
}
LocalCommand item = new LocalCommand(commandText, parameters, num7, command.CommandType);
this.CommandList.Add(item);
}
private static string FormatParameters (SqlParameterCollection sqlParameterCollection)
{
StringBuilder sb = new StringBuilder ();
foreach (SqlParameter p in sqlParameterCollection) {
if (sb.Length > 0) {
sb.Append (", ");
}
sb.AppendFormat ("{0}:{1}={2}", p.DbType, p.ParameterName, p.Value);
}
return sb.ToString ();
}
internal static void sqlParamsToQueryString(System.Data.SqlClient.SqlParameterCollection sqlParams, out string querySting)
{
querySting = string.Join("&", (from System.Data.SqlClient.SqlParameter param in sqlParams select String.Concat(param.ParameterName, "=", param.SqlValue)).ToArray());
}
internal void Append(SqlCommand command) {
ADP.CheckArgumentNull(command, "command");
Bid.Trace("<sc.SqlCommandSet.Append|API> %d#, command=%d, parameterCount=%d\n", ObjectID, command.ObjectID, command.Parameters.Count);
string cmdText = command.CommandText;
if (ADP.IsEmpty(cmdText)) {
throw ADP.CommandTextRequired(ADP.Append);
}
CommandType commandType = command.CommandType;
switch(commandType) {
case CommandType.Text:
case CommandType.StoredProcedure:
break;
case CommandType.TableDirect:
Debug.Assert(false, "command.CommandType");
throw System.Data.SqlClient.SQL.NotSupportedCommandType(commandType);
default:
Debug.Assert(false, "command.CommandType");
throw ADP.InvalidCommandType(commandType);
}
SqlParameterCollection parameters = null;
SqlParameterCollection collection = command.Parameters;
if (0 < collection.Count) {
parameters = new SqlParameterCollection();
// clone parameters so they aren't destroyed
for(int i = 0; i < collection.Count; ++i) {
SqlParameter p = new SqlParameter();
collection[i].CopyTo(p);
parameters.Add(p);
// SQL Injection awarene
if (!SqlIdentifierParser.IsMatch(p.ParameterName)) {
throw ADP.BadParameterName(p.ParameterName);
}
}
foreach(SqlParameter p in parameters) {
// deep clone the parameter value if byte[] or char[]
object obj = p.Value;
byte[] byteValues = (obj as byte[]);
if (null != byteValues) {
int offset = p.Offset;
int size = p.Size;
int countOfBytes = byteValues.Length - offset;
if ((0 != size) && (size < countOfBytes)) {
countOfBytes = size;
}
byte[] copy = new byte[Math.Max(countOfBytes, 0)];
Buffer.BlockCopy(byteValues, offset, copy, 0, copy.Length);
p.Offset = 0;
p.Value = copy;
}
else {
char[] charValues = (obj as char[]);
if (null != charValues) {
int offset = p.Offset;
int size = p.Size;
int countOfChars = charValues.Length - offset;
if ((0 != size) && (size < countOfChars)) {
countOfChars = size;
}
char[] copy = new char[Math.Max(countOfChars, 0)];
Buffer.BlockCopy(charValues, offset, copy, 0, copy.Length*2);
p.Offset = 0;
p.Value = copy;
}
else {
ICloneable cloneable = (obj as ICloneable);
if (null != cloneable) {
p.Value = cloneable.Clone();
}
}
}
}
}
int returnParameterIndex = -1;
if (null != parameters) {
for(int i = 0; i < parameters.Count; ++i) {
if (ParameterDirection.ReturnValue == parameters[i].Direction) {
returnParameterIndex = i;
break;
}
}
}
LocalCommand cmd = new LocalCommand(cmdText, parameters, returnParameterIndex, command.CommandType);
CommandList.Add(cmd);
}
/// <summary>
/// Thêm một hàng dữ liệu vào bảng TN_ChungTu
/// </summary>
/// <param name="iID_MaDotPhanBo"></param>
/// <param name="Params"></param>
/// <param name="MaND"></param>
/// <param name="IPSua"></param>
/// <returns></returns>
public static String InsertRecord(String iID_MaDotPhanBo, SqlParameterCollection Params, String MaND, String IPSua)
{
String MaChungTu = "";
Bang bang = new Bang("TN_ChungTu");
DataTable dtDotPhanBo = PhanBo_DotPhanBoModels.GetDotPhanBo(iID_MaDotPhanBo);
bang.MaNguoiDungSua = MaND;
bang.IPSua = IPSua;
for (int i = 0; i < Params.Count; i++)
{
bang.CmdParams.Parameters.AddWithValue(Params[i].ParameterName, Params[i].Value);
}
bang.CmdParams.Parameters.AddWithValue("@iNamLamViec", dtDotPhanBo.Rows[0]["iNamLamViec"]);
bang.CmdParams.Parameters.AddWithValue("@iID_MaNguonNganSach", dtDotPhanBo.Rows[0]["iID_MaNguonNganSach"]);
bang.CmdParams.Parameters.AddWithValue("@iID_MaNamNganSach", dtDotPhanBo.Rows[0]["iID_MaNamNganSach"]);
bang.CmdParams.Parameters.AddWithValue("@iID_MaTrangThaiDuyet", LuongCongViecModel.Get_iID_MaTrangThaiDuyetMoi(QuyetToanModels.iID_MaPhanHeQuyetToan));
bang.DuLieuMoi = true;
String MaChungTuAddNew = Convert.ToString(bang.Save());
//Thêm chi tiết chỉ tiêu
//PhanBo_ChungTuChiTietModels.ThemChiTiet(MaChungTuAddNew, MaND, IPSua);
return MaChungTu;
}
public void ExecuteSP(string procedureName)
{
SqlCommand cmd = new SqlCommand();
this.Connect();
cmd.CommandTimeout = this.CommandTimeout;
cmd.CommandText = procedureName;
cmd.Connection = _connection;
if (_transaction != null) cmd.Transaction = _transaction;
cmd.CommandType = CommandType.StoredProcedure;
this.CopyParameters(cmd);
cmd.ExecuteNonQuery();
_parameterCollection = cmd.Parameters;
cmd.Dispose();
if (this.AutoCloseConnection) this.Disconnect();
}
/// <summary>
/// Cập nhập dữ liệu 1 Record của Chỉ tiêu
/// </summary>
/// <param name="iID_MaChungTu"></param>
/// <param name="Params">Params là của cmd.Parameters</param>
/// <param name="MaND"></param>
/// <param name="IPSua"></param>
/// <returns></returns>
public static Boolean UpdateRecord(String iID_MaChungTu, SqlParameterCollection Params, String MaND, String IPSua)
{
Bang bang = new Bang("TN_ChungTu");
bang.MaNguoiDungSua = MaND;
bang.IPSua = IPSua;
bang.GiaTriKhoa = iID_MaChungTu;
bang.DuLieuMoi = false;
for (int i = 0; i < Params.Count; i++)
{
bang.CmdParams.Parameters.AddWithValue(Params[i].ParameterName, Params[i].Value);
}
bang.Save();
return false;
}
public SqlDataReader ExecuteSPReader(string procedureName)
{
SqlDataReader reader;
SqlCommand cmd = new SqlCommand();
this.Connect();
cmd.CommandTimeout = this.CommandTimeout;
cmd.CommandText = procedureName;
cmd.Connection = _connection;
if (_transaction != null) cmd.Transaction = _transaction;
cmd.CommandType = CommandType.StoredProcedure;
this.CopyParameters(cmd);
CommandBehavior behavior = CommandBehavior.Default;
if (this.AutoCloseConnection) behavior = behavior | CommandBehavior.CloseConnection;
if (_isSingleRow) behavior = behavior | CommandBehavior.SingleRow;
reader = cmd.ExecuteReader(behavior);
_parameterCollection = cmd.Parameters;
cmd.Dispose();
return reader;
}
public DataSet executeQuery(string q, SqlParameterCollection p)
{
return executeQuery("master", q, p);
}
public void Reset()
{
if (_parameters != null)
{
_parameters.Clear();
}
if (_parameterCollection != null)
{
_parameterCollection = null;
}
}
internal SqlParameterCollectionImplementation(SqlParameterCollection parameterCollection) => _parameterCollection = parameterCollection;
public SqlCommand (string cmdText, SqlConnection connection, SqlTransaction transaction)
{
this.commandText = cmdText;
this.connection = connection;
this.transaction = transaction;
this.commandType = CommandType.Text;
this.updatedRowSource = UpdateRowSource.Both;
this.commandTimeout = DEFAULT_COMMAND_TIMEOUT;
#if NET_2_0
notificationAutoEnlist = true;
#endif
designTimeVisible = true;
parameters = new SqlParameterCollection (this);
}
public static DataTable CallQueryProcedure(string ProcudereName, SqlParameterCollection Parms)
{
DataTable rows = new DataTable();
try
{
string ConnectionString = connection();
SqlConnection Conn = new SqlConnection(ConnectionString);
SqlCommand cmd = new SqlCommand(ProcudereName, Conn);
foreach (SqlParameter parmrow in Parms)
{
cmd.Parameters.Add(parmrow.ParameterName, parmrow.Value);
}
cmd.CommandType = System.Data.CommandType.StoredProcedure;
Conn.Open();
SqlDataReader rdr = cmd.ExecuteReader();
rows.Load(rdr);
//cmd.CommandText =ProcudereName;
return rows;
}
catch (Exception ex)
{
return rows;
}
}
internal void DeriveParameters ()
{
if (commandType != CommandType.StoredProcedure)
throw new InvalidOperationException (String.Format ("SqlCommand DeriveParameters only supports CommandType.StoredProcedure, not CommandType.{0}", commandType));
ValidateCommand ("DeriveParameters", false);
string procName = CommandText;
string schemaName = String.Empty;
int dotPosition = procName.LastIndexOf ('.');
// Procedure name can be: [database].[user].[procname]
if (dotPosition >= 0) {
schemaName = procName.Substring (0, dotPosition);
procName = procName.Substring (dotPosition + 1);
if ((dotPosition = schemaName.LastIndexOf ('.')) >= 0)
schemaName = schemaName.Substring (dotPosition + 1);
}
procName = EscapeProcName (procName, false);
schemaName = EscapeProcName (schemaName, true);
SqlParameterCollection localParameters = new SqlParameterCollection (this);
localParameters.Add ("@procedure_name", SqlDbType.NVarChar, procName.Length).Value = procName;
if (schemaName.Length > 0)
localParameters.Add ("@procedure_schema", SqlDbType.NVarChar, schemaName.Length).Value = schemaName;
string sql = "sp_procedure_params_rowset";
try {
Connection.Tds.ExecProc (sql, localParameters.MetaParameters, 0, true);
} catch (TdsTimeoutException ex) {
Connection.Tds.Reset ();
throw SqlException.FromTdsInternalException ((TdsInternalException) ex);
} catch (TdsInternalException ex) {
Connection.Close ();
throw SqlException.FromTdsInternalException ((TdsInternalException) ex);
}
SqlDataReader reader = new SqlDataReader (this);
parameters.Clear ();
object[] dbValues = new object[reader.FieldCount];
while (reader.Read ()) {
reader.GetValues (dbValues);
parameters.Add (new SqlParameter (dbValues));
}
reader.Close ();
if (parameters.Count == 0)
throw new InvalidOperationException ("Stored procedure '" + procName + "' does not exist.");
}
internal void Append(SqlCommand command)
{
ADP.CheckArgumentNull(command, "command");
Bid.Trace("<sc.SqlCommandSet.Append|API> %d#, command=%d, parameterCount=%d\n", ObjectID, command.ObjectID, command.Parameters.Count);
string cmdText = command.CommandText;
if (ADP.IsEmpty(cmdText))
{
throw ADP.CommandTextRequired(ADP.Append);
}
CommandType commandType = command.CommandType;
switch (commandType)
{
case CommandType.Text:
case CommandType.StoredProcedure:
break;
case CommandType.TableDirect:
Debug.Assert(false, "command.CommandType");
throw System.Data.SqlClient.SQL.NotSupportedCommandType(commandType);
default:
Debug.Assert(false, "command.CommandType");
throw ADP.InvalidCommandType(commandType);
}
SqlParameterCollection parameters = null;
SqlParameterCollection collection = command.Parameters;
if (0 < collection.Count)
{
parameters = new SqlParameterCollection();
// clone parameters so they aren't destroyed
for (int i = 0; i < collection.Count; ++i)
{
SqlParameter p = new SqlParameter();
collection[i].CopyTo(p);
parameters.Add(p);
// SQL Injection awarene
if (!SqlIdentifierParser.IsMatch(p.ParameterName))
{
throw ADP.BadParameterName(p.ParameterName);
}
}
foreach (SqlParameter p in parameters)
{
// deep clone the parameter value if byte[] or char[]
object obj = p.Value;
byte[] byteValues = (obj as byte[]);
if (null != byteValues)
{
int offset = p.Offset;
int size = p.Size;
int countOfBytes = byteValues.Length - offset;
if ((0 != size) && (size < countOfBytes))
{
countOfBytes = size;
}
byte[] copy = new byte[Math.Max(countOfBytes, 0)];
Buffer.BlockCopy(byteValues, offset, copy, 0, copy.Length);
p.Offset = 0;
p.Value = copy;
}
else
{
char[] charValues = (obj as char[]);
if (null != charValues)
{
int offset = p.Offset;
int size = p.Size;
int countOfChars = charValues.Length - offset;
if ((0 != size) && (size < countOfChars))
{
countOfChars = size;
}
char[] copy = new char[Math.Max(countOfChars, 0)];
Buffer.BlockCopy(charValues, offset, copy, 0, copy.Length * 2);
p.Offset = 0;
p.Value = copy;
}
else
{
ICloneable cloneable = (obj as ICloneable);
if (null != cloneable)
{
p.Value = cloneable.Clone();
}
}
}
}
}
int returnParameterIndex = -1;
if (null != parameters)
{
for (int i = 0; i < parameters.Count; ++i)
{
if (ParameterDirection.ReturnValue == parameters[i].Direction)
{
returnParameterIndex = i;
break;
}
}
}
LocalCommand cmd = new LocalCommand(cmdText, parameters, returnParameterIndex, command.CommandType, command.ColumnEncryptionSetting);
CommandList.Add(cmd);
}