C# SqlClient.SqlParameter类代码示例

    protected string generate_forum_name(string group_id)
    {
        string conn = System.Configuration.ConfigurationManager.ConnectionStrings["connstring"].ConnectionString;

        SqlConnection connection = new SqlConnection(conn);

        string sqlqry = "SELECT group_name FROM forum_group WHERE [email protected]";

        SqlCommand command = new SqlCommand(sqlqry, connection);

        SqlParameter param1 = new SqlParameter();
        param1.SqlDbType = System.Data.SqlDbType.Int;
        param1.ParameterName = "@p1";
        param1.Value = group_id;
        command.Parameters.Add(param1);

        SqlDataReader Reader = null;

        command.Connection.Open();
        Reader = command.ExecuteReader();
        Reader.Read();
        string name = Reader[0].ToString();
        command.Connection.Close();

        return name;
    }

        public ResultModel CheckContractSubBusinessInvoiceApplyConfirm(UserModel user, int subId)
        {
            ResultModel result = new ResultModel();

            string cmdText = "select COUNT(*) from dbo.Inv_BusinessInvoice bi inner join dbo.Invoice inv on inv.InvoiceId = bi.InvoiceId where bi.SubContractId = 1 and inv.InvoiceStatus in (@entryStatus,@readyStatus)";

            SqlParameter[] paras = new SqlParameter[3];
            paras[0] = new SqlParameter("@subId", subId);
            paras[1] = new SqlParameter("@entryStatus", (int)NFMT.Common.StatusEnum.已录入);
            paras[1] = new SqlParameter("@readyStatus", (int)NFMT.Common.StatusEnum.已生效);

            object obj = DBUtility.SqlHelper.ExecuteScalar(this.ConnectString, CommandType.Text, cmdText, paras);
            int i = 0;
            if (obj == null || !int.TryParse(obj.ToString(), out i) || i <= 0)
            {
                result.ResultStatus = -1;
                result.Message = "检验业务发票失败";
                return result;
            }
            if (i > 0)
            {
                result.ResultStatus = -1;
                result.Message = "子合约中含有未完成的业务发票，不能进行确认完成操作。";
                return result;
            }

            result.ResultStatus = 0;
            result.Message = "业务发票全部完成";

            return result;
        }

        public void PersistUpdateOf(IAggregateRoot entity)
        {
            Account account = entity as Account;

            string sql = "UPDATE Account SET [email protected] WHERE [email protected]";
            SqlParameter parameter1 = new SqlParameter("@Id", account.Id);
            SqlParameter parameter2= new SqlParameter("@Balance", account.Balance);

            using (SqlConnection conn = new SqlConnection(_connStr))
            {
                conn.Open();
                using (SqlCommand cmd = conn.CreateCommand())
                {
                    cmd.CommandText = sql;
                    cmd.Parameters.Add(parameter1);
                    cmd.Parameters.Add(parameter2);

                    int affectedCount = cmd.ExecuteNonQuery();

                    if (affectedCount <= 0) 
                    {
                        throw new Exception("update Account failed");
                    }
                }
            }
        }

 /// <summary>
 /// Get InvoiceNo Corresponding to Ledger For PurchaseReturn Register
 /// </summary>
 /// <param name="decLedgerId"></param>
 /// <returns></returns>
 public List<DataTable> GetInvoiceNoCorrespondingtoLedgerForPurchaseReturnReport(decimal decLedgerId, decimal decVoucherId)
 {
     List<DataTable> ListObj = new List<DataTable>();
     DataTable dtbl = new DataTable();
     try
     {
         if (sqlcon.State == ConnectionState.Closed)
         {
             sqlcon.Open();
         }
         SqlDataAdapter sqldataadapter = new SqlDataAdapter("GetInvoiceNoCorrespondingtoLedgerForPurchaseReturnReport", sqlcon);
         sqldataadapter.SelectCommand.CommandType = CommandType.StoredProcedure;
         SqlParameter sqlparameter = new SqlParameter();
         sqlparameter = sqldataadapter.SelectCommand.Parameters.Add("@ledgerId", SqlDbType.Decimal);
         sqlparameter.Value = decLedgerId;
         sqlparameter = sqldataadapter.SelectCommand.Parameters.Add("@voucherTypeId", SqlDbType.Decimal);
         sqlparameter.Value = decVoucherId;
         sqldataadapter.Fill(dtbl);
         ListObj.Add(dtbl);
     }
     catch (Exception ex)
     {
         MessageBox.Show(ex.Message, "OpenMiracle", MessageBoxButtons.OK, MessageBoxIcon.Information);
     }
     finally
     {
         sqlcon.Close();
     }
     return ListObj;
 }

        public override List<SqlParameter> CreateInsertParameters(IModel obj, ref SqlParameter returnValue)
        {
            FinBusInvAllotDetail inv_finbusinvallotdetail = (FinBusInvAllotDetail)obj;

            List<SqlParameter> paras = new List<SqlParameter>();
            returnValue.Direction = ParameterDirection.Output;
            returnValue.SqlDbType = SqlDbType.Int;
            returnValue.ParameterName = "@DetailId";
            returnValue.Size = 4;
            paras.Add(returnValue);

            SqlParameter allotidpara = new SqlParameter("@AllotId", SqlDbType.Int, 4);
            allotidpara.Value = inv_finbusinvallotdetail.AllotId;
            paras.Add(allotidpara);

            SqlParameter businessinvoiceidpara = new SqlParameter("@BusinessInvoiceId", SqlDbType.Int, 4);
            businessinvoiceidpara.Value = inv_finbusinvallotdetail.BusinessInvoiceId;
            paras.Add(businessinvoiceidpara);

            SqlParameter financeinvoiceidpara = new SqlParameter("@FinanceInvoiceId", SqlDbType.Int, 4);
            financeinvoiceidpara.Value = inv_finbusinvallotdetail.FinanceInvoiceId;
            paras.Add(financeinvoiceidpara);

            SqlParameter allotbalapara = new SqlParameter("@AllotBala", SqlDbType.Decimal, 9);
            allotbalapara.Value = inv_finbusinvallotdetail.AllotBala;
            paras.Add(allotbalapara);

            SqlParameter detailstatuspara = new SqlParameter("@DetailStatus", SqlDbType.Int, 4);
            detailstatuspara.Value = (int)Common.StatusEnum.已生效;
            paras.Add(detailstatuspara);

            return paras;
        }

 /// <summary>
 /// Add Parameters for calling stored procedures
 /// </summary>
 /// <param name="paramName">Name of Parameter</param>
 /// <param name="value">Value of Parameter</param>
 public void AddParameter(string paramName, object value)
 {
     SqlParameter param = new SqlParameter();
     param.ParameterName = paramName;
     param.Value = value;
     cmd.Parameters.Add(param);
 }

        public static bool AddCart(orderinfo item)
        {
            SqlParameter[] parms = new SqlParameter[7];
            parms[0] = new SqlParameter("@id", SqlDbType.VarChar, 20);
            parms[0].Direction = ParameterDirection.Output;
            parms[1] = new SqlParameter("@productid", SqlDbType.NVarChar,50);
            parms[1].Value = item.productid;
            parms[2] = new SqlParameter("@userid", SqlDbType.NVarChar, 50);
            parms[2].Value = item.userid;
            parms[3] = new SqlParameter("adddate", SqlDbType.DateTime);
            parms[3].Value = item.adddate;
            parms[4] = new SqlParameter("@vipprice", SqlDbType.Decimal);
            parms[4].Value = item.vipprice;
            parms[5] = new SqlParameter("@buynum", SqlDbType.Int);
            parms[5].Value =item.buynum;
            parms[6] = new SqlParameter("@cartstate", SqlDbType.Int);
            parms[6].Value = item.cartstate;

            string sql = "exec dbo.www_getno 'usercart',10,@id output " +
                         "Insert into usercart(cartid,productid,userid,adddate,vipprice,buynum,cartstate) VALUES (@id,@productid,@userid,@adddate,@vipprice,@buynum,@cartstate)";
            int result = 0;
            try
            {
                result = SqlHelper.ExecuteNonQuery(SqlHelper.connectionstring, CommandType.Text, sql, parms);
                return result > 0;
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }
            finally
            {
            }
        }

        public void DeleteUser(decimal UserId)
        {
            SqlParameter[] parameters=new SqlParameter[1];
            parameters[0]=new SqlParameter("@UserId" , UserId);

            DataBase.Instance.ExecuteCommand("dbo.sproc_DeleteUser" , CommandType.StoredProcedure , parameters , null);
        }

        public static string SP_Tender_FindRecord(string ProcessId, ref DataSet ReturnDs)
        {
            SqlConnection sqlConn = new SqlConnection();  //defines database connection
            SqlCommand sqlCmd = new SqlCommand();  //defines what to do
            SqlDataAdapter sqlAdap = new SqlDataAdapter();

            try
            {
                sqlConn.ConnectionString = ConfigurationManager.ConnectionStrings["AMS_MasterConnectionString"].ToString();
                sqlConn.Open();

                sqlCmd.CommandText = "SP_Tender_FindRecord";
                sqlCmd.CommandType = CommandType.StoredProcedure;
                sqlCmd.Connection = sqlConn;

                SqlParameter parm1 = new SqlParameter("@ProcessId", SqlDbType.VarChar);
                parm1.Value = ProcessId;
                parm1.Direction = ParameterDirection.Input;
                sqlCmd.Parameters.Add(parm1);

                sqlAdap.SelectCommand = sqlCmd;
                sqlAdap.Fill(ReturnDs);

                return string.Empty;

            }
            catch (Exception err)
            {
                return err.Message;
            }
            finally
            { sqlConn.Close(); sqlConn.Dispose(); sqlAdap.Dispose(); }
        }

        internal static void AssignParameterValues(SqlParameter[] commandParameters, object[] parameterValues)
        {
            try
            {
                if ((commandParameters == null) || (parameterValues == null))
                {
                    return;
                }

                if (commandParameters.Length != parameterValues.Length)
                {
                    throw new ArgumentException("Parameter count does not match Parameter Value count.");
                }

                for (int i = 0, j = commandParameters.Length; i < j; i++)
                {
                    commandParameters[i].Value = parameterValues[i];
                }
            }
            catch (SystemException ex)
            {
                throw ex;
            }
            catch (Exception ex)
            {
                throw ex;
            }

        }

        public SigmaResultType AddEquipment(TypeEquipment objEquipment)
        {
            TypeUserInfo userinfo = AuthMgr.GetUserInfo();

            SigmaResultType result = new SigmaResultType();
            TransactionScope scope = null;

            // Get connection string
            string connStr = ConnStrHelper.getDbConnString();

            List<SqlParameter> paramList = new List<SqlParameter>();
            paramList.Add(new SqlParameter("@EquipmentCodeMain", objEquipment.EquipmentCodeMain.Trim()));
            paramList.Add(new SqlParameter("@EquipmentCodeSub", objEquipment.EquipmentCodeSub.Trim()));
            paramList.Add(new SqlParameter("@Description", objEquipment.Description.Trim()));
            paramList.Add(new SqlParameter("@ThirdLevel", objEquipment.ThirdLevel.Trim()));
            paramList.Add(new SqlParameter("@Spec", objEquipment.Spec.Trim()));
            paramList.Add(new SqlParameter("@EquipmentType", objEquipment.EquipmentType.Trim()));
            paramList.Add(new SqlParameter("@CreatedBy", userinfo.SigmaUserId.Trim()));
            paramList.Add(new SqlParameter("@ModelNumber", objEquipment.ModelNumber.Trim()));
            SqlParameter outParam = new SqlParameter("@NewId", SqlDbType.Int);
            outParam.Direction = ParameterDirection.Output;
            paramList.Add(outParam);

            using (scope = new TransactionScope(TransactionScopeOption.Required))
            {
                result.AffectedRow = SqlHelper.ExecuteNonQuery(connStr, CommandType.StoredProcedure, "usp_AddEquipment", paramList.ToArray());
                result.IsSuccessful = true;
                result.ScalarValue = (int)outParam.Value;

                scope.Complete();
            }

            return result;
        }

        public static DataSet ExecuteDataSet(CommandType cmdType, string strProcedureName, SqlParameter[] objParameters)
        {
            DataSet dset = new DataSet();
            SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["db1ConnectionString"].ToString());
            con.Open();
            try
            {
                SqlCommand cmd = new SqlCommand();
                cmd.Connection = con;
                cmd.CommandType = cmdType;
                cmd.CommandText = strProcedureName;

                AttachParameters(cmd, objParameters);

                SqlDataAdapter ad = new SqlDataAdapter(cmd);
                ad.Fill(dset);

                return dset;

            }
            catch (Exception ex)
            {
                return dset;
            }
            finally
            {
                con.Close();
            }
        }

        public static void ExecuteNonQuery( CommandType cmdType, string strProcedureName, SqlParameter[] objParameters)
        {
            SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["db1ConnectionString"].ToString());
            con.Open();
            try
            {
                SqlCommand cmd = new SqlCommand();
                cmd.Connection = con;
                cmd.CommandType = cmdType;
                cmd.CommandText = strProcedureName;

                AttachParameters(cmd, objParameters);

                cmd.ExecuteNonQuery();

            }
            catch (Exception ex)
            {

            }
            finally
            {

                con.Close();
            }
        }

        public DataSet PRC_PersonasListasDePrecios()
        {
            ArrayList Parameters=new ArrayList(0);

            SqlParameter MODOParameter=new SqlParameter("@MODO",SqlDbType.Int);
            MODOParameter.Size=0;
            MODOParameter.Value=MODO;
            Parameters.Add(MODOParameter);

            SqlParameter PersonasListasDePrecios_IDParameter=new SqlParameter("@PersonasListasDePrecios_ID",SqlDbType.Int);
            PersonasListasDePrecios_IDParameter.Size=0;
            PersonasListasDePrecios_IDParameter.Value=PersonasListasDePrecios_ID;
            Parameters.Add(PersonasListasDePrecios_IDParameter);

            SqlParameter Personas_IDParameter=new SqlParameter("@Personas_ID",SqlDbType.Int);
            Personas_IDParameter.Size=0;
            Personas_IDParameter.Value=Personas_ID;
            Parameters.Add(Personas_IDParameter);

            SqlParameter ListasDePrecios_IDParameter=new SqlParameter("@ListasDePrecios_ID",SqlDbType.Int);
            ListasDePrecios_IDParameter.Size=0;
            ListasDePrecios_IDParameter.Value=ListasDePrecios_ID;
            Parameters.Add(ListasDePrecios_IDParameter);

            SqlParameter PersonasDirecciones_IDParameter=new SqlParameter("@PersonasDirecciones_ID",SqlDbType.Int);
            PersonasDirecciones_IDParameter.Size=0;
            PersonasDirecciones_IDParameter.Value=PersonasDirecciones_ID;
            Parameters.Add(PersonasDirecciones_IDParameter);

             DataSet dsResult=ExecuteStoredProcedure("[Grifo].[PRC_PersonasListasDePrecios]",ref Parameters);

            return dsResult;
        }

        public bool InserirDA(Usuarios usu)
        {
            SqlParameter[] paramsToSP = new SqlParameter[12];

            paramsToSP[0] = new SqlParameter("@login", usu.Login);
            paramsToSP[1] = new SqlParameter("@senha", utils.Criptografar(usu.Senha));
            paramsToSP[2] = new SqlParameter("@nome", usu.Nome.ToUpper());
            paramsToSP[3] = new SqlParameter("@status", usu.Status);
            paramsToSP[4] = new SqlParameter("@dtinicio", usu.DtInicio);
            paramsToSP[5] = new SqlParameter("@dtfim", usu.DtFim);
            paramsToSP[6] = new SqlParameter("@tipo", usu.Tipo);
            paramsToSP[7] = new SqlParameter("@email", usu.Email);
            paramsToSP[8] = new SqlParameter("@pessoaid", usu.PessoaId);
            paramsToSP[9] = new SqlParameter("@nrtentlogin", usu.NrTentLogin);
            paramsToSP[10] = new SqlParameter("@dhtentlogin", usu.DhTentLogin);
            paramsToSP[11] = new SqlParameter("@categoriaid", usu.CategoriaId);

            try
            {
                SqlHelper.ExecuteNonQuery(ConfigurationManager.ConnectionStrings["conexao"].ToString(), CommandType.StoredProcedure, "stp_insert_usuarios", paramsToSP);

                return true;
            }
            catch (Exception e)
            {
                return false;
            }
        }