C# DB.GetCon方法代码示例

 public int SelectMyScore(string username, string gameID)
 {
     int myscore = 0;
     SqlConnection conn = new SqlConnection();
     string sqlstr = "select gamScore from GAME_RECORD where jobskyerID ='" + username + "' and gameID='" + gameID + "' ";
     DB db = new DB();
     conn = db.GetCon();
     conn.Open();
     SqlCommand cmd = new SqlCommand(sqlstr, conn);
     try
     {
         myscore=Int32.Parse(cmd.ExecuteScalar().ToString());
         return myscore;//获取个人纪录并返回
     }
     catch (Exception ex)
     {
         return 0;
     }
     finally
     {
         cmd.Dispose();
         conn.Close();
     }
     //return myscore;
 }

 public bool SelectScore(string username,string gameID){
     SqlConnection conn = new SqlConnection();
     string sqlstr = "select count(*) from GAME_RECORD where jobskyerID ='" + username + "' and gameID='" + gameID + "' ";
     DB db = new DB();
     conn = db.GetCon();
     conn.Open();
     SqlCommand cmd = new SqlCommand(sqlstr, conn);
     try
     {
         if (Int32.Parse( cmd.ExecuteScalar().ToString()) == 0)
         {
             return false;
         }
     }
     catch (Exception ex)
     {
         return false;
     }
     finally
     {
         cmd.Dispose();
         conn.Close();
     }
     return true;//本来就有记录，就返回true
 }

 public bool ScoreRank_Bind()///分数排行绑定数据
 {
     DB db = new DB();
     string scoreRankstr = "SELECT GAME_RECORD.jobskyerID,gamScore,jobName FROM GAME_RECORD,JOBSKYER WHERE GAME_RECORD.jobskyerID=JOBSKYER.jobskyerID ORDER BY gamScore desc";
     SqlConnection con = new SqlConnection();
     con = db.GetCon();
     con.Open();
     SqlCommand com = new SqlCommand(scoreRankstr, con);
     SqlDataReader dr = com.ExecuteReader();
     try
     {
         ScoreRank.DataSource = dr;
         ScoreRank.DataBind();
     }  
     catch(Exception ex)
     {
         return false;
     }  
     finally
     {
         com.Dispose();
         con.Close();
     }
     return true;
 }

    public bool ScoreRankList_Bind()///下拉列表绑定数据
    {
        DB db = new DB();
        string sql_game = "SELECT * FROM GAME ";
        SqlConnection con = new SqlConnection();
        con = db.GetCon();
        con.Open();
        SqlCommand cmd = new SqlCommand(sql_game, con);
        SqlDataReader sqlRead_Game = cmd.ExecuteReader();
        try
        {
            ScoreRankList.DataSource = sqlRead_Game;
            ScoreRankList.DataTextField = "gamName";
            ScoreRankList.DataValueField = "gameID";
            ScoreRankList.DataBind();
            ScoreRankList.Items.Add(new ListItem("请选择","-1"));
            ScoreRankList.SelectedValue = "-1";

        }
        catch (Exception ex)
        {
            return false;
        }
        finally
        {
            cmd.Dispose();
            con.Close();
        }
        return true;
    }

 public bool Insert_DutyTime()///如果选择了没有安排过值班的人，就插入值班安排数据
 {
     string dutyInTime = chooseWeekday.SelectedValue + chooseSignInTime.SelectedValue;
     string dutyOutTime = chooseWeekday.SelectedValue + chooseSignOutTime.SelectedValue;
     string InsertStr = "INSERT INTO dutyTimeTable(dutyInTime,dutyOutTime,jobskyerID)VALUES(@dutyInTime,@dutyOutTime,@jobskyerID)";
     DB db = new DB();
     SqlConnection con = new SqlConnection();
     con = db.GetCon();
     con.Open();
     SqlCommand com = new SqlCommand(InsertStr, con);
     com.Parameters.Add("@dutyInTime", SqlDbType.DateTime, 0).Value = Convert.ToDateTime(dutyInTime);
     com.Parameters.Add("@dutyOutTime", SqlDbType.DateTime, 0).Value = Convert.ToDateTime(dutyOutTime);
     com.Parameters.Add("@jobskyerID", SqlDbType.NChar, 0).Value = chooseName.SelectedValue;
     try
     {
         com.ExecuteNonQuery();
         Sign_Result.Text = "安排值班成功！";
     }
     catch (Exception ex)
     {
         Sign_Result.Text = "安排值班失败！";
         return false;
     }
     finally
     {
         com.Dispose();
         con.Close();
     }
     return true;
 }

 protected void confirm_Click(object sender, EventArgs e)///首先判断该人员是否已经安排过值班
 {
     DB db = new DB();
     string str = "SELECT jobskyerID FROM dutyTimeTable";
     string jobskyerID = chooseName.SelectedValue;
     SqlConnection con = new SqlConnection();
     con = db.GetCon();
     SqlCommand com = new SqlCommand(str, con);
     SqlDataAdapter da = new SqlDataAdapter(com);
     DataSet ds = new DataSet();
     da.Fill(ds);
     ///SqlDataReader dr = com.ExecuteReader();
     int k = ds.Tables[0].Rows.Count;
     for (int i = 0; i < k; ++i)
     {
         if (jobskyerID == ds.Tables[0].Rows[i]["jobskyerID"].ToString())
         {
             Update_DutyTime();
         }
         if (i == k)
         {
             Insert_DutyTime();
         }
     }
 }

 public bool Update_DutyTime()///如果选择了安排过值班的人，就更新值班安排数据
 {
     DB db = new DB();
     string dutyInTime = chooseWeekday.SelectedValue + chooseSignInTime.SelectedValue;
     string dutyOutTime = chooseWeekday.SelectedValue + chooseSignOutTime.SelectedValue;
     string str = "UPDATE dutyTimeTable SET [email protected],[email protected] WHERE [email protected]";
     SqlConnection con = new SqlConnection();
     con = db.GetCon();
     SqlCommand com = new SqlCommand(str, con);
     com.Parameters.Add("@dutyInTime", SqlDbType.DateTime, 0).Value = dutyInTime;
     com.Parameters.Add("@dutyOutTime", SqlDbType.DateTime, 0).Value = dutyOutTime;
     com.Parameters.Add("@jobskyerID", SqlDbType.DateTime, 0).Value = chooseName.SelectedValue;
     try
     {
         com.ExecuteNonQuery();
         Sign_Result.Text = "更新值班成功！";
     }
     catch (Exception ex)
     {
         Sign_Result.Text = "更新值班失败！";
         return false;
     }
     finally
     {
         com.Dispose();
         con.Close();
     }
     return true;
 }

 public bool name_dropListBind()
 {
     try
     {
         DB db = new DB();
         string str = "SELECT dutyTimeTable.jobskyerID,jobName FROM dutyTimeTable,jobskyer WHERE dutyTimeTable.jobskyerID=jobskyer.jobskyerID";
         SqlConnection con = new SqlConnection();
         con = db.GetCon();
         con.Open();
         SqlCommand com = new SqlCommand(str, con);
         SqlDataReader dr = com.ExecuteReader();      
         name_dropList.DataSource = dr;
         name_dropList.DataTextField = "jobName";
         name_dropList.DataBind();
         name_dropList.Items.Add(new ListItem("请选择", "-1"));
         name_dropList.SelectedValue = "-1";
         com.Dispose();
         con.Close();
     }
     catch (Exception ex)
     {
         return false;
     }
     
     return true;
 }

 protected void SubmitAnnounce_Click(object sender, ImageClickEventArgs e)
 {
     try
     {
         DateTime now = DateTime.Now;
         DB db = new DB();
         SqlConnection con = db.GetCon();
         SqlCommand com = new SqlCommand();//using System.Data.SqlClient;
         con.Open();
         com.Connection = con;
         com.CommandType = CommandType.StoredProcedure;//using System.Data;
         com.CommandText = "SubmitAnnounce";
         com.Parameters.Add("@jobskyerID", SqlDbType.NVarChar);
         com.Parameters.Add("@notTitle", SqlDbType.NVarChar);
         com.Parameters.Add("@notContent", SqlDbType.NVarChar);
         com.Parameters.Add("@notTime", SqlDbType.DateTime);
         com.Parameters["@jobskyerID"].Value = Session["jobskyerID"].ToString();
         com.Parameters["@notTitle"].Value = AddAnnounceTitle.Text;
         com.Parameters["@notContent"].Value = AddContentText.Text;
         com.Parameters["@notTime"].Value = now;
         com.ExecuteNonQuery();
         con.Close();
         Response.Redirect("Announce.aspx");
     }
     catch(Exception ex)
     {
         Response.Write(ex.Message);
     }
 }

 public bool Address_list_Bind_Internet()
 {
     DB db = new DB();
     SqlConnection con = new SqlConnection();
     string Internet_sqlstr = "SELECT jobName, jobPhone  FROM jobskyer WHERE jobPosition = '网络组'";
     con = db.GetCon();
     con.Open();
     SqlCommand com_Internet = new SqlCommand(Internet_sqlstr, con);
     SqlDataReader dr_Internet = com_Internet.ExecuteReader();
     try
     {
         AddressList_Repeater_Internet.DataSource = dr_Internet;
         AddressList_Repeater_Internet.DataBind();
     }
     catch(Exception ex)
     {
         return false;
     }
     finally
     {
         com_Internet.Dispose();
         con.Close();
     }
     return true;
 }

        public void bind()
        {
            DB db = new DB();
            SqlConnection con = new SqlConnection();
            con = db.GetCon();
            string str = "select jobskyer.jobskyerID,jobskyer.jobName,myDutyTime.dutyInTime,myDutyTime.dutyOutTime,myDutyTime.flag0,myDutyTime.flag1 from jobskyer,myDutyTime where jobskyer.jobskyerID=myDutyTime.jobskyerID";
            SqlCommand com = new SqlCommand(str, con);
            SqlDataAdapter da = new SqlDataAdapter(com);
            DataSet ds = new DataSet();
            da.Fill(ds);
            int k = ds.Tables[0].Rows.Count;
            for (int i = 0; i < k; i++)
            {
                string tojobskyerID = ds.Tables[0].Rows[i]["toJobskyerID"].ToString();
                string select = "SELECT jobName FROM JOBSKYER WHERE jobskyerID='" + tojobskyerID + "'";
                DB db1 = new DB();
                SqlConnection con1 = new SqlConnection();
                con1 = db1.GetCon();
                string str1 = "select jobName from jobskyer where jobskyerID='" + tojobskyerID + "'";
                SqlCommand com1 = new SqlCommand(str1, con1);
                SqlDataAdapter da1 = new SqlDataAdapter(com1);
                DataSet ds1 = new DataSet();
                da1.Fill(ds1);
                string jobname = ds1.Tables[0].Rows[0]["jobName"].ToString();
                ds.Tables[0].Rows[i]["jobName"] = jobname;
                if (i == k)
                {
                    com1.Dispose();
                    con1.Close();
                }
            }
            try
            {
                DutyRecordGridView.DataSource = ds;
                DutyRecordGridView.DataKeyNames = new string[] { "jobskyerID" };
                DutyRecordGridView.DataBind();
            }
            catch (Exception ex)
            {
                Response.Write(ex.Message);
            }
            finally
            {
                com.Dispose();
                con.Close();
            }

        }

 protected void Page_Load(object sender, EventArgs e)
 {
     DB db = new DB();
     SqlConnection scon = new SqlConnection();
     scon = db.GetCon();
     scon.Open();
     if (scon.State == ConnectionState.Open)
     {
         Response.Write("<script>alert('数据库连接成功！');location.href='JsCommon/Login.aspx';</script>");
     }
     else
     {
         Response.Write("<script>alert('数据库连接失败！');location.href='JsCommon/Login.aspx';</script>");
     }
     scon.Close();
 }

 protected void Print()
 {
     //string a = "...";
     string str = "select noticeID,jobskyerID,jobName,notTitle,notContent,notTime from Notice,jobskyer where jobskyer.jobskyerID=Notice.jobskyerID orderby noticeID desc";
     DB db = new DB();
     SqlConnection con = new SqlConnection();
     con = db.GetCon();
     con.Open();
     SqlCommand com = new SqlCommand(str, con);
     SqlDataAdapter da = new SqlDataAdapter(com);
     DataSet ds = new DataSet();//using System.Data;
     da.Fill(ds);
     AnnounceRepeater.DataSource = ds;
     AnnounceRepeater.DataBind();
     con.Close();
 }

 public void DlistBind()    //下拉框数据绑定
 {
     DB db = new DB();
     SqlConnection myCon = db.GetCon();
     SqlCommand myCom = new SqlCommand();
     myCon.Open();
     myCom = new SqlCommand("select count(*) FROM myDutyTime WHERE jobskyerID='" + Session["jobskyerID"] + "'", myCon); //获得该组文件的总个数
     this.lbCount.Text = (Convert.ToInt32(myCom.ExecuteScalar())/4).ToString();  //算出总页数为DropdownList赋值
     int[] num = new int[Convert.ToInt32(lbCount.Text)];
     //Response.Write(lbCount.Text.ToString());
     //Response.Write(lbPage.Text.ToString());
     myCom.Dispose();
     myCon.Close();
     for (int i = 1; i <= Convert.ToInt32(lbCount.Text); i++)
     {
         num[i - 1] = i;
     }
     DropDownList1.DataSource = num;
     DropDownList1.DataBind();
 }

 protected void rptBind()        //repeater数据绑定
 {
     DB db = new DB();
     int n = 4 * (Convert.ToInt32(lbPage.Text) - 1);
     SqlConnection myCon = db.GetCon();
     myCon.Open();
     string sqlstr = "SELECT TOP 4 jobskyerID,dutyInTime,dutyOutTime,toJobskyerID,flag0,flag1 FROM myDutyTime WHERE jobskyerID='" + Session["jobskyerID"] + "' and jobskyerID not in (select top (@n) jobskyerID from FILES where jobskyerID='" + Session["jobskyerID"] + "')";
     SqlCommand mycom = new SqlCommand(sqlstr, myCon);
     mycom.Parameters.Add("n", n);
     SqlDataAdapter da = new SqlDataAdapter(mycom);
     DataSet ds = new DataSet();
     da.Fill(ds);
     ds.Dispose();
     da.Dispose();
     myCon.Close();
     if (ds != null)
     {
         this.Repeater1.DataSource = ds;
         this.Repeater1.DataBind();
     }
 }