C# SqlClient.SqlParameterCollection类代码示例

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

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

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

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

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

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