C# SqlConnection类代码示例

    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 static DataTable GetAllCidades(int estado_id)
        {
            DataTable retorno = new DataTable();
            StringBuilder SQL = new StringBuilder();
            SQL.Append(@"SELECT CidadeId, Nome FROM Cidade WHERE EstadoId = @ESTADO_ID");

            try
            {
                using (SqlConnection connection = new SqlConnection(ConfigurationManager.ConnectionStrings["Mendes_varejo"].ConnectionString))
                {
                    connection.Open();
                    SqlCommand command = new SqlCommand(SQL.ToString(), connection);
                    command.Parameters.AddWithValue("@ESTADO_ID", estado_id);
                    command.ExecuteNonQuery();
                    SqlDataAdapter adapter = new SqlDataAdapter(command);
                    adapter.Fill(retorno);
                }
            }
            catch (Exception erro)
            {
                throw erro;
            }

            return retorno;
        }

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

        public Video[] GetAllVideo()
        {
            List<Video> myVideos = new List<Video>();
            SqlConnection myConn = new SqlConnection(connstring);
            myConn.Open();

            SqlCommand mySqlCommand = new SqlCommand("select * from video", myConn);
            SqlDataReader reader = mySqlCommand.ExecuteReader();

            while (reader.Read())
            {
                Video myVideo = new Video();
                object id = reader["Id"];

                if(id != null)
                {
                    int videoId = -1;
                    if (!int.TryParse(id.ToString(), out videoId))
                    {
                        throw new Exception("Failed to parse Id of video.");
                    }

                    myVideo.Id = videoId;
                }

                myVideo.Name = reader["Name"].ToString();
                myVideo.Url = reader["Url"].ToString();
                myVideos.Add(myVideo);
            }

            myConn.Close();

            return myVideos.ToArray();
        }

    protected void Submit_Click1(object sender, EventArgs e)
    {
        try
        {
            //generate a new GUID ID
            Guid newGUID = Guid.NewGuid();

            SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["RegistrationConnectionString"].ConnectionString);
            conn.Open();
            string insertQuery = "insert into UserData (ID,UserName,Email,Password,Country) values (@ID ,@Uname ,@email ,@password, @country)";
            SqlCommand com = new SqlCommand(insertQuery, conn);
            com.Parameters.AddWithValue("@ID", newGUID.ToString());
            com.Parameters.AddWithValue("@Uname", IDSUaserName.Text);
            com.Parameters.AddWithValue("@email", IDSEmail.Text);
            com.Parameters.AddWithValue("@password", IDSPassword.Text);
            com.Parameters.AddWithValue("@country", IDSCountry.SelectedItem.ToString());

            com.ExecuteNonQuery();
            Response.Redirect("Manager.aspx");
            Response.Write("Registration is sucessful");

            conn.Close();
        }
        catch(Exception ex)
        {
            Response.Write("Error:"+ex.ToString());
        }
    }

    //method to open or close the database connection
    public SqlConnection ManageDatabaseConnection(string actionToPerform)
    {
        string connectionString = "Data Source=ph0ibk90ya.database.windows.net;Initial Catalog=ParkingLot;Integrated Security=False;User ID=samara;Password=s4m4r4DEV;Connect Timeout=100;Encrypt=False;TrustServerCertificate=False;ApplicationIntent=ReadWrite;MultiSubnetFailover=False";
        SqlConnection sqlConnection = new SqlConnection(connectionString);

        try
        {
            //desicion to whether open or close the database connection
            if (actionToPerform.Equals("Open"))
            {
                sqlConnection.Open();
            }
            else
            {
                sqlConnection.Close();
            }

        }
        catch (SqlException sqlException)
        {

            //throw the exception to upper layers
            throw sqlException;
        }

        return sqlConnection;
    }

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

    SqlConnection connect; //database object

    public LoginClass(string usr, string pass)
    {
        username = usr;
        password = pass;
        connect = new SqlConnection(ConfigurationManager.ConnectionStrings["CommunityAssistConnectionString"].ConnectionString);

    }

    protected void Button4_Click(object sender, EventArgs e)
    {
        con = new SqlConnection("Data Source=.\\SQLEXPRESS;AttachDbFilename=C:\\Users\\Shubhdip\\Downloads\\job portal\\job portal\\Jobadda\\App_Data\\JobportalDB.mdf;Integrated Security=True;User Instance=True");
        con.Open();

         int cid=0;
         cid = Convert.ToInt32(Session["Cid"].ToString());

         int rws = 0;
         rws = Convert.ToInt32(GridView1.Rows.Count.ToString());
          // Label22.Text = GridView1.Rows.Count.ToString();

        for (int i = 0; i < rws ; i++)
         {
             CheckBox chk = (CheckBox)GridView1.Rows[i].FindControl("CheckBox3");

             if (chk.Checked == true)
             {
                 String a = GridView1.Rows[i].Cells[2].Text;
                 string query = "insert into Saved_jobs (candidate_id,jobpost_id) values (" + cid + "," + a + ")";
                 cmd = new SqlCommand(query, con);
                 cmd.ExecuteNonQuery();
             }
         }
    }

    protected void btnOK_Click(object sender, EventArgs e)
    {
        string ID;
        SqlConnection mycon = new SqlConnection(ConfigurationManager.AppSettings["conStr"]);
        mycon.Open();
        DataSet mydataset = new DataSet();
        SqlDataAdapter mydataadapter = new SqlDataAdapter("select * from tb_Blog where UserName='" + Session["UserName"] + "'", mycon);
        mydataadapter.Fill(mydataset, "tb_Blog");
        DataRowView rowview = mydataset.Tables["tb_Blog"].DefaultView[0];
        ID = rowview["BlogID"].ToString();

        string P_str_Com = "Insert into tb_Message(FriendName,Sex,HomePhone,MobilePhone,QQ,ICQ,Address,Birthday,Email,PostCode,BlogID,IP)"
            +" values ('"+this.txtName.Text+"','"+this.DropDownList1.SelectedValue+"','"+this.txtHphone.Text+"'"
        +",'"+this.txtMphone.Text+"','"+this.txtQQ.Text+"','"+this.txtICQ.Text+"','"+this.txtAddress.Text+"'"
        +",'"+this.txtBirthday.Text+"','"+this.txtEmail.Text+"','"+this.txtPostCode.Text+"','"+ID+"','"+Request.UserHostAddress+"')";
        SqlData da = new SqlData();
        if (!ValidateDate1(txtBirthday.Text) && !ValidateDate2(txtBirthday.Text) && !ValidateDate3(txtBirthday.Text))
        {
            Response.Write("<script language=javascript>alert('输入的日期格式有误！');location='javascript:history.go(-1)'</script>");
        }
        else
        {
            bool add = da.ExceSQL(P_str_Com);
            if (add == true)
            {
                Response.Write("<script language=javascript>alert('添加成功!');location='AddLinkMan.aspx'</script>");
            }
            else
            {
                Response.Write("<script language=javascript>alert('添加失败！');location='javascript:history.go(-1)'</script>");
            }
        }
    }

    protected void Button1_Click(object sender, EventArgs e)
    {
        SqlTransaction myTransaction = null;
        {
            try
            {
                SqlConnection conn = new SqlConnection(@"Data Source=ajjpsqlserverdb.db.4338448.hostedresource.com; database=ajjpsqlserverdb;
                                                            User ID=ajjpsqlserverdb; Password= Devry2010;");

                conn.Open();
                SqlCommand command = conn.CreateCommand();
                string strSQL;
                string txtBoxText = TextBox1.Text;
                txtBoxText = txtBoxText.Replace("'", "''");

                myTransaction = conn.BeginTransaction();
                command.Transaction = myTransaction;

                strSQL = "UPDATE aspnet_Membership SET Password = '" + txtBoxText + "' WHERE UserID = '" + DropDownList1.SelectedValue + "'";

                command.CommandType = System.Data.CommandType.Text;
                command.CommandText = strSQL;
                command.ExecuteNonQuery();

                myTransaction.Commit();

                command.Connection.Close();
                Response.Redirect("~/User/Main.aspx");
            }
            catch (Exception ex)
            {
                lblErr.Text = ex.Message;
            }
        }
    }

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

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