C# SqlClient.SqlCommandBuilder类代码示例

    protected void Button2_Click(object sender, EventArgs e)
    {
        string qq = "Data Source=gz-20150728tajv\\sqlexpress;Initial Catalog=Student1;Integrated Security=True ";
        SqlConnection Conn = new SqlConnection(qq);
        SqlDataAdapter da = new SqlDataAdapter();
        string SQL = "select * from Hydropower";
        da.SelectCommand = new SqlCommand(SQL, Conn);
        DataSet ds = new DataSet();
        da.Fill(ds, "Hydropower");

        DataRow dr = ds.Tables["Hydropower"].NewRow();
        dr["寝室号"] = TextBox1.Text.ToString();
        dr["月份"] = TextBox9.Text.ToString();
        dr["度数"] = TextBox2.Text.ToString();
        dr["单价"] = TextBox3.Text.ToString();
        dr["总金额"] = TextBox4.Text.ToString();
        dr["是否交钱"] = TextBox5.Text.ToString();

        ds.Tables[0].Rows.Add(dr);
        SqlCommandBuilder read = new SqlCommandBuilder(da);
        da.Update(ds, "Hydropower");

        GridView1.DataSource = ds;
        GridView1.DataBind();
    }

        // The return type can be changed to IEnumerable, however to support
        // paging and sorting, the following parameters must be added:
        //     int maximumRows
        //     int startRowIndex
        //     out int totalRowCount
        //     string sortByExpression
        public IQueryable<Msts.Topics.Chapter11___LINQ.Lesson02___LINQ_to_SQL.JobsDataSet.jobsRow> gv_GetData()
        {
            // from here we bind the second gridview with a tyoped dataset

            var ds = new JobsDataSet();
            var cs = ConfigurationManager.ConnectionStrings["Msts"].ConnectionString;
            var conn = new SqlConnection(cs);
            var cmd = new SqlCommand("select * from jobs", conn) { CommandType = CommandType.Text };
            var sa = new SqlDataAdapter(cmd);
            var scb = new SqlCommandBuilder(sa);

            sa.Fill(ds, ds.jobs.TableName);

            var cmd2 = new SqlCommand("select * from employee", conn) { CommandType = CommandType.Text };
            var sa2 = new SqlDataAdapter(cmd2);
            var scb2 = new SqlCommandBuilder(sa2);

            sa2.Fill(ds, ds.employee.TableName);

            var data = from j in ds.jobs
                       join em in ds.employee
                       on j.job_id equals em.job_id
                       orderby j.job_desc, em.fname, em.lname
                       select j;

            this.msg.Text = data.Count().ToString();

            return data.AsQueryable();
        }

 private bool InitDBParams(object[] KeyValues)
 {
     if (m_Values == null)
     {
         m_Values = new object[3];
         //}
         //{
         string strsql = string.Format("select * from sys_lock_record where tablename='{0}' and fieldnames='{1}'", m_strTableName, m_strKeys);
         m_adp = new SqlDataAdapter(strsql, m_conn);
         m_adp.MissingSchemaAction = MissingSchemaAction.AddWithKey;
         m_ds = new DataSet();
         m_cmdBuilder = new SqlCommandBuilder(m_adp);
     }
     int nCount = 1;
     m_Values[0] = m_strTableName;
     m_Values[1] = m_strKeys;
     m_strValues = "";
     foreach (object obj in KeyValues)
     {
         m_strValues += obj.ToString() + ",";
         nCount++;
     }
     m_Values[2] = m_strValues;
     return true;
 }

 public DataSet GetData(string value)
 {
     connect.Open();
     switch (value)
     {
         case "GetAud": sel = "Select N_auditorii AS '№ аудитории ', Korpus AS 'Корпус', Vid_auditorii.Nazvanie AS'Тип аудитории', Podrazdelenie.Nazvanie AS 'Подразделение', Cpecializacia AS 'Специализация', Vmesimost AS 'Вместимость', Nalichie_proectora AS 'Наличие проектора', Ploschad AS 'Площадь', Visota AS 'Высота', Shirina AS 'Ширина', Dlina AS 'Длина' From Pasport_auditorii Left join Vid_auditorii ON Pasport_auditorii.ID_auditorii = Vid_auditorii.ID_auditorii  Left Join Podrazdelenie ON Pasport_auditorii.ID_podrazdelenia = Podrazdelenie.ID_podrazdelenia"; break;
         case "GetOb": sel = "Select N_auditorii AS '№ аудитории', Korpus AS 'Корпус', Inventarnii_nomer AS 'Инвентарный номер', Tip_oborudovania.Nazvanie AS 'Название', Stoimost_oborudovania AS 'Стоимость', chel.FIO AS 'Ответственное лицо',  Data_ustonovki AS 'Дата установки' , Data_spisania AS 'Дата списания' From Uchet_oborudovania Left Join Tip_oborudovania ON Uchet_oborudovania.ID_oborudovania = Tip_oborudovania.ID_oborudovania Left Join chel ON Uchet_oborudovania.Id_chela = chel.Id_chela"; break;
         case "GetRem": sel= "Select N_auditorii AS '№ аудитории', Korpus AS 'Корпус', N_smeti AS '№ сметы', Vid_remota.Nazvanie_remonta AS 'Вид ремонта', P_org.Название AS 'Подрядная организация', Stoimost AS 'Стоимость', Data_nachala AS 'Дата начала', Data_okonchania AS 'Дата окончания' From Plan_remonta Left Join Vid_remota ON Plan_remonta.ID_remonta = Vid_remota.ID_remonta Left join P_org ON Plan_remonta.Id_org = P_org.Id_org"; break;
         case "GetV_a": sel = "Select * From Vid_auditorii"; break;
         case "GetPodr": sel= "Select * From Podrazdelenie"; break;
         case "GetO_l": sel = "Select * From chel"; break;
         case "GetN_ob": sel = "Select * From Tip_oborudovania"; break;
         case "GetV_r": sel = "Select * From Vid_remota"; break;
         case "GetP_org": sel = "Select * From P_org"; break;
         case "Подразделение": sel = "SELECT Podrazdelenie.Nazvanie AS 'Название', SUM (Ploschad) AS 'Общая площадь', COUNT (*) AS 'Количество' FROM Pasport_auditorii, Podrazdelenie Where Pasport_auditorii.ID_podrazdelenia=Podrazdelenie.ID_podrazdelenia GROUP BY Podrazdelenie.Nazvanie";
             break;
         case "Оборудование": sel = "SELECT Tip_oborudovania.Nazvanie AS 'Название оборудования', COUNT (*) AS 'Количество', SUM (Stoimost_oborudovania) AS 'Общая стоимость' FROM Uchet_oborudovania, Tip_oborudovania Where Uchet_oborudovania.ID_oborudovania=Tip_oborudovania.ID_oborudovania GROUP BY Tip_oborudovania.Nazvanie";
             break;
         case "Ответственное лицо": sel = "SELECT Chel.FIO AS 'ФИО', COUNT (*) AS 'Количество аудиторий', SUM (kol) AS 'Колличество оборудования' FROM Uchet_oborudovania, chel  Where Uchet_oborudovania.Id_chela=chel.Id_chela GROUP BY Chel.FIO";
             break;
         case "Подрядная организация": sel = "SELECT P_org.Название AS 'Подрядная организация', COUNT (*) AS 'Количество аудиторий', SUM (Stoimost) AS 'Общая стоимость' FROM Plan_remonta, P_org  Where Plan_remonta.Id_org=P_org.Id_org GROUP BY P_org.Название";
             break;
    
     }
     add = new SqlDataAdapter(sel, connect);
     comb = new SqlCommandBuilder(add);
     add.Fill(ds);
     connect.Close();
     return ds;
 }

        protected void Page_Load(object sender, EventArgs e)
        {

            if (!this.IsPostBack)
            {
                using (con = new SqlConnection(ConfigurationManager.ConnectionStrings["conexion"].ConnectionString))
                {
                    try
                    {
                        con.Open();
                        adap = new SqlDataAdapter("select * from usuario", con);
                        build = new SqlCommandBuilder(adap);
                        adap.Fill(midata, "tabusu");

                    }
                    catch (SqlException ec)
                    {


                    }
                    finally
                    {
                        con.Close();
                        Session["midata"] = midata;
                    }

                }
            }else
            {
                midata = (DataSet)Session["midata"];
            }
        }

        private void button1_Click(object sender, EventArgs e)
        {
            String SQLQuery = @"SELECT [guid]
      ,[WCName]
      ,[MachineState]
      ,[StartTime]
      ,[EndTime]
      ,[LastModified]
  FROM [SFI_local_PC_SQL].[dbo].[tbl_slc_MachineStateHistory]
WHERE [EndTime] IS NULL";
            
            using (SqlConnection con = new SqlConnection("Data Source=" + Settings1.SQLConnectionString + ";Initial Catalog=SFI_local_PC_SQL;Integrated Security=True"))
            {
                con.Open();

                using (SqlDataAdapter da = new SqlDataAdapter(SQLQuery, con))
                {
                    using (SqlCommandBuilder cmdBuilder = new SqlCommandBuilder(da))
                    {
                        DataSet DataSet1 = new DataSet();
                        da.Fill(DataSet1, "tbl_slc_MachineStateHistory");
                        DataSet1.Tables["tbl_slc_MachineStateHistory"].Rows[0]["MachineState"] = 0;
                        da.Update(DataSet1, "tbl_slc_MachineStateHistory");
                    }
                        
                }
            }
        }

        private void button2_Click(object sender, EventArgs e) // Ìí¼Ó
        {
            UpdateGVData();

            string sql = string.Format("SELECT map, Model FROM npc WHERE RepresentID = {0}", m_RepresentID);
            DataTable tbl_trash = Helper.GetDataTable(sql, Conn);
            string strMap = tbl_trash.Rows[0]["map"].ToString().Trim();
            string strName = tbl_trash.Rows[0]["Model"].ToString().Trim();

            sql = string.Format("SELECT MAX(_index) FROM dic_npc_socket_desc");
            tbl_trash = Helper.GetDataTable(sql, Conn);
            int newIndex = tbl_trash.Rows[0][0] == DBNull.Value ? 1 : Convert.ToInt32(tbl_trash.Rows[0][0]) + 1;

            DataTable tbl = this.dataGridViewX1.DataSource as DataTable;
            DataRow row = tbl.NewRow();
            row["_index"] = newIndex;
            row["Map"] = strMap;
            row["RepresentID"] = m_RepresentID;
            row["Name"] = strName;
            row["Socket"] = m_Socket;
            row["FileName"] = this.textBox1.Text;
            tbl.Rows.Add(row);

            SqlCommandBuilder cmdBuilder = new SqlCommandBuilder(m_adp);
            m_adp.InsertCommand = cmdBuilder.GetInsertCommand();

            int val = m_adp.Update(tbl);
            tbl.AcceptChanges();
        }

        private void Form1_Load(object sender, EventArgs e)
        {
            connectionString = "Data Source=(local);Initial Catalog=Exam-UserRuns;Integrated Security=SSPI;";
            dbConn = new SqlConnection(connectionString);

            usersDA = new SqlDataAdapter("select * from Users", dbConn);
            runsDA = new SqlDataAdapter("select * from Runs", dbConn);

            runsCB = new SqlCommandBuilder(runsDA);

            dataSet = new DataSet();
            usersDA.Fill(dataSet, "Users");
            runsDA.Fill(dataSet, "Runs");

            dataRelation = new DataRelation("User_Runs", dataSet.Tables["Users"].Columns["userID"], dataSet.Tables["Runs"].Columns["userID"]);
            dataSet.Relations.Add(dataRelation);

            usersBS = new BindingSource();
            runsBS = new BindingSource();

            usersBS.DataSource = dataSet;
            usersBS.DataMember = "Users";

            runsBS.DataSource = usersBS;
            runsBS.DataMember = "User_Runs";

            usersComboBox.DataSource = usersBS;
            usersComboBox.DisplayMember = "userName";

            runsDataGridView.DataSource = runsBS;
        }

 /*USED IN THE USER CREATION PROCESS
   * FUNCTION         :This method create a new user in the DB
   * RETURN           :TRUE if the user is created, FALSE orthewise
   * MORE             :Before calling this method, the "login" of the new user
   *                  will be previously validate with the method "VALIDATELOGIN(string login) above
   **/
 public bool CreateNewUserCAD(UserClass user)
 {
     bool updated = false;
     DataSet bdvirtual = new DataSet();
     try
     {
         SqlDataAdapter da = new SqlDataAdapter("select *from UserTable", c);
         da.Fill(bdvirtual, "UserTable");
         DataTable tabla = new DataTable();
         tabla = bdvirtual.Tables["UserTable"];
         DataRow nuevaFila = tabla.NewRow();
         nuevaFila[0] = 2;
         nuevaFila[1] = user.FirstName;
         nuevaFila[2] = user.LastName;
         nuevaFila[3] = user.DateOfBirth;
         nuevaFila[4] = user.UserName;
         nuevaFila[5] = user.Password;
         nuevaFila[6] = user.Email;
         nuevaFila[7] = user.Country;
         tabla.Rows.Add(nuevaFila);
         SqlCommandBuilder cbuilder = new SqlCommandBuilder(da);
         da.Update(bdvirtual, "UserTable");
         updated = true;
     }
     catch (SqlException exp){updated = false;}
     finally{c.Close();}
     return updated;
 }

 /// <summary>
 /// 保存表修改到数据库
 /// </summary>
 /// <returns>保存的行数</returns>
 public int SaveTable()
 {
     int nRet = 0;
     if (mTableChanged || true)
     {
         try
         {
             mAdapter = new SqlDataAdapter(mSqlCmd, mConn);
             SqlCommandBuilder cmd = new SqlCommandBuilder(mAdapter);
             mAdapter.UpdateCommand = cmd.GetUpdateCommand();
             DataTable tbl = mTable.GetChanges();
             if (tbl != null && tbl.Rows.Count > 0)
             {
                 nRet = mAdapter.Update(tbl);
             }
             mTable.AcceptChanges();
             mTableChanged = false;
         }
         catch(Exception ex)
         {
             throw ex;
         }
     }
     return nRet;
 }

        private void connectToServer()
        {
            SqlConnection conn = Connection.getConnection();

            try
            {
                conn.Open();
                stuAdapter = new SqlDataAdapter("SELECT * from 学生账号", conn);
                teacherAdapter = new SqlDataAdapter("SELECT * from 教师账号", conn);
                adminAdapter = new SqlDataAdapter("SELECT * from 管理员账号", conn);

                stuAdapter.Fill(dataset, "student");
                teacherAdapter.Fill(dataset, "teacher");
                adminAdapter.Fill(dataset, "admin");

                SqlCommandBuilder cmdBuilder1 = new SqlCommandBuilder(stuAdapter);
                SqlCommandBuilder cmdBuilder2 = new SqlCommandBuilder(adminAdapter);
                SqlCommandBuilder cmdBuilder3 = new SqlCommandBuilder(teacherAdapter);

            }
            catch (Exception e)
            {
                MessageBox.Show("连接至服务器失败，请检查连接！错误：" + e.ToString(), "错误");
            }
            finally
            {
                conn.Close();
            }
        }

        protected void Button1_Click(object sender, EventArgs e)
        {
            string sqlcheck = "select * from vote where nic=" + Session["voter_nic"].ToString();
            ds = new DataSet();
            SqlDataAdapter ad = new SqlDataAdapter("select * from vote where nic =" + Session["voter_nic"], con);
            SqlCommandBuilder cb = new SqlCommandBuilder(ad);
            ds.Clear();
            ad.Fill(ds);

            if (ds.Tables[0].Rows.Count == 0)
            {

                Response.Redirect("vote_page.aspx");

            }
            else
            {
               // Response.Write(" <script >alert ('congrates') </script>");
                Label8.Text = "You Already Casted Your Vote !";
                // Response.Redirect("voter_home.aspx");
                // Response.Redirect("vote_page.aspx");
                // con.Close();

            }
        }

        private void button3_Click(object sender, EventArgs e)
        {
            y = this.year_comboBoxEx.SelectedItem.ToString();//要查看的日志的年份
            m = this.month_comboBoxEx.SelectedItem.ToString();//要查看的日志的月份
            SqlConnection connection = new SqlConnection("UID=sa;PWD=iti240;Database=kjqb;server=115.24.161.202;");
            string sqlstr = "select a.LAL_SIGNINTIME,a.LAL_LOG,b.LLC_COMMENT from LOG_T_ATTENCELOG a,LOG_T_LOGCOMMENT b where a.LAL_ID=b.LLC_LOGID and a.KU_ID=90021 and a.LAL_YEAR='" + int.Parse(y) + "' and a.LAL_MONTH='" + int.Parse(m) + "'";
            DataSet ds = new DataSet();
            SqlDataAdapter da = new SqlDataAdapter(sqlstr, connection);
            SqlCommandBuilder cb = new SqlCommandBuilder(da);
            da.Fill(ds);
            connection.Close();
            try
            {
                DataTable dt = ds.Tables[0];
                int b,i = 1;
                int a = dt.Rows.Count;
                for (b = 0; b < a; b++, i++)
                {
                    //将从数据库中查询的数据显示在listview中
                    string pubtime = dt.Rows[b][0].ToString();
                    string content = dt.Rows[b][1].ToString();
                    string pingjia = dt.Rows[b][2].ToString();
                    ListViewItem lvi = new ListViewItem();
                    lvi.Text = i.ToString();
                    lvi.SubItems.AddRange(new string[] { pubtime, content, pingjia });
                    this.listView3.Items.Add(lvi);
                }

            }
            finally
            {
                connection.Close();
            }
        }

        private void FillDataSet()
        {
            try
            {
                SqlConnection connection = ServerConnector.Connection;
                SqlDataAdapter result = new SqlDataAdapter();
                SqlCommand command = new SqlCommand(@"
                    SELECT relation_id as relationId,
                           name as name,
                           male_name as maleName,
                           female_name as femaleName,
                           max_rel_count as maxRelCount
                    FROM d_relations AS RelationsDefinition
                    ");

                result.SelectCommand = command;
                result.SelectCommand.Connection = connection;
                SqlCommandBuilder builder = new SqlCommandBuilder(result);
                result.Fill(relationsDefinitionDs, "RelationsDefinition");
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.ToString());
            }
        }

        //Obtiene un set de datos de db.
        static public DbResultSet GetDataTable(string selectCommand)
        {
            try
            {
                // Create a new data adapter based on the specified query.

                SqlConnection dbsession = DbManager.dbConnect();
                SqlDataAdapter dataAdapter = new SqlDataAdapter(selectCommand, dbsession);

                // Create a command builder to generate SQL update, insert, and
                // delete commands based on selectCommand. These are used to
                // update the database.
                SqlCommandBuilder commandBuilder = new SqlCommandBuilder(dataAdapter);

                // Populate a new data table and bind it to the BindingSource.
                DbResultSet rs = new DbResultSet();

                rs.dataTable.Locale = System.Globalization.CultureInfo.InvariantCulture;
                dataAdapter.Fill(rs.dataTable);
                return rs;

            }
            catch (Exception e)
            {
                MessageBox.Show(e.ToString());

                DbResultSet rs = new DbResultSet();
                rs.operationState = 1;
                return rs;
            }
        }