C# SqlClient.SqlParameter类代码示例

        private void btnPreg_Click(object sender, EventArgs e)
        {
            if (this.txtPreg.Text == string.Empty)
            {
                MessageBox.Show("Ingrese un texto para la pregunta");
                return;
            }

            // Creo la pregunta

            //Abro la conexion
            AccesoDatos.getInstancia().abrirConexion();

            System.Data.SqlClient.SqlCommand comVenta = new System.Data.SqlClient.SqlCommand("LOS_GESTORES.sp_app_creaPregunta");

            System.Data.SqlClient.SqlParameter p1V = new System.Data.SqlClient.SqlParameter("@id_usu", this.id_comprador);
            comVenta.Parameters.Add(p1V);

            System.Data.SqlClient.SqlParameter p2V = new System.Data.SqlClient.SqlParameter("@id_pub", this.id_pub);
            comVenta.Parameters.Add(p2V);

            System.Data.SqlClient.SqlParameter p3V = new System.Data.SqlClient.SqlParameter("@txt", this.txtPreg.Text.Trim());
            comVenta.Parameters.Add(p3V);

            System.Data.SqlClient.SqlParameter p4V = new System.Data.SqlClient.SqlParameter("@fecha", this.fecha_hoy);
            comVenta.Parameters.Add(p4V);

            System.Data.SqlClient.SqlDataReader rolReader = AccesoDatos.getInstancia().ejecutaSP(comVenta);

            //Cierro la conexion
            AccesoDatos.getInstancia().cerrarConexion();

            MessageBox.Show("Su pregunta se ha registrado con exito");
            this.Close();
        }

        public void AddParameter(string name, object value, System.Data.DbType paramtype)
        {
            System.Data.SqlClient.SqlParameter param = new System.Data.SqlClient.SqlParameter(name, paramtype);
            param.Value = value;

            comm.Parameters.Add(param);
        }

    private System.Data.DataRow GetParameter(string IDParametro, int? IDPortal, int? IDSistema, string IDUsuario)
    {
      // Aca se lee la informacion de la base de datos
      // y se preparan los layers
      string connStr = ValidacionSeguridad.Instance.GetSecurityConnectionString();
      System.Data.SqlClient.SqlConnection conn = new System.Data.SqlClient.SqlConnection(connStr);
      conn.Open();

      System.Data.SqlClient.SqlCommand cmd = new System.Data.SqlClient.SqlCommand
        ("SELECT * FROM dbo.SF_VALOR_PARAMETRO(@IDParametro, @IDPortal, @IDSistema, @IDUsuario)", conn);

      System.Data.SqlClient.SqlParameter prm = new System.Data.SqlClient.SqlParameter("@IDParametro", System.Data.SqlDbType.VarChar, 100);
      prm.Value = IDParametro;
      cmd.Parameters.Add(prm);

      prm = new System.Data.SqlClient.SqlParameter("@IDPortal", System.Data.SqlDbType.Int);
      if (IDPortal.HasValue)
      {
        prm.Value = IDPortal.Value;
      }
      else
      {
        prm.Value = null;
      }
      cmd.Parameters.Add(prm);

      prm = new System.Data.SqlClient.SqlParameter("@IDSistema", System.Data.SqlDbType.Int);
      if (IDSistema.HasValue)
      {
        prm.Value = IDSistema.Value;
      }
      else
      {
        prm.Value = null;
      }
      cmd.Parameters.Add(prm);

      prm = new System.Data.SqlClient.SqlParameter("@IDUsuario", System.Data.SqlDbType.VarChar);
      if (IDUsuario != null)
      {
        prm.Value = IDUsuario;
      }
      else
      {
        prm.Value = null;
      }
      cmd.Parameters.Add(prm);

      //     IdParametro, Alcance, ValorTexto, ValorEntero, ValorDecimal, ValorLogico, ValorFechaHora
      cmd.CommandType = System.Data.CommandType.Text;
      System.Data.SqlClient.SqlDataAdapter da = new System.Data.SqlClient.SqlDataAdapter(cmd);

      System.Data.DataSet ds = new System.Data.DataSet();
      da.Fill(ds);

      conn.Close();

      return ds.Tables[0].Rows[0];
      //return resultado;
    }

        public DataTable GetSPResult(string editor, string dbName, string spName, string[] ParameterNameArray, string[] ParameterValueArray)
        {
            if (ParameterNameArray == null || ParameterValueArray == null)
            {
                return null;
            }

            string ParameterValuesStr = "";
            System.Data.SqlClient.SqlParameter[] paramsArray = new System.Data.SqlClient.SqlParameter[ParameterNameArray.Length];
            for (int i = 0; i < ParameterNameArray.Length; i++)
            {
                paramsArray[i] = new System.Data.SqlClient.SqlParameter(ParameterNameArray[i].StartsWith("@") ? ParameterNameArray[i] : "@" + ParameterNameArray[i], ParameterValueArray[i]);
                ParameterValuesStr = ParameterValuesStr + paramsArray[i].ParameterName + "=" + ParameterValueArray[i] + ",";
            }
            ExecSQLLogger.Info("editor is: " + editor + ", Database is: " + dbName + ", SPName is: " + spName + ", Parameters is :" + ParameterValuesStr.TrimEnd(new char[] { ',' }));


            IProductRepository MyRepository = RepositoryFactory.GetInstance().GetRepository<IProductRepository, IMES.FisObject.FA.Product.IProduct>();
            string ConnectString = string.Format(IMES.Infrastructure.Repository._Schema.SqlHelper.ConnectionString, dbName);
            try
            {
                return IMES.Infrastructure.Repository._Schema.SqlHelper.ExecuteDataFillConsiderOutParams(ConnectString, CommandType.StoredProcedure, spName, paramsArray);
            }
            catch (Exception e)
            {
                ExecSQLLogger.Error(e.Message, e);
                throw e;
            }
        }

        public DataSet GetSQLResult(string editor, string dbName, string sqlText, string[] ParameterNameArray, string[] ParameterValueArray)
        {
            ExecSQLLogger.Info("editor is: " + editor + ", Database is: " + dbName + ", ExecSQL is: " + "SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED; \n" + sqlText);
            System.Data.SqlClient.SqlParameter[] paramsArray = null;
            if (ParameterNameArray != null && ParameterValueArray != null && ParameterNameArray.Length == ParameterValueArray.Length)
            {
                paramsArray = new System.Data.SqlClient.SqlParameter[ParameterNameArray.Length];
                for (int i = 0; i < ParameterNameArray.Length; i++)
                {
                    paramsArray[i] = new System.Data.SqlClient.SqlParameter(ParameterNameArray[i].StartsWith("@") ? ParameterNameArray[i] : "@" + ParameterNameArray[i], ParameterValueArray[i]);
                }
            }

            sqlText = "SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED; \n" + sqlText + " select @@ROWCOUNT as AffectRowCount";
            string ConnectString = string.Format(IMES.Infrastructure.Repository._Schema.SqlHelper.ConnectionString, dbName);
            if (string.IsNullOrEmpty(dbName))
            {
                ConnectString = IMES.Infrastructure.Repository._Schema.SqlHelper.ConnectionString_FA;
            }

            IProductRepository MyRepository = RepositoryFactory.GetInstance().GetRepository<IProductRepository, IMES.FisObject.FA.Product.IProduct>();
            try
            {
                return IMES.Infrastructure.Repository._Schema.SqlHelper.ExecSPorSql(ConnectString, CommandType.Text, sqlText, paramsArray);
            }
            catch (Exception e)
            {
                ExecSQLLogger.Error(e.Message, e);
                throw e;
            }
        }

 private static async Task DbCommandAsync(Phone p)
 {
     using (MobileContext db = new MobileContext())
     {
         System.Data.SqlClient.SqlParameter name = new System.Data.SqlClient.SqlParameter("name", p.Name);
         System.Data.SqlClient.SqlParameter price = new System.Data.SqlClient.SqlParameter("price", p.Price);
         await db.Database.ExecuteSqlCommandAsync("INSERT INTO Phones (Name, Price) VALUES (@name, @price)", name, price);
     }
 }

 public static System.Data.Objects.ObjectResult<BAOCAOLICHSUTONKHO> GetLichSuTonKho(KaraokeEntities kara, int KhoID, DateTime dtFrom, DateTime dtTo)
 {
     var Parameter_KhoID = new System.Data.SqlClient.SqlParameter("@KhoID", System.Data.SqlDbType.Int);
     Parameter_KhoID.Value = KhoID;
     var Parameter_DateFrom = new System.Data.SqlClient.SqlParameter("@DateFrom", System.Data.SqlDbType.DateTime);
     Parameter_DateFrom.Value = dtFrom;
     var Parameter_DateTo = new System.Data.SqlClient.SqlParameter("@DateTo", System.Data.SqlDbType.DateTime);
     Parameter_DateTo.Value = dtTo;
     return kara.ExecuteStoreQuery<BAOCAOLICHSUTONKHO>("SP_BAOCAOLICHSUTONKHO @KhoID, @DateFrom, @DateTo", Parameter_KhoID, Parameter_DateFrom, Parameter_DateTo);
 }

    public bool Save(string CodAntena, string TagEquipo)
    {
      // Aca se graba la informacion en la base de datos.

      //1. Para el caso RFID, donde del Agente está escuchando o leyendo los Data Logger cada 15 segundos se debe ejecutar la siguiente SP con los parámetros que se detallan:
      //-----------------------------------------------------------------------------------------------------------------------
      //-- Declare type parameter
      //DECLARE @RC int                                                   --Valor de retorno 0=OK
      //DECLARE @CODANTENA varchar(4)                         --Código de antena
      //DECLARE @TAGEQUIPO varchar(16)                        --Nro de Tag del equipo/camión
      //-- Set parameter values
      //SET @CODANTENA = '0001'
      //SET @TAGEQUIPO = 'A000000000000112'
      //EXEC @RC = [TARJA].[dbo].[STP_MOVIMIENTOS_PUNTOS_CONTROL] @CODANTENA, @TAGEQUIPO
      //-----------------------------------------------------------------------------------------------------------------------
      // Para los códigos de las antenas determinamos la siguiente codificación:
      //                       1 = Trapiche 1
      //2 = Trapiche 2
      //3 = Trapiche 3
      //4 = Bascula 9
      //5 = Bascula 10
      //6 = Portería Gas
      //7 = Portería Gas Interno)

      try
      {
        string strConnection = GetConnectionString();
        System.Data.SqlClient.SqlConnection conn = new System.Data.SqlClient.SqlConnection(strConnection);
        conn.Open();

        System.Data.SqlClient.SqlCommand cmd = new System.Data.SqlClient.SqlCommand();
        cmd.Connection = conn;
        cmd.CommandType = System.Data.CommandType.StoredProcedure;
        cmd.CommandText = "STP_MOVIMIENTOS_PUNTOS_CONTROL";

        System.Data.SqlClient.SqlParameter prm = new System.Data.SqlClient.SqlParameter("@CODANTENA", System.Data.SqlDbType.VarChar, 4);
        prm.Value = CodAntena;
        cmd.Parameters.Add(prm);

        prm = new System.Data.SqlClient.SqlParameter("@TAGEQUIPO", System.Data.SqlDbType.VarChar, 16);
        prm.Value = TagEquipo;
        cmd.Parameters.Add(prm);

        int rc =(int) cmd.ExecuteScalar();

        if (rc == 0)
          return true;
        else
          return false;
      }
      catch (Exception)
      {
        return false;
      }
    }

 public static System.Data.SqlClient.SqlParameter getDecimalInputParam(string ip_name, 
     object ip_value)
 {
     Debug.Assert(ip_name.IndexOf(C_PREFIX_OF_PARAM) >= 0, C_WRONG_PARAM_NAME);
     System.Data.SqlClient.SqlParameter v_sqlPara = new System.Data.SqlClient.SqlParameter();
     v_sqlPara.SqlDbType = SqlDbType.Decimal;
     v_sqlPara.Direction = ParameterDirection.Input;
     v_sqlPara.Value = ip_value;
     v_sqlPara.ParameterName = ip_name;
     return v_sqlPara;
 }

        public static System.Data.SqlClient.SqlParameter[] NullParameters()
        {
                System.Data.SqlClient.SqlParameter[] retVal = new System.Data.SqlClient.SqlParameter[4];

                retVal[0] = new System.Data.SqlClient.SqlParameter("maxlong", null);
                retVal[1] = new System.Data.SqlClient.SqlParameter("minLong", null);
                retVal[2] = new System.Data.SqlClient.SqlParameter("maxlat", null);
                retVal[3] = new System.Data.SqlClient.SqlParameter("minlat", null);
            return retVal;

        }

 public override System.Data.IDataReader AgeClassGetAll()
 {
     System.Data.SqlClient.SqlParameter[] arrSqlParameter = new System.Data.SqlClient.SqlParameter[0];
     if ((_SqlTransaction == null))
     {
         return ((System.Data.IDataReader)(SqlHelper.ExecuteReader(ConnectionString, "usp_AgeClassGetAll", arrSqlParameter)));
     }
     else
     {
         return ((System.Data.IDataReader)(SqlHelper.ExecuteReader(_SqlTransaction, "usp_AgeClassGetAll", arrSqlParameter)));
     }
 }

 public override void AgeClassDelete(int AgeClassID)
 {
     System.Data.SqlClient.SqlParameter[] arrSqlParameter = new System.Data.SqlClient.SqlParameter[] {
             new System.Data.SqlClient.SqlParameter("@AgeClassID", Helper.CheckForNullValue(AgeClassID))};
     if ((_SqlTransaction == null))
     {
         SqlHelper.ExecuteNonQuery(ConnectionString, "usp_AgeClassDelete", arrSqlParameter);
     }
     else
     {
         SqlHelper.ExecuteNonQuery(_SqlTransaction, "usp_AgeClassDelete", arrSqlParameter);
     }
 }

 public override System.Data.IDataReader AgeClassGet(int AgeClassID)
 {
     System.Data.SqlClient.SqlParameter[] arrSqlParameter = new System.Data.SqlClient.SqlParameter[] {
             new System.Data.SqlClient.SqlParameter("@AgeClassID", Helper.CheckForNullValue(AgeClassID))};
     if ((_SqlTransaction == null))
     {
         return ((System.Data.IDataReader)(SqlHelper.ExecuteReader(ConnectionString, "usp_AgeClassGet", arrSqlParameter)));
     }
     else
     {
         return ((System.Data.IDataReader)(SqlHelper.ExecuteReader(_SqlTransaction, "usp_AgeClassGet", arrSqlParameter)));
     }
 }

 public static NHANVIEN CheckLogin(KaraokeEntities kara,string user, string pass)
 {
     if (!String.IsNullOrEmpty(user) && !String.IsNullOrEmpty(pass))
     {
         var Parameter_TenDangNhap = new System.Data.SqlClient.SqlParameter("@TenDangNhap", System.Data.SqlDbType.VarChar, 50);
         Parameter_TenDangNhap.Value = user;
         var Parameter_MatKhau = new System.Data.SqlClient.SqlParameter("@MatKhau", System.Data.SqlDbType.VarChar, 255);
         Parameter_MatKhau.Value = pass;
         NHANVIEN nv = kara.ExecuteStoreQuery<NHANVIEN>("SP_Login_NhanVien @TenDangNhap, @MatKhau", Parameter_TenDangNhap, Parameter_MatKhau).FirstOrDefault();
         return nv;
     }
     return null;
 }

 public IQueryable<BAOCAOKHU> GetBaoCaoKhu(DateTime dtFrom, DateTime dtTo)
 {
     //return from x in mKaraokeEntities.BAOCAOKHUs
     //       where dtFrom.CompareTo(x.NgayBan.Value) <= 0 && dtTo.CompareTo(x.NgayBan.Value) >= 0
     //       orderby x.TenKhu
     //       select x;
     var paraFrom = new System.Data.SqlClient.SqlParameter("@NgayBatDau", System.Data.SqlDbType.DateTime);
     paraFrom.Value = dtFrom;
     var paraTo = new System.Data.SqlClient.SqlParameter("@NgayKetThuc", System.Data.SqlDbType.DateTime);
     paraTo.Value = dtTo;
     var sql = mKaraokeEntities.ExecuteStoreQuery<BAOCAOKHU>("SP_REPORT_BAOCAOKHU @NgayBatDau,@NgayKetThuc", paraFrom, paraTo);
     return sql.AsQueryable<BAOCAOKHU>();
 }