C# SqlCommand类代码示例

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

    protected int getTotalCount()
    {
        SqlConnection connection = new SqlConnection(GetConnectionString());

        DataTable dt = new DataTable();

        try
        {
            connection.Open();
            string sqlStatement = "SELECT * FROM tblContact";
            SqlCommand sqlCmd = new SqlCommand(sqlStatement, connection);
            SqlDataAdapter sqlDa = new SqlDataAdapter(sqlCmd);
            sqlDa.Fill(dt);

        }
        catch (System.Data.SqlClient.SqlException ex)
        {
            string msg = "Fetch Error:";
            msg += ex.Message;
            throw new Exception(msg);
        }
        finally
        {
            connection.Close();
        }
        return dt.Rows.Count;
    }

    public List<string> consultarMarca(string id)
    {
        List<string> marcas = new List<string>();
        SqlConnection conn = new SqlConnection(@"Data Source=.\SQLEXPRESS;AttachDbFilename='|DataDirectory|DBTag.mdf';Integrated Security=True;User Instance=True");
        // Abre a conexão
        conn.Open();
        try
        {
            SqlCommand select = new SqlCommand("SELECT * from TbMarca where [email protected]", conn);
            SqlParameter pID = new SqlParameter("id", id);
            select.Parameters.Add(pID);
            // Lê, linha a linha a tabela
            SqlDataReader dr = select.ExecuteReader();
            while (dr.Read())
            {
                marcas.Add(dr["id"].ToString());
                marcas.Add(dr["nome_marca"].ToString());
            }
        }
        catch (Exception ex)
        {
            throw new Exception(ex.ToString(), ex);
        }

        return marcas;
    }

    //Función que añade un proyecto nuevo
    public String anadirProyecto(string titulo, string tituloEng, string subtitulo, string subtituloEng, int logo, string descripcion, string descripcionEng, int activo)
    {
        string valdev = string.Empty;

        SqlCommand cmd = new SqlCommand();
        SqlParameter sqlPar = null;
        sqlPar = cmd.Parameters.Add("@TITULO", SqlDbType.VarChar, 200);
        sqlPar.Value = titulo;
        sqlPar = cmd.Parameters.Add("@TITULO_ENG", SqlDbType.VarChar, 200);
        sqlPar.Value = tituloEng;
        sqlPar = cmd.Parameters.Add("@SUBTITULO", SqlDbType.VarChar, 150);
        sqlPar.Value = subtitulo;
        sqlPar = cmd.Parameters.Add("@SUBTITULO_ENG", SqlDbType.VarChar, 150);
        sqlPar.Value = subtituloEng;
        sqlPar = cmd.Parameters.Add("@LOGO", SqlDbType.Bit);
        sqlPar.Value = logo;
        sqlPar = cmd.Parameters.Add("@DESCRIPCION", SqlDbType.VarChar);
        sqlPar.Value = descripcion;
        sqlPar = cmd.Parameters.Add("@DESCRIPCION_ENG", SqlDbType.VarChar);
        sqlPar.Value = descripcionEng;
        sqlPar = cmd.Parameters.Add("@ACTIVO", SqlDbType.Bit);
        sqlPar.Value = activo;

        valdev = con.execProcedureValor(cmd, "spr_AnadirProyecto");

        return valdev;
    }

    protected override void OnLoad(EventArgs e)
    {
        base.OnLoad(e);

        if (!this.IsPostBack)
        {
            //efectuar o data binding
            //dDL.DataSource = distritos;
            //dDL.DataBind();

            SqlDB Bd = new SqlDB("ConStr_DivAdmin");
            string str = "SELECT NomeDistrito from Distritos";

            SqlCommand cmd = new SqlCommand(str, Bd.SqlConDB);

            Bd.SqlConDB.Open();
            SqlDataReader dR = cmd.ExecuteReader();

            //efectuar o data binding
            dDL.DataSource = dR;
            dDL.DataTextField = "NomeDistrito";
            dDL.DataBind();
            dR.Close();
            Bd.SqlConDB.Close();
        }

        return;
    }

    protected void btnSave_Click(object sender, EventArgs e)
    {
        DataAccess dataaccess = new DataAccess();

        using (SqlConnection Sqlcon = dataaccess.OpenConnection())
        {
            using (SqlCommand cmd = new SqlCommand())
            {

                cmd.Connection = Sqlcon;
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.CommandText = "InsertITJob";
                cmd.Parameters.Add(new SqlParameter("@Exists", SqlDbType.Int));
                cmd.Parameters.Add(new SqlParameter("@ITJobHeading", SqlDbType.VarChar, 100));
                cmd.Parameters.Add(new SqlParameter("@ITJobDesc", SqlDbType.NVarChar, 50));
                cmd.Parameters.Add(new SqlParameter("@ITJobDate", SqlDbType.DateTime));

                cmd.Parameters["@ITJobHeading"].Value = txtITJob.Text;
                cmd.Parameters["@ITJobDesc"].Value = txtITDesc.Text;
                cmd.Parameters["@ITJobDate"].Value = System.DateTime.Now;

                cmd.Parameters["@Exists"].Value = 0;
                cmd.ExecuteNonQuery();
                int retVal = (int)cmd.Parameters["@Exists"].Value;
            }
        }
        LoadData();
        PanelShow.Visible = true;
        PanelAdd.Visible = false;
        txtITJob.Text = "";
        txtITDesc.Text = "";
    }

    protected void LinkButton1_Click(object sender, EventArgs e)
    {
        SqlConnection con = new SqlConnection("Data Source=DIP\\SQLEXPRESS;Initial Catalog=UVPCE_DB;Integrated Security=True");
        SqlCommand cmd = new SqlCommand("insert into [user](username,subject,comment,posteddate) values(@username,@subject,@comment,@postedate)", con);

        cmd.Parameters.AddWithValue("@username", Textname.Text);
        cmd.Parameters.AddWithValue("@subject", txtSubject.Text);
        cmd.Parameters.AddWithValue("@comment", txtComment.Text);
        cmd.Parameters.AddWithValue("@postedate", DateTime.Now);
        con.Open();
        //SqlCommand cmd = new SqlCommand("insert into (username,subject,comment,posteddate) values('"+Textname.Text+"','"+txtSubject.Text+"','"+txtComment+"')",con);
        cmd.ExecuteNonQuery();
        // con.Close();

        SqlCommand cmd1 = new SqlCommand("select no from [user] where username='" + Textname.Text + "' and subject='" + txtSubject.Text + "' and comment='" + txtSubject.Text + "'", con);
        SqlDataReader dr = cmd1.ExecuteReader();
        while (dr.Read())
        {
            Label1.Text = dr["no"].ToString();
        }

        Textname.Text = string.Empty;
        txtSubject.Text = string.Empty;
        txtComment.Text = string.Empty;
        BindRepeaterData();
    }

    public static List<string> SearchEmployees(string prefixText, int count)
    {
        using (SqlConnection conn = new SqlConnection())
        {
            conn.ConnectionString = ConfigurationManager.ConnectionStrings["rateMyMPConnectionString"].ConnectionString;
            using (SqlCommand cmd = new SqlCommand())
            {

                cmd.CommandText = "SELECT dbo.mpDetails.constituencyId,dbo.userMaster.profilePic, dbo.constituency.constituency, dbo.state.state, dbo.userMaster.firstName, dbo.userMaster.middleName, dbo.userMaster.lastName,dbo.userMaster.profilePic FROM dbo.constituency INNER JOIN dbo.state ON dbo.constituency.stateId = dbo.state.stateId INNER JOIN dbo.mpDetails ON dbo.state.stateId = dbo.mpDetails.permanentStateId AND dbo.constituency.constituencyId = dbo.mpDetails.constituencyId INNER JOIN dbo.userMaster ON dbo.mpDetails.guid = dbo.userMaster.guid where dbo.userMaster.firstName like '%' + @search+'%' or dbo.userMaster.middleName like '%'+ @search+'%' or dbo.userMaster.lastName like '%' + @search+'%' or  dbo.state.state  like '%'+ @search+'%' or dbo.constituency.constituency like '%'+ @search+'%'";
                cmd.Parameters.AddWithValue("@search", prefixText);
                cmd.Connection = conn;
                conn.Open();
                List<string> employees = new List<string>();
                using (SqlDataReader sdr = cmd.ExecuteReader())
                {
                    while (sdr.Read())
                    {
                        employees.Add(AjaxControlToolkit.AutoCompleteExtender
                           .CreateAutoCompleteItem(string.Format("{0}{1}{2}{3}{4}{5} ",
                           sdr["firstName"] + " ", sdr["middleName"] + " ", sdr["lastName"] + " ,", sdr["constituency"] + " ,", sdr["state"] + " ,", sdr["profilePic"].ToString()),
                           sdr["constituencyId"].ToString()));
                    }
                }
                conn.Close();
                return employees;
            }
        }
    }

    public String eliminaSucursales(DataSucursal ds)
    {
        string msg = "";

        SqlCommand cmd = new SqlCommand("USP_ELIMINAR_SUCURSAL", cn.getCn);
        cmd.CommandType = CommandType.StoredProcedure;

        cmd.Parameters.Add("@id", SqlDbType.Int).Value = ds.Id;

        cn.getCn.Open();
        try
        {
            msg = cmd.ExecuteNonQuery().ToString() + " Sucursal eliminado";
        }
        catch (Exception ex)
        {
            msg = ex.Message;

        }
        finally
        {
            cn.getCn.Close();
        }
        return msg;
    }

 //  To insert 'Group' record in database by stored procedure
 public int InsertGroup(string GroupName, string GroupDescription,bool IsActive, int  LoginUser,string Ret)
 {
     SqlConnection Conn = new SqlConnection(ConnStr);
         Conn.Open();
         //  'uspInsertGroup' stored procedure is used to insert record in Group table
         SqlCommand DCmd = new SqlCommand("uspInsertGroup", Conn);
         DCmd.CommandType = CommandType.StoredProcedure;
         try
         {
             DCmd.Parameters.AddWithValue("@GroupName", GroupName);
             DCmd.Parameters.AddWithValue("@GroupDescription", GroupDescription);
             DCmd.Parameters.AddWithValue("@LoggedInUser", LoginUser);
             DCmd.Parameters.AddWithValue("@IsActive", IsActive);
             DCmd.Parameters.AddWithValue("@RetMsg", Ret);
              return DCmd.ExecuteNonQuery();
         }
         catch
         {
             throw;
         }
         finally
         {
             DCmd.Dispose();
             Conn.Close();
             Conn.Dispose();
         }
 }

    public static List<ReporteNotificacionGeocerca> ObtenerNotificacionesGeocerca(List<string> unidades, List<string> geocercas, DateTime fechaInicial, DateTime fechaFinal, int accion, string cliente)
    {
        List<ReporteNotificacionGeocerca> reporteFinal = new List<ReporteNotificacionGeocerca>();
        List<Vehiculo> vehiculos = AutentificacionBD.VehiculosCliente(cliente);
        string query = "SELECT     Unidad, accion, fechahora, geocerca, nombre, id_reportegeocerca " +
                       "FROM         vReporteGeocercaUTC " +
                       "WHERE     (Unidad IN (";
        foreach (string unidad in unidades)
            query += "'" + unidad + "', ";
        //quitar la última coma y espacio
        query = query.Remove(query.Length - 2);
        //agregar los dos paréntesis finales.
        query += ")) AND (fechahora between @fechainicial AND @fechafinal) ";
        if (accion == ReporteNotificacionGeocerca.AccionFuera)
        {
            query += "AND accion = 'Fuera' ";
        }
        else if(accion == ReporteNotificacionGeocerca.AccionDentro)
        {
            query += "AND accion = 'Dentro' ";
        }
        else if (accion == ReporteNotificacionGeocerca.AccionDentroFuera)
        {
            query += " AND (accion = 'Fuera' OR accion = 'Dentro') ";
        }
        query +="ORDER BY fechahora DESC";

        using (SqlConnection sqlConnection = new SqlConnection(ConfigurationManager.ConnectionStrings["connectionStringBD"].ConnectionString))
        {
            sqlConnection.Open();
            using (SqlCommand sqlCommand = new SqlCommand(query,sqlConnection))
            {
                sqlCommand.Parameters.AddWithValue("@fechainicial", fechaInicial);
                sqlCommand.Parameters.AddWithValue("@fechafinal", fechaFinal);
                Hashtable nombresGeocercas = GeocercasBD.ConsultarGeocercasNombreBD(cliente);
                using (SqlDataReader reader = sqlCommand.ExecuteReader())
                {
                    while (reader.Read()){
                        string geocercaID= (string)reader["geocerca"];
                        if (geocercas.Where(x=>x.Equals(geocercaID)).ToList().Count == 1)
                        {
                            ReporteNotificacionGeocerca reporte = new ReporteNotificacionGeocerca();
                            string sVehiculo = (string)reader["Unidad"];
                            string sAccionBD = (string)reader["accion"];
                            reporte.VehiculoID = sVehiculo;
                            reporte.GeocercaID = geocercaID;
                            reporte.Fecha = (DateTime)reader["fechahora"];
                            reporte.Vehiculo = vehiculos.Find(x=> x.Unidad.Equals(sVehiculo)).Descripcion;
                            //reporte.Accion = sAccionBD == "Dentro"?Resources.Reportes.aspx.Dentro:Resources.Reportes.aspx.Fuera;
                            reporte.iAccion = sAccionBD == "Dentro" ? ReporteNotificacionGeocerca.AccionDentro : ReporteNotificacionGeocerca.AccionFuera;
                            reporte.Geocerca = nombresGeocercas[geocercaID].ToString();
                            reporteFinal.Add(reporte);
                        }
                    }
                }
            }
        }

        return reporteFinal.OrderBy(x => x.Fecha).ToList();
    }

 //  To Change status of 'Group' record of specific GroupId from database by stored procedure
 public int ChangeGroupStatus(int GroupId, int LoggedInUser, string returnmsg, bool IsActive)
 {
     SqlConnection Conn = new SqlConnection(ConnStr);
         Conn.Open();
         //  'uspUpdateGroupStatus' stored procedure is used to Chnage Status of record in Group table
         SqlCommand DCmd = new SqlCommand("uspUpdateGroupStatus", Conn);
         DCmd.CommandType = CommandType.StoredProcedure;
         DCmd.Parameters.AddWithValue("@GroupId", GroupId);
         DCmd.Parameters.AddWithValue("@LoggedInUser", LoggedInUser);
         DCmd.Parameters.AddWithValue("@IsActive", IsActive);
         DCmd.Parameters.AddWithValue("@RetMsg", returnmsg);
         try
         {
             return DCmd.ExecuteNonQuery();
         }
         catch
         {
             throw;
         }
         finally
         {
             DCmd.Dispose();
             Conn.Close();
             Conn.Dispose();
         }
 }

    public static string PublishActivity(int ClubId, string ActivityContent)
    {
        // 将新增活动存入数据库，并从数据库返回信息及数据
        string connString = System.Configuration.ConfigurationManager.ConnectionStrings["CZConnectionString"].ConnectionString;
        SqlConnection conn = new SqlConnection(connString);
        conn.Open();
        // 存储
        string PublishDate = DateTime.Now.ToString();
        string queryString1 = "Insert Into Activity Values (" + ClubId + ",N'" + ActivityContent + "','" + PublishDate + "')";
        SqlCommand cmd = new SqlCommand(queryString1, conn);
        cmd.ExecuteNonQuery();
        // 查询最后插入的数据，就是新的数据
        string queryString2 = "Select Top 1 * From Activity Where ClubId=" + ClubId + " Order By PublishDate Desc";
        cmd = new SqlCommand(queryString2, conn);
        SqlDataAdapter adapter = new SqlDataAdapter(cmd);
        DataSet ds = new DataSet();
        adapter.Fill(ds);
        int Id = Convert.ToInt32(ds.Tables[0].Rows[0]["Id"].ToString());
        string Content = ds.Tables[0].Rows[0]["Content"].ToString();
        string Date = ds.Tables[0].Rows[0]["PublishDate"].ToString();

        conn.Close();

        // 通过判断前后时间知是否查入成功
        if (PublishDate == Date)
        {
            return "{status:1,id:" + Id + ",content:'" + Content + "',date:'" + Date + "'}";
        }
        else
        {
            return "{status:-1}";
        }
    }

        protected void Fill_User_Header()
        {
            DataView view = null;
            SqlConnection con;
            SqlCommand cmd = new SqlCommand();
            DataSet ds     = new DataSet();
            DataTable dt   = new DataTable();
            System.Configuration.Configuration rootWebConfig = System.Web.Configuration.WebConfigurationManager.OpenWebConfiguration("/BitOp");
            System.Configuration.ConnectionStringSettings connString;
            connString = rootWebConfig.ConnectionStrings.ConnectionStrings["BopDBConnectionString"];
            con            = new SqlConnection(connString.ToString());
            cmd.Connection = con;
            con.Open();
            string sql = @"SELECT Fecha_Desde, Inicio_Nombre, Region, Supervisor
                         FROM Criterios
                         WHERE Criterio_ID = " + @Criterio_ID;
            SqlDataAdapter da = new SqlDataAdapter(sql, con);
            da.Fill(ds);
            dt = ds.Tables[0];
            view = new DataView(dt);
            foreach (DataRowView row in view)
            {
                Lbl_Fecha_Desde.Text    = row["Fecha_Desde"].ToString("dd-MM-yyyy");
                Lbl_Inicio_Descrip.Text = row["Inicio_Nombre"].ToString();
                Lbl_Region.Text         = row["Region"].ToString();
                Lbl_Supervisor.Text     = row["Supervisor"].ToString();
             }

            con.Close();
        }

    public bool tblPackageTime_Delete(int packageTimeId)
    {
        int i = 0;
        try
        {
            if (ConnectionData._MyConnection.State == ConnectionState.Closed)
            {
                ConnectionData._MyConnection.Open();
            }
            SqlCommand cmd = new SqlCommand("DELETE FROM tblpackageTime WHERE packageTimeId = @packageTimeId",
                ConnectionData._MyConnection);
            cmd.CommandType = CommandType.Text;
            cmd.Parameters.Add("@packageTimeId", SqlDbType.Int).Value = packageTimeId;
            i = cmd.ExecuteNonQuery();
            cmd.Dispose();
        }
        catch (Exception ex)
        {

        }
        finally
        {
            ConnectionData._MyConnection.Close();
        }
        return i > 0;
    }