Inheritance: System.Data.ProviderBase.AbstractDbCommandBuilder
Example #1
0
        private void Actualizar_Click(object sender, EventArgs e)
        {
            SqlConnection cnx = new SqlConnection("Data Source=ROMAN\\SQLEXPRESS;Initial Catalog=usuarios;Integrated Security=True");
            string datobuscado = textBox3.Text;
            string consulta = "Select *from producto";
            DataTable dt = new DataTable();

            SqlCommand cmd = new SqlCommand(consulta, cnx);
            cnx.Open();
            SqlDataReader lectura = cmd.ExecuteReader();

            dt.Load(lectura);
            dataGridView2.DataSource = dt;

            try {
                SqlDataAdapter cmd1 = new SqlDataAdapter("Select *from producto", cnx);
                SqlCommandBuilder c1 = new SqlCommandBuilder(cmd1);

                MessageBox.Show("Datos actualizados");
            }

            catch {

            }
        }
        /// <summary>
        /// Method load animal data conects to database using connection string
        /// </summary>
        /// <returns></returns>
        public DataTable LoadAnimalData()
        {
            try
            {
                using (mConnection = new SqlConnection(m_connString))
                {
                    //connect to database
                    //mConnection.ConnectionString =
                    //  ConfigurationManager.ConnectionStrings[m_connString].ConnectionString;

                    mConnection.Open();

                  string  sqlQuery = "SELECT * FROM tblAnimal";
                    mDataAdapterAnimals = new SqlDataAdapter(sqlQuery, mConnection);
                    mDataSet = new DataSet();
                    mDataAdapterAnimals.Fill(mDataSet, "tblAnimal");

                   SqlCommandBuilder sqlCommands = new SqlCommandBuilder(mDataAdapterAnimals);
                    DataTable tblData = mDataSet.Tables["tblAnimal"];

                    return tblData;
                }
            }
            catch (Exception)
            {

                throw;
            }
            finally
            {
                mConnection.Close();
            }
        }
Example #3
0
    protected void Submit_Click(object sender, EventArgs e)
    {
        string strConn = WebConfigurationManager.ConnectionStrings["dmtucaoConnectionString"].ConnectionString;
        SqlConnection con = new SqlConnection(strConn);
        con.Open();

        SqlDataAdapter da = new SqlDataAdapter("SELECT * FROM [User] WHERE [username]='" + Session["UserName"] + "'", con);
        SqlCommandBuilder scb = new SqlCommandBuilder(da);
        DataSet ds = new DataSet();
        da.Fill(ds, "user");

        if (ds.Tables["user"].Rows.Count > 0)
        {
            ds.Tables["user"].Rows[0]["face"] = FaceUrl.Text;
            ds.Tables["user"].Rows[0]["sex"] = Sex.SelectedIndex;
            ds.Tables["user"].Rows[0]["email"] = Email.Text;
            ds.Tables["user"].Rows[0]["qq"] = QQ.Text;
            ds.Tables["user"].Rows[0]["telephone"] = PhoneNumber.Text;
            ds.Tables["user"].Rows[0]["birthday"] = Birthday.Text;
            ds.Tables["user"].Rows[0]["introduction"] = Introduction.Text;
            if (da.Update(ds, "user") == 1)
                ClientScript.RegisterStartupScript(GetType(), "成功", "<script>alert('修改成功');location.reload();</script>");
        }
        con.Close();
        con = null;
        return;
    }
Example #4
0
 public DataTable SqlDataTable(string strname, string str, out DataSet ds, out SqlDataAdapter da)
 {
     try
     {
         conn.Open();
         da = new SqlDataAdapter(str, connstr);
         SqlCommandBuilder thisBuilder = new SqlCommandBuilder(da);
         ds = new DataSet();
         da.Fill(ds, strname);
         DataTable mytable = new DataTable();
         mytable = ds.Tables[0];
         return mytable;
     }
     catch (Exception ex)
     {
         da = null;
         ds = null;
         //MessageBox.Show(ex.Message);
         return null;
     }
     finally
     {
         conn.Close();
     }
 }
Example #5
0
 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;
 }
Example #6
0
        public void insert(customer_class cc)
        {
            SqlConnection con = new SqlConnection("Data Source=.\\sqlexpress;Initial Catalog=egas;Integrated Security=True;Pooling=False");
            SqlDataAdapter da = new SqlDataAdapter("select * from customer",con);
            DataSet ds = new DataSet();
            da.Fill(ds,"customer");
            DataRow dr = ds.Tables[0].NewRow();
            dr[0] = cc._id;
            dr[1] = cc._name;
            dr[2] = cc._father;
            dr[3] = cc._address;
            dr[4] = "empty";
            dr[5] = cc._email;
            dr[6] = cc._contact;
            dr[7] = cc._dob;
            dr[8] = cc._pin;
            dr[9] = cc._dist;
            dr[10] = cc._dealer_id;
            dr[11] = 0;
            dr[12] = 6; 
            dr[13] = "PENDING";
            dr[14] = DateTime.Today.ToString("dd/MM/yyyy") ;  


            ds.Tables[0].Rows.Add(dr);
            SqlCommandBuilder cb = new SqlCommandBuilder(da);
            da.Update(ds.Tables[0]);  
  
 
 
        }
