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