Example #7
0
        private void FillDataSet()
        {
            try
            {
                SqlConnection connection = ServerConnector.Connection;
                SqlDataAdapter result = new SqlDataAdapter();
                SqlCommand command = new SqlCommand(@"
                    SELECT person_id as personId,
                           first_name as firstName,
                           last_name as lastName,
                           family_name as familyName,
                           gender as gender,
                           birth_date as birthDate,
                           death_date as deathDate,
                           comment as comment
                    FROM Persons
                    ");

                result.SelectCommand = command;
                result.SelectCommand.Connection = connection;
                SqlCommandBuilder builder = new SqlCommandBuilder(result);
                result.Fill(personsDs, "Persons");
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.ToString());
            }
        }
        protected void Button1_Click1(object sender, EventArgs e)
        {
            System.Threading.Thread.Sleep(3000);
            if (TextBox1.Text == "" && TextBox2.Text == "")
            {
                Label1.Text = "Invalid ID or Password";
            }
            else
            {
                Session["a"] = TextBox1.Text;

                ds = new DataSet();
                SqlDataAdapter ad = new SqlDataAdapter("select * from adm_info where username ='******' and pass='******'", con);
                SqlCommandBuilder cb = new SqlCommandBuilder(ad);
                ds.Clear();
                ad.Fill(ds);

                if (TextBox2.Text == "" || ds.Tables[0].Rows.Count == 0)
                {

                    Label1.Text = "Invalid ID or Password";

                }
                else
                {
                    //Response.Write(" <script >alert ('congrates') </script>");

                    Response.Redirect("default.aspx");
                    con.Close();

                }
                Label1.Text = "Invalid ID or Password";
            }
        }
Example #9
0
        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;
        }
Example #10
0
 /*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;
 }
Example #11
0
 /// <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;
 }
Example #12
0
        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();
            }
        }
Example #13
0
        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();
            }
        }
        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 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());
            }
        }
    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();
    }
Example #17
0
        protected void btnUpdate_Click(object sender, EventArgs e)
        {
             string cs = ConfigurationManager.ConnectionStrings["DBCS"].ConnectionString;
             using (SqlConnection con = new SqlConnection(cs)) 
             {
                 SqlDataAdapter da = new SqlDataAdapter();
                 da.SelectCommand = new SqlCommand((string)ViewState["SQL_QUERY"], con);

                 SqlCommandBuilder builder = new SqlCommandBuilder();
                 builder.DataAdapter = da;
                 
                 DataSet ds = (DataSet)ViewState["DATASET"];

                 if (ds.Tables["Students"].Rows.Count > 0) 
                 {
                     DataRow dr = ds.Tables["Students"].Rows[0];
                     dr["Name"] = txtStudentName.Text;
                     dr["Gender"] = ddlGender.SelectedValue;
                     dr["TotalMarks"] = txtTotalMarks.Text;
                 }
                 //this line not working saying command error.
                 int rowsupdated = da.Update(ds, "Students");
                 if(rowsupdated > 0)
                 {
                  lblStatus.ForeColor = System.Drawing.Color.Green;
                  lblStatus.Text = rowsupdated.ToString() + "rows(s) updated";
                 }
                 else
                 {
                     lblStatus.ForeColor = System.Drawing.Color.Red;
                     lblStatus.Text = " No rows(s) updated";

                 }
             }
        }
        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"];
            }
        }
        // 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();
        }
Example #20
0
        public static void Upload(this System.Data.DataTable dt, string tableName)
        {

            string query = "SELECT * from " + tableName;


            using (SqlConnection conn = new SqlConnection(SqlConnStr))
            {
                using (SqlDataAdapter oda = new SqlDataAdapter())
                {
                    using (SqlCommandBuilder bu = new SqlCommandBuilder())
                    {
                        using (SqlBulkCopy bulkcopy = new SqlBulkCopy(conn))
                        {
                            oda.SelectCommand = new SqlCommand(query, conn);
                            bulkcopy.DestinationTableName = "dbo." + tableName;
                            DataTable dtsql = new DataTable();
                            oda.Fill(dtsql);
                            List<DataRow> lst_temp = dt.AsEnumerable().ToList();
                            foreach (DataRow row in lst_temp)
                            {
                                dtsql.ImportRow(row);
                            }
                            conn.Open();

                            bulkcopy.WriteToServer(dtsql);
                            conn.Close();
                        }
                    }
                }
            }

        }
Example #21
0
 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;
 }
Example #22
0
File: Form1.cs Project: viticm/pap2
        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();
        }
Example #23
0
        private void ultraButton1_Click(object sender, EventArgs e)
        {
            DataSet ds = new DataSet("SottoConti");

            SqlConnection conn = new SqlConnection("Data Source=sfera.nadirweb.it;Database=Sfera;User ID=sa;Password=fanREA68;");
            SqlDataAdapter da = new SqlDataAdapter("SELECT * FROM SottoConti ORDER BY CodiceConto", conn);
            SqlCommandBuilder sqlCmb = new SqlCommandBuilder(da);
            da.Fill(ds, "SottoConti");

            DataTable tbSottoConti = ds.Tables[0];
            int oldCodiceConto = (int)tbSottoConti.Rows[0]["CodiceConto"];
            int index = 0;
            foreach (DataRow dr in tbSottoConti.Rows)
            {
                if (dr["CodiceSottoConto"] == DBNull.Value || dr["CodiceSottoConto"].ToString() == string.Empty)
                {
                    if ((int)dr["CodiceConto"] != oldCodiceConto)
                    {
                        index = 0;
                        oldCodiceConto = (int)dr["CodiceConto"];
                    }
                    index = index + 1;
                    dr["CodiceSottoConto"] = index.ToString().PadLeft(3,'0');
                }
            }

            da.Update(ds, "SottoConti");
        }
        private void connectToServer()
        {
            SqlConnection conn = Connection.getConnection();

            try
            {
                conn.Open();

                //先获取所有供应商信息到dataset去
                StringBuilder builder = new StringBuilder();
                builder.Append("SELECT supplier_id, supplier_name as 供应商名称, ");
                builder.Append("supplier_addr as 供应商地址, ");
                builder.Append("supplier_phone as 供应商电话 ");
                builder.Append("FROM tb_supplier");
                supplierDataAdapter = new SqlDataAdapter(builder.ToString(), conn);

                supplierDataAdapter.Fill(dataset, "supplier");
                dataTable = dataset.Tables["supplier"];

                SqlCommandBuilder cmdBuilder1 = new SqlCommandBuilder(supplierDataAdapter);
            }
            catch (Exception e)
            {
                MessageBox.Show("连接至服务器失败,请检查连接!错误:" + e.ToString(), "错误");
            }
            finally
            {
                conn.Close();
            }
        }
 protected void Add_Click(object sender, EventArgs e)
 {
     DateTime dt = DateTime.Now;
     SqlCommand cmd = new SqlCommand();
     cmd.CommandText = "select * from Company";
     cmd.Connection = con;
     SqlDataAdapter da = new SqlDataAdapter();
     da.SelectCommand = cmd;
     DataSet ds = new DataSet();
     da.Fill(ds, "Company");
     SqlCommandBuilder cb = new SqlCommandBuilder(da);
     DataRow drow = ds.Tables["Company"].NewRow();
     drow["User_Id"] = UserId.Text;
     drow["Category"] = Category.SelectedItem;
     drow["Person"] = ContactPerson.Text;
     drow["C_Name"] = CompanyName.Text;
     drow["Mobile_No"] = Mobile.Text;
     drow["Tagline"] = Tagline.Text;
     drow["Phone"] = Phone.Text;
     drow["Email_Id"] = Email.Text;
     drow["Website"] = Website.Text;
     drow["Date"] = dt.ToShortDateString();
     drow["Address"] = Address1.Text;
     drow["Shop"] = Shop.Text;
     drow["Gali"] = Gali.Text;
     ds.Tables["Company"].Rows.Add(drow);
     da.Update(ds, "Company");
     Panel1.Visible = false;
     Panel2.Visible = true;
     
 }
Example #26
0
        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");
                    }
                        
                }
            }
        }
Example #27
0
        //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;
            }
        }
Example #28
0
        private void Form1_Load(object sender, EventArgs e)
        {
            connectionString = "Data Source=(local);Initial Catalog=Practice-TrackingSystem;Integrated Security=SSPI;";
            dbConn = new SqlConnection(connectionString);

            projectsDA = new SqlDataAdapter("SELECT * FROM Project", dbConn);
            tasksDA = new SqlDataAdapter("SELECT * FROM Task", dbConn);

            tasksCB = new SqlCommandBuilder(tasksDA);

            dataSet = new DataSet();
            projectsDA.Fill(dataSet, "Project");
            tasksDA.Fill(dataSet, "Task");

            dataRelation = new DataRelation("Project_Tasks", dataSet.Tables["Project"].Columns["ProjID"],
                                                            dataSet.Tables["Task"].Columns["ProjID"]);
            dataSet.Relations.Add(dataRelation);

            projectsBS = new BindingSource();
            tasksBS = new BindingSource();

            projectsBS.DataSource = dataSet;
            projectsBS.DataMember = "Project";

            tasksBS.DataSource = projectsBS;
            tasksBS.DataMember = "Project_Tasks";

            projectsComboBox.DataSource = projectsBS;
            projectsComboBox.DisplayMember = "ProjName";

            tasksDataGridView.DataSource = tasksBS;
        }
Example #29
0
        public static void BuildCommandObjects(string conString, string cmdtxt, ref SqlCommand insertCmd, ref SqlCommand updateCmd, ref SqlCommand deleteCmd)
        {
            if ((conString == null) || (conString.Trim().Length == 0)) throw new ArgumentNullException( "conString" );
            if ((cmdtxt == null) || (cmdtxt.Length == 0)) throw new ArgumentNullException( "cmdtxt" );

            try
            {
                using (SqlConnection sqlConnection = new SqlConnection(conString))
                {
                    using (SqlDataAdapter dataAdapter = new SqlDataAdapter(cmdtxt, sqlConnection))
                    {
                        using (SqlCommandBuilder cmdBuilder = new SqlCommandBuilder(dataAdapter))
                        {
                            insertCmd = cmdBuilder.GetInsertCommand();
                            updateCmd = cmdBuilder.GetUpdateCommand();
                            deleteCmd = cmdBuilder.GetDeleteCommand();
                        }
                    }
                }
            }
            catch //(Exception ex)
            {
                throw;// new MyException(string.Format("Building command objects for table {0} failed", tableName), ex);
            }
        }
Example #30
0
        /// <summary>
        /// Permet de faire une requète de type UPDATE ou INSERT INTO
        /// </summary>
        /// <param name="rqt"> Requète</param>
        /// <param name="dt"> Database à modifier</param>
        /// <returns></returns>
        public int Update(string rqt, DataTable dt)
        {
            if (sqlConnect != null)
            {
                SqlTransaction trans = sqlConnect.BeginTransaction();
                SqlCommand sqlCmd = new SqlCommand(rqt, sqlConnect, trans);
                SqlDataAdapter sqlDA = new SqlDataAdapter(sqlCmd);
                SqlCommandBuilder build = new SqlCommandBuilder(sqlDA);
                sqlDA.UpdateCommand = build.GetUpdateCommand();
                sqlDA.InsertCommand = build.GetInsertCommand();
                sqlDA.DeleteCommand = build.GetDeleteCommand();

                sqlDA.MissingSchemaAction = MissingSchemaAction.AddWithKey;

                try
                {
                    int res = sqlDA.Update(dt);
                    trans.Commit();
                    return res;
                }
                catch (DBConcurrencyException)
                {
                    trans.Rollback();
                }
            }
            return 0;
        }
Example #31
0
        protected void Page_Load(object sender, System.EventArgs e)
        {
            System.Data.SqlClient.SqlCommandBuilder builder = new System.Data.SqlClient.SqlCommandBuilder(PhotosAdapter);

            if (Request.Params["Direction"] != null)
            {
                direction = Request.Params["Direction"].ToLower();
            }
            else
            {
                Response.Redirect(backlinkurl);
            }

            if ((direction != "up") && (direction != "down"))
            {
                Response.Redirect(backlinkurl);
            }

            object auctionidresult = null;

            using (SqlConnection connection = CommonFunctions.GetConnection()) {
                connection.Open();
                SqlCommand GetAuctionID = new SqlCommand("SELECT ID FROM Auctions WHERE PropertyID = @PropertyID", connection);
                GetAuctionID.Parameters.Add("@PropertyID", SqlDbType.Int);
                GetAuctionID.Parameters["@PropertyID"].Value = propertyid;

                auctionidresult = GetAuctionID.ExecuteScalar();
                connection.Close();
            }

            bool ifauction = (auctionidresult is int);

            if (ifauction)
            {
                Response.Redirect(CommonFunctions.PrepareURL("InternalError.aspx"));
            }

            if (Request.Params["PhotoID"] != null)
            {
                //string connectionstring = System.Configuration.ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;

                //CommonFunctions.Connection.ConnectionString = connectionstring;

                photoid = Convert.ToInt32(Request.Params["PhotoID"]);

                SinglePhotoAdapter.SelectCommand.Parameters["@PhotoID"].Value = photoid;

                if (CommonFunctions.SyncFill(SinglePhotoAdapter, PhotosSet) > 0)
                {
                    propertyid = (int)PhotosSet.Tables["PropertyPhotos"].Rows[0]["PropertyID"];

                    PropertiesAdapter.SelectCommand.Parameters["@PropertyID"].Value = propertyid;

                    if (CommonFunctions.SyncFill(PropertiesAdapter, PropertiesSet) > 0)
                    {
                        int uid = (int)PropertiesSet.Tables["Properties"].Rows[0]["UserID"];

                        if ((uid == AuthenticationManager.UserID) || AuthenticationManager.IfAdmin)
                        {
                            PhotosAdapter.SelectCommand.Parameters["@PropertyID"].Value = propertyid;

                            PhotosSet.Clear();
                            if (CommonFunctions.SyncFill(PhotosAdapter, PhotosSet) > 1)
                            {
                                System.Data.DataRow row1 = null;
                                System.Data.DataRow row2 = null;
                                int temp;

                                if (direction == "up")
                                {
                                    foreach (System.Data.DataRow datarow in PhotosSet.Tables["PropertyPhotos"].Rows)
                                    {
                                        if ((int)datarow["ID"] == photoid)
                                        {
                                            row2 = datarow;
                                            break;
                                        }
                                        else
                                        {
                                            row1 = datarow;
                                        }
                                    }
                                }
                                else
                                {
                                    bool ifnext = false;
                                    foreach (System.Data.DataRow datarow in PhotosSet.Tables["PropertyPhotos"].Rows)
                                    {
                                        if ((int)datarow["ID"] == photoid)
                                        {
                                            row1   = datarow;
                                            ifnext = true;
                                        }
                                        else if (ifnext)
                                        {
                                            row2 = datarow;
                                            break;
                                        }
                                    }
                                }

                                if ((row1 != null) && (row2 != null))
                                {
                                    temp = (int)row1["OrderNumber"];
                                    row1["OrderNumber"] = (int)row2["OrderNumber"];
                                    row2["OrderNumber"] = temp;

                                    //lock (CommonFunctions.Connection)
                                    PhotosAdapter.Update(PhotosSet);
                                }
                            }
                        }
                    }
                }
            }

            Response.Redirect(backlinkurl);
        }
    //protected System.Data.SqlClient.SqlConnection Connection;

    protected void Page_Load(object sender, System.EventArgs e)
    {
        System.Data.SqlClient.SqlCommandBuilder builder = new System.Data.SqlClient.SqlCommandBuilder(EmailsAdapter);

        //if (propertyid == -1)
        //    Response.Redirect (CommonFunctions.PrepareURL ("default.aspx"), true);

        //string connectionstring = System.Configuration.ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;

        //CommonFunctions.Connection.ConnectionString = connectionstring;

        PropertiesAdapter.SelectCommand.Parameters["@PropertyID"].Value = propertyid;
        PhotosAdapter.SelectCommand.Parameters["@PropertyID"].Value     = propertyid;

        //lock (CommonFunctions.Connection)
        if (PropertiesAdapter.Fill(PropertiesFullSet) < 1)
        {
            Response.Redirect(backlinkurl);
        }
        //lock (CommonFunctions.Connection)
        PhotosAdapter.Fill(PhotosSet);

        if (!(bool)PropertiesFullSet.Tables["Properties"].Rows[0]["IfApproved"] &&
            (!AuthenticationManager.IfAuthenticated ||
             ((AuthenticationManager.UserID != (int)PropertiesFullSet.Tables["Properties"].Rows[0]["UserID"]) &&
              !AuthenticationManager.IfAdmin)))
        {
            Response.Redirect(backlinkurl, true);
        }

        BackLink.NavigateUrl = CommonFunctions.PrepareURL("ViewProperty.aspx?PropertyID=" + propertyid.ToString(),
                                                          backlinktext);

        if (!IsPostBack)
        {
            DataBind();
        }

        //new link menu
        DBConnection obj = new DBConnection();

        try
        {
            string vNum = propertyid.ToString();

            //get city, state, country using property number

            DataTable dt = VADBCommander.CityStatePropertyInd(vNum);
            if (dt.Rows.Count > 0)
            {
                string vCity = dt.Rows[0]["city"].ToString();
                vNum = dt.Rows[0]["state"].ToString();

                dt = VADBCommander.StateProvinceNamedInd(vNum);
                string vState = dt.Rows[0]["state"].ToString();
                vNum = dt.Rows[0]["country"].ToString();

                dt = VADBCommander.CountryInd(vNum);
                string vCountry = dt.Rows[0]["country"].ToString();

                //lnkCountry.Text = vCountry;
                //lnkCountry.NavigateUrl = CommonFunctions.PrepareURL(vCountry + "/default.aspx");

                //lnkState.Text = vState;
                //lnkState.NavigateUrl = CommonFunctions.PrepareURL(vCountry + "/" + vState + "/default.aspx");

                //lnkCity.Text = vCity;
                //lnkCity.NavigateUrl = CommonFunctions.PrepareURL(vCountry + "/" + vState + "/" + vCity + "/default.aspx");

                lnkProperty.Text        = "Property #" + propertyid.ToString();
                lnkProperty.NavigateUrl = CommonFunctions.PrepareURL(vCountry + "/" + vState + "/" + vCity + "/" + propertyid + "/default.aspx");

                Session["emailReturnURL"] = CommonFunctions.PrepareURL(vCountry + "/" + vState + "/" + vCity + "/default.aspx");
            }
        }
        catch (Exception ex) { Response.Write("Error with response"); }
        finally { obj.CloseConnection(); }
    }
        /// <summary>
        /// 加载数据(返回DataTable)
        /// </summary>
        /// <param name="ConnStr">连接字符串</param>
        /// <param name="strSQL">SQL语句</param>
        /// <param name="strTableName">DataTable表名</param>
        /// <param name="isTable">是否对应有物理表,需要有更新、保存等命令</param>
        public virtual SD.DataTable GetData(string ConnStr, string strSQL, string strTableName, bool isTable)
        {
            SD.DataTable dt = new SD.DataTable(strTableName);

            try
            {
                using (sqlconn = new SDC.SqlConnection(ConnStr))
                {
                    //sqlconn.Open();

                    using (sqlcmd = new SDC.SqlCommand(strSQL, sqlconn))
                    {
                        //if (sqlcmd == null)
                        //{
                        //    using (dt = new SD.DataTable(strTableName))
                        //    {
                        //        return dt;
                        //    }
                        //}

                        sqlcmd.CommandTimeout = 7200;

                        using (sqladp = new SDC.SqlDataAdapter(sqlcmd))
                        {
                            if (isTable)
                            {
                                using (sqlcmdbd = new SDC.SqlCommandBuilder(sqladp))
                                {
                                    sqlcmdbd.ConflictOption = SD.ConflictOption.CompareAllSearchableValues;

                                    sqladp.InsertCommand = sqlcmdbd.GetInsertCommand();
                                    sqladp.UpdateCommand = sqlcmdbd.GetUpdateCommand();
                                    sqladp.DeleteCommand = sqlcmdbd.GetDeleteCommand();

                                    sqladp.Fill(dt);

                                    return(dt);
                                }
                            }
                            else
                            {
                                sqladp.Fill(dt);

                                return(dt);
                            }
                        }
                    }
                }
            }

            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                if (sqlconn != null)
                {
                    if (sqlconn.State != SD.ConnectionState.Closed)
                    {
                        sqlconn.Close();
                    }

                    sqlconn.Dispose();
                }
            }
        }
Example #34
0
        public void btnSaveDeliveries_Click(object sender, EventArgs e)
        {
            //if checking if the new delivery job created is from contracted or non contracted client . Admin can add only non contracted client
            if (txtboxContracted.Text == "n" && label1.Text == "ADMIN DELIVERIES")
            {
                DateTime startTime  = Convert.ToDateTime("08:30");
                DateTime endTime    = Convert.ToDateTime("16:30");
                DateTime cStartTime = Convert.ToDateTime(txtboxDeliverieHourStart.Text);
                DateTime cEndtTime  = Convert.ToDateTime(txtboxDeliveryHourEnd.Text);
                //another if making sure that the time that has been enetered is inside the working hours of the company 08:30-16:30
                if (cStartTime > startTime && cEndtTime < endTime)
                {
                    //if the time is correct creates the new record

                    DataRow OneRecord = dsCustomer.Tables["Deliveries"].NewRow();
                    OneRecord[0] = txtboxDeliverieID.Text;
                    OneRecord[1] = txtboxDeliverieHourStart.Text;
                    OneRecord[2] = txtboxDeliveryHourEnd.Text;
                    OneRecord[3] = txtboxDeliverieDayStart.Text;
                    OneRecord[4] = txtboxDeliverieDayEnd.Text;
                    OneRecord[5] = txtboxCourierID.Text;
                    OneRecord[6] = txtboxContracted.Text;



                    dsCustomer.Tables["Deliveries"].Rows.Add(OneRecord);


                    System.Data.SqlClient.SqlCommandBuilder myUpdateDB;
                    myUpdateDB = new System.Data.SqlClient.SqlCommandBuilder(daCustomer);
                    myUpdateDB.DataAdapter.Update(dsCustomer.Tables["Deliveries"]);

                    btnNextRecordDeliveries.Visible     = true;
                    btnPreviousRecordDeliveries.Visible = true;
                    btnSaveDeliveries.Visible           = false;
                    btnAddNewCourierJob.Visible         = true;
                    btnRefresh.Visible            = true;
                    btnCancelAdd.Visible          = false;
                    btnSaveEditDeliveryLC.Visible = false;
                    btnEditLCDeliveries.Visible   = false;
                    MessageBox.Show("New Record Has Been Added. Click on Refresh to refresh the DataBase");
                }
                else
                {
                    //mesage if the time entered is wrong
                    MessageBox.Show("The Time must be between 08:30 and 16:30");
                }
            }
            else if (txtboxContracted.Text == "y" && label1.Text == "LC DELIVERIES")
            {
                //the else if contracted job is added

                DateTime startTime  = Convert.ToDateTime("08:30");
                DateTime endTime    = Convert.ToDateTime("16:30");
                DateTime cStartTime = Convert.ToDateTime(txtboxDeliverieHourStart.Text);
                DateTime cEndtTime  = Convert.ToDateTime(txtboxDeliveryHourEnd.Text);

                //again checks about the time if its between 8:30-16:30 and then creates the record
                if (cStartTime > startTime && cEndtTime < endTime)
                {
                    DataRow OneRecord = dsCustomer.Tables["Deliveries"].NewRow();
                    OneRecord[0] = txtboxDeliverieID.Text;
                    OneRecord[1] = txtboxDeliverieHourStart.Text;
                    OneRecord[2] = txtboxDeliveryHourEnd.Text;
                    OneRecord[3] = txtboxDeliverieDayStart.Text;
                    OneRecord[4] = txtboxDeliverieDayEnd.Text;
                    OneRecord[5] = txtboxCourierID.Text;
                    OneRecord[6] = txtboxContracted.Text;



                    dsCustomer.Tables["Deliveries"].Rows.Add(OneRecord);


                    System.Data.SqlClient.SqlCommandBuilder myUpdateDB;
                    myUpdateDB = new System.Data.SqlClient.SqlCommandBuilder(daCustomer);
                    myUpdateDB.DataAdapter.Update(dsCustomer.Tables["Deliveries"]);

                    btnNextRecordDeliveries.Visible     = true;
                    btnPreviousRecordDeliveries.Visible = true;
                    btnSaveDeliveries.Visible           = false;
                    btnAddNewCourierJob.Visible         = true;
                    btnRefresh.Visible            = true;
                    btnCancelAdd.Visible          = false;
                    btnSaveEditDeliveryLC.Visible = true;
                    btnEditLCDeliveries.Visible   = true;
                    btnSaveEditDeliveryLC.Visible = false;
                    MessageBox.Show("New Record Has Been Added. Click on Refresh to refresh the DataBase");
                }
                else
                {
                    MessageBox.Show("Delivery times must be betwen 08:30 and 16:30");
                }
            }
            else
            {
                MessageBox.Show("You have no rights to perform this");
            }
        }
        private void toolStripButton1_Click(object sender, EventArgs e)
        {
            int count = 0;

            Guid[] warehouseZonesIds = new Guid[] { };
            if (this.comboBox1.SelectedIndex != -1)
            {
                warehouseZonesIds = new Guid[] { Guid.Parse(comboBox1.SelectedValue.ToString()) };
            }
            string msg = String.Empty;

            bool combine = BatchCombineCheck.Checked;

            this.dataGridView1.EndEdit();
            try
            {
                List <object> list = new List <object>();

                System.Data.SqlClient.SqlConnection oleConnection = new System.Data.SqlClient.SqlConnection(sql);
                oleConnection.Open();
                DataSet dsSql = new DataSet();
                System.Data.SqlClient.SqlDataAdapter oa = new System.Data.SqlClient.SqlDataAdapter("select * from StorageChecking", oleConnection);

                oa.Fill(dsSql);
                System.Data.SqlClient.SqlCommandBuilder scb = new System.Data.SqlClient.SqlCommandBuilder(oa);

                dtn = dsSql.Tables[0];
                DateTime date = DateTime.Now;
                int      j    = dtn.Rows.Count;
                if (dataGridView1.Rows.Count == 0)
                {
                    MessageBox.Show("尚未查询当前库存情况,记录为空!");
                    return;
                }
                var dtnGroup = from i in dtn.AsEnumerable()
                               group i by new { t1 = i.Field <string>("DocumentNum") } into g
                    select new
                {
                    DocNo = g.FirstOrDefault().Field <string>("DocumentNum").ToString()
                };
                if (dtnGroup != null)
                {
                    foreach (var item in dtnGroup.ToList())
                    {
                        if (item.DocNo.Contains(DocNum))
                        {
                            if (MessageBox.Show("本月已做过盘存操作,如果你需要重新盘存,本月原盘存记录将被清除!", "提示", MessageBoxButtons.OKCancel, MessageBoxIcon.Warning) == DialogResult.OK)
                            {
                                oleConnection = new System.Data.SqlClient.SqlConnection(sql);
                                oleConnection.Open();
                                System.Data.SqlClient.SqlCommand sqlCommand = new System.Data.SqlClient.SqlCommand();
                                sqlCommand.Connection  = oleConnection;
                                sqlCommand.CommandText = "delete from StorageChecking where DocumentNum LIKE  '%" + DocNum + "%'";
                                sqlCommand.ExecuteNonQuery();
                                MessageBox.Show("本月旧盘存记录清除完成!");
                                return;
                            }
                            else
                            {
                                dataGridView1.DataSource = null;
                                return;
                            }
                        }
                    }
                }


                foreach (DataGridViewRow r in this.dataGridView1.Rows)
                {
                    if (Convert.ToDecimal(r.Cells["colRealAmount"].Value) == 0)
                    {
                        count++;
                        r.Cells["colRealAmount"].Style.BackColor = Color.Red;
                    }
                    string   pName          = r.Cells[0].Value.ToString();
                    string   gg             = r.Cells[1].Value.ToString();
                    string   unit           = r.Cells[2].Value.ToString();
                    string   pNumber        = r.Cells[3].Value.ToString();
                    string   pFactory       = r.Cells[4].Value == null ? "无" : r.Cells[4].Value.ToString();
                    string   origin         = r.Cells[5].Value == null ? "无" : r.Cells[5].Value.ToString();
                    string   batchNumber    = r.Cells[6].Value == null ? "无" : r.Cells[6].Value.ToString();
                    DateTime pDate          = Convert.ToDateTime(r.Cells[7].Value);
                    DateTime validDate      = Convert.ToDateTime(r.Cells[8].Value);
                    decimal  purchasePrice  = Convert.ToDecimal(r.Cells[9].Value);
                    decimal  canUsed        = Convert.ToDecimal(r.Cells[10].Value);
                    decimal  currentIn      = Convert.ToDecimal(r.Cells[11].Value);
                    decimal  money          = Convert.ToDecimal(r.Cells[12].Value);
                    decimal  realAmount     = r.Cells[13].Value == null ? 0m : Convert.ToDecimal(r.Cells[13].Value);
                    decimal  dismaindAmount = canUsed - realAmount;
                    DateTime dtime          = DateTime.Now;
                    string   documentNum    = "PCD" + DocNum;
                    string   opuser         = AppClientContext.CurrentUser.Employee.Name;
                    string   wh             = r.Cells["Column3"].Value.ToString();

                    DataRow dr = dtn.NewRow();
                    dr.BeginEdit();
                    dr[0]  = j++;
                    dr[1]  = pName.ToString();
                    dr[2]  = gg;
                    dr[3]  = unit;
                    dr[4]  = pNumber;
                    dr[5]  = pFactory;
                    dr[6]  = origin;
                    dr[7]  = batchNumber;
                    dr[8]  = pDate;
                    dr[9]  = validDate;
                    dr[10] = purchasePrice;
                    dr[11] = canUsed;
                    dr[12] = money;
                    dr[13] = currentIn;
                    dr[14] = realAmount;
                    dr[15] = canUsed - realAmount;
                    dr[16] = dtime;
                    dr[17] = documentNum;
                    dr[18] = opuser;
                    dr[19] = wh;
                    dr.EndEdit();
                    dtn.Rows.Add(dr);
                }
                DataTable dty = new DataTable();
                if (count > 0)
                {
                    if (MessageBox.Show("有实盘数据为0,是否继续保存?", "提示", MessageBoxButtons.OKCancel, MessageBoxIcon.Warning) == DialogResult.OK)
                    {
                        oa.Update(dtn);
                        dsSql.AcceptChanges();
                        count = 0;
                        MessageBox.Show("保存成功,损溢数量将自动计算。\n\r请注意:若有药品损溢情况,请申报损溢并审批!");
                    }
                    else
                    {
                        return;
                    }
                }
                else
                {
                    oa.Update(dtn);
                    dsSql.AcceptChanges();
                    count = 0;
                    MessageBox.Show("保存成功,损溢数量将自动计算。\n\r请注意:若有药品损溢情况,请申报损溢并审批!");
                }

                oleConnection.Close();
            }
            catch (Exception ex)
            {
                MessageBox.Show("操作失败!");
            }
        }
Example #36
0
    protected void Page_Load(object sender, System.EventArgs e)
    {
        System.Data.SqlClient.SqlCommandBuilder builder  = new System.Data.SqlClient.SqlCommandBuilder(RegionsAdapter);
        System.Data.SqlClient.SqlCommandBuilder builder2 = new System.Data.SqlClient.SqlCommandBuilder(CountriesAdapter);
        System.Data.SqlClient.SqlCommandBuilder builder3 = new System.Data.SqlClient.SqlCommandBuilder(StateProvincesAdapter);
        System.Data.SqlClient.SqlCommandBuilder builder4 = new System.Data.SqlClient.SqlCommandBuilder(CitiesAdapter);

        userid = AuthenticationManager.UserID;

        //string connectionstring = System.Configuration.ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;

        //CommonFunctions.Connection.ConnectionString = connectionstring;

        //lock (CommonFunctions.Connection)
        RegionsAdapter.Fill(RegionsSet);

        if (!IsPostBack)
        {
            DataBind();

            RegionList_SelectedIndexChanged(RegionList, EventArgs.Empty);
        }
        else
        {
            try
            {
                if (Convert.ToInt32(RegionList.SelectedValue) != 0)
                {
                    CountriesAdapter.SelectCommand.Parameters["@RegionID"].Value = Convert.ToInt32(RegionList.SelectedValue);
                    //lock (CommonFunctions.Connection)
                    CountriesAdapter.Fill(CountriesSet);
                }
            }
            catch (Exception)
            {
            }
            try
            {
                if (Convert.ToInt32(CountryList.SelectedValue) != 0)
                {
                    StateProvincesAdapter.SelectCommand.Parameters["@CountryID"].Value = Convert.ToInt32(CountryList.SelectedValue);
                    //lock (CommonFunctions.Connection)
                    StateProvincesAdapter.Fill(StateProvincesSet);
                }
            }
            catch (Exception)
            {
            }
            try
            {
                if (Convert.ToInt32(StateList.SelectedValue) != 0)
                {
                    CitiesAdapter.SelectCommand.Parameters["@StateProvinceID"].Value = Convert.ToInt32(StateList.SelectedValue);
                    //lock (CommonFunctions.Connection)
                    CitiesAdapter.Fill(CitiesSet);
                }
            }
            catch (Exception)
            {
            }
        }
        Page.Header.Controls.Add(new LiteralControl("<link href='/css/StyleSheetBig4.css' rel='stylesheet' type='text/css'></script>"));
    }
Example #37
0
 public void UpdateDatabase(System.Data.DataSet ds)
 {
     System.Data.SqlClient.SqlCommandBuilder cb = new System.Data.SqlClient.SqlCommandBuilder(da_1);
     cb.DataAdapter.Update(ds.Tables["Planner"]);
 }
Example #38
0
        //protected System.Data.SqlClient.SqlConnection Connection;

        protected void Page_Load(object sender, System.EventArgs e)
        {
            System.Data.SqlClient.SqlCommandBuilder builder = new System.Data.SqlClient.SqlCommandBuilder(PhotosAdapter);

            if (propertyid != -1)
            {
                //string connectionstring = System.Configuration.ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;

                //CommonFunctions.Connection.ConnectionString = connectionstring;

                PropertiesAdapter.SelectCommand.Parameters["@PropertyID"].Value = propertyid;

                if (CommonFunctions.SyncFill(PropertiesAdapter, PropertiesSet) > 0)
                {
                    int uid = (int)PropertiesSet.Tables["Properties"].Rows[0]["UserID"];

                    if ((uid == AuthenticationManager.UserID) || AuthenticationManager.IfAdmin)
                    {
                        PhotosAdapter.SelectCommand.Parameters["@PropertyID"].Value = propertyid;

                        //lock (CommonFunctions.Connection)
                        PhotosAdapter.Fill(PhotosSet);

                        if ((bool)PropertiesSet.Tables["Properties"].Rows[0]["IfMoreThan7PhotosAllowed"] ||
                            (PhotosSet.Tables["PropertyPhotos"].Rows.Count < 7))
                        {
                            string filename;
                            string destname;
                            string extension;
                            int    i;

                            System.IO.Stream photostream = null;
                            string           oldfilename = "";

                            if (Request.Files.Count > 0)
                            {
                                photostream = Request.Files[0].InputStream;
                                oldfilename = Request.Files[0].FileName;
                            }
                            else if (Request.Params["PhotoLocation"] != null)
                            {
                                System.Net.WebClient webclient = new System.Net.WebClient();
                                byte[] buffer = webclient.DownloadData(Request.Params["PhotoLocation"]);
                                photostream = new System.IO.MemoryStream(buffer, 0, buffer.Length, false, true);
                                oldfilename = Request.Params["PhotoLocation"];
                            }
                            else
                            {
                                Response.Redirect(backlinkurl);
                            }

                            System.Drawing.Image photo = System.Drawing.Image.FromStream(photostream, true, true);
                            if ((photo.Width > 320) && ((double)photo.Width / (double)photo.Height >= 2.5))
                            {
                                if (photo.Width > 500)
                                {
                                    double ratio = 500 / (double)photo.Width;

                                    System.Drawing.Bitmap oThumbNail = new System.Drawing.Bitmap((int)(ratio * photo.Width),
                                                                                                 (int)(ratio * photo.Height));

                                    Graphics oGraphic = Graphics.FromImage(oThumbNail);

                                    oGraphic.CompositingQuality = System.Drawing.Drawing2D.CompositingQuality.HighQuality;

                                    oGraphic.SmoothingMode = System.Drawing.Drawing2D.SmoothingMode.HighQuality;

                                    oGraphic.InterpolationMode = System.Drawing.Drawing2D.InterpolationMode.HighQualityBicubic;

                                    Rectangle oRectangle = new Rectangle(0, 0, (int)(ratio * photo.Width),
                                                                         (int)(ratio * photo.Height));

                                    oGraphic.DrawImage(photo, oRectangle);

                                    photo = oThumbNail;
                                }
                            }
                            else if (Math.Max(photo.Width, photo.Height) > 320)
                            {
                                double ratio = 320 / (double)Math.Max(photo.Width, photo.Height);

                                System.Drawing.Bitmap oThumbNail = new System.Drawing.Bitmap((int)(ratio * photo.Width),
                                                                                             (int)(ratio * photo.Height));

                                Graphics oGraphic = Graphics.FromImage(oThumbNail);

                                oGraphic.CompositingQuality = System.Drawing.Drawing2D.CompositingQuality.HighQuality;

                                oGraphic.SmoothingMode = System.Drawing.Drawing2D.SmoothingMode.HighQuality;

                                oGraphic.InterpolationMode = System.Drawing.Drawing2D.InterpolationMode.HighQualityBicubic;

                                Rectangle oRectangle = new Rectangle(0, 0, (int)(ratio * photo.Width),
                                                                     (int)(ratio * photo.Height));

                                oGraphic.DrawImage(photo, oRectangle);

                                photo = oThumbNail;
                            }

                            extension = oldfilename.Substring(oldfilename.LastIndexOf(".") + 1);

                            if (!System.IO.Directory.Exists(Request.PhysicalApplicationPath +
                                                            System.Configuration.ConfigurationManager.AppSettings["ImagesSubfolderPath"]))
                            {
                                System.IO.Directory.CreateDirectory(Request.PhysicalApplicationPath +
                                                                    System.Configuration.ConfigurationManager.AppSettings["ImagesSubfolderPath"]);
                            }
                            for (i = 0; (i < 10000) &&
                                 System.IO.File.Exists(Request.PhysicalApplicationPath +
                                                       System.Configuration.ConfigurationManager.AppSettings["ImagesSubfolderPath"] + "property" +
                                                       propertyid.ToString("00000000") + "photo" + i.ToString("0000") + "." + extension); i++)
                            {
                                ;
                            }
                            if (i > 10000)
                            {
                                //TODO: add error handling here
                                Response.Redirect(backlinkurl);
                            }

                            destname = "property" + propertyid.ToString("00000000") + "photo" + i.ToString("0000") + "." + extension;
                            filename = Request.PhysicalApplicationPath +
                                       System.Configuration.ConfigurationManager.AppSettings["ImagesSubfolderPath"] + destname;

                            photo.Save(filename, System.Drawing.Imaging.ImageFormat.Jpeg);

                            //if (CommonFunctions.Connection.State == System.Data.ConnectionState.Closed)
                            //CommonFunctions.Connection.Open ();


                            object maxid;
                            int    newid;
                            int    newordernumber;

                            using (SqlConnection connection = CommonFunctions.GetConnection()) {
                                connection.Open();
                                System.Data.SqlClient.SqlCommand getmaxid =
                                    new System.Data.SqlClient.SqlCommand("SELECT MAX(ID) FROM PropertyPhotos", connection);
                                System.Data.SqlClient.SqlCommand getmaxordernumber =
                                    new System.Data.SqlClient.SqlCommand("SELECT MAX(OrderNumber) FROM PropertyPhotos", connection);


                                maxid = getmaxid.ExecuteScalar();
                                connection.Close();

                                if (maxid is DBNull)
                                {
                                    newid = 1;
                                }
                                else
                                {
                                    newid = (int)maxid + 1;
                                }

                                maxid = getmaxordernumber.ExecuteScalar();
                            }
                            if (maxid is DBNull)
                            {
                                newordernumber = 1;
                            }
                            else
                            {
                                newordernumber = (int)maxid + 1;
                            }

                            System.Data.DataRow newphoto = PhotosSet.Tables["PropertyPhotos"].NewRow();

                            newphoto["ID"]          = newid;
                            newphoto["PropertyID"]  = propertyid;
                            newphoto["FileName"]    = destname;
                            newphoto["Width"]       = photo.Width;
                            newphoto["Height"]      = photo.Height;
                            newphoto["OrderNumber"] = newordernumber;

                            PhotosSet.Tables["PropertyPhotos"].Rows.Add(newphoto);

                            //lock (CommonFunctions.Connection)
                            PhotosAdapter.Update(PhotosSet);
                        }
                    }
                }
            }

            Response.Redirect(backlinkurl);
        }
Example #39
0
 //allow forms to update the data adaptor with changes in the current dataset table - used for editing records in the main form
 public void updateDB(System.Data.DataSet dataSet)
 {
     System.Data.SqlClient.SqlCommandBuilder commandBuilder = new System.Data.SqlClient.SqlCommandBuilder(dataAdapt);
     commandBuilder.DataAdapter.Update(dataSet.Tables[0]);
 }