C#learning materials

using System;
using System.Data;
using System.Data.OleDb;

using System.Windows.Forms;

namespace _4._0NewProperty
{
    public partial class Form2 : Form
    {
        public System.Windows.Forms.DataGrid datagrid1;
        public System.Windows.Forms.TextBox textbox1;
        public System.Windows.Forms.TextBox textbox2;
        public System.Windows.Forms.TextBox textbox3;
        public System.Windows.Forms.TextBox textbox4;
        public System.Windows.Forms.Button button2;
        public System.Windows.Forms.Label Label1;
        public System.Windows.Forms.BindingNavigator bindingNavigator1;
        private string str_connection = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\\Users\\Samsung\\Desktop\\my.accdb";
        private string str_query_adapter = "select * from [table]";
        public bool Button_click = false;

        public Form2()
        {
            InitializeComponent();
            //Mylabel("Label", "Name4", label1_Click, 150, 150, 75, 23);
            Mybutton("Display", "button2", Button_display_datagrid, 8, 670, 75, 25);
            Mybutton("Refresh", "button3", Button_datagrid_refesh, 100, 670, 75, 25);
            Mybutton("Add", "button4", Button_datagrid_Add, 190, 670, 75, 25);
            Mybutton("Del_Temp_all", "button5", Button_Del_All_Temp, 280, 670, 85, 25);
            Mybutton("Del_Selected", "button6", Button_Del_Selected, 380, 670, 85, 25);
            Mybutton("Modify_An_Row", "button1", Button_modify_datagrid, 480, 670, 93, 25);
            Mybutton("Update", "button7", Button_MemoryToTable, 1250, 670,75, 25);
            Mybutton("Del_An_Row", "button8", Button_Del_An_Row, 1100, 670, 93, 25);

            Mylabel("...", "Label1", Label_click_fuck, 8, 620, 80, 30);
            bindingNavigator("bindingNavigator1", 592, 25, 23, 22);

            Mytextbox("123456", "NameTextbox1",12, 591, 75, 23, 7);
            Mytextbox("chuanzhi", "NameTextbox2", 147, 591, 75, 23, 8);
            Mytextbox("chuanzhi", "NameTextbox3", 294, 591, 75, 23, 9);
            Mytextbox("chuanzhi", "NameTextbox4", 463, 591, 75, 23, 10);
            MyDataGrid("My_DataGrid", 2, 12, 586, 550, Duoble_Click_Cell);
            //MyDataGridView("My_DataGridView", 2, 12, 586, 150);
        }
        /// <summary>
        /// 动态生成Textbox控件
        /// </summary>
        /// <returns>null</returns>
        private string Mytextbox(string str, string name, int x, int y, int z, int o, int index)
        {
            TextBox tb = new TextBox();
            tb.Location = new Point(x, y);
            tb.Size = new Size(z, o);
            tb.Text = str;
            tb.TabIndex = index;
            tb.Name = name;
            this.Controls.Add(tb);
            return name;
        }
        /// <summary>
        /// 动态生成Button控件
        /// </summary>
        /// <returns>null</returns>
        private void Mybutton(string str, string name, EventHandler my_event, int x, int y, int z, int o)
        {
            Button bt_close = new Button();
            //bt_close.Location = new Point(200, 200);
            bt_close.Text = str;
            bt_close.Location = new Point(x, y);
            bt_close.Size = new System.Drawing.Size(z, o);
            bt_close.Name = name;
            this.Controls.Add(bt_close);
            bt_close.Click += new EventHandler(my_event);
        }
        /// <summary>
        /// 动态生成Label控件
        /// </summary>
        /// <returns>null</returns>
        private void Mylabel(string str, string name, EventHandler my_event, int x, int y, int z, int o)
        {
            Label label1 = new Label();
            label1.Location = new Point(x, y);
            label1.Size = new System.Drawing.Size(z, o);
            label1.Text = str;
            label1.Name = name;
            this.Controls.Add(label1);
            label1.Click += new System.EventHandler(my_event);
        }

        /// <summary>
        /// 动态生成bindingNavigator控件
        /// </summary>
        /// <returns>null</returns>
        private void bindingNavigator(string name, int x, int y, int z, int o)
        {
            BindingNavigator bng = new BindingNavigator();
            bng.Location = new Point(x,y);
            bng.Name = name;
            bng.Size = new System.Drawing.Size(z, o);
            this.Controls.Add(bng);
        }
        /// <summary>
        /// 动态生成DataGrid控件
        /// </summary>
        /// <returns>null</returns>
        private void MyDataGrid(string name,int x,int y,int z,int o,EventHandler myevent)
        {
            DataGrid dgd = new DataGrid();
            dgd.Location = new Point(x,y);
            dgd.Size = new System.Drawing.Size(z,o);
            dgd.Name = name;
            dgd.DoubleClick += new System.EventHandler(myevent);
            this.Controls.Add(dgd);
        }
        /// <summary>
        /// 动态生成DataGridView控件
        /// </summary>
        /// <returns>null</returns>
        private void MyDataGridView(string name, int x, int y, int z, int o)
        {
            DataGridView dgv = new DataGridView();
            dgv.Name = name;
            dgv.Location = new Point(x, y);
            dgv.Size = new System.Drawing.Size(z,o);
            this.Controls.Add(dgv);
        }
        /// <summary>
        /// 通过界面的Textbox向数据库表中添加字段源数据。
        /// </summary>
        /// <returns>OK:0   NOK:1</returns>
        public int DataBase_Add_Field(string str_connection,string str_query,string str_temp_table,string str_t,string str_t1,string str_t2,string str_t3)
        {
            OleDbConnection oledbcon = new OleDbConnection(str_connection);
            OleDbDataAdapter oledbdap = new OleDbDataAdapter(str_query_adapter, oledbcon);
            DataSet ds = new DataSet();
            oledbdap.Fill(ds, str_temp_table);

            DataRow dr = ds.Tables[str_temp_table].NewRow();
            dr["position"] = str_t;
            dr["name"] = str_t1;
            dr["sex"] = str_t2;
            dr["age"] = str_t3;

            ds.Tables[str_temp_table].Rows.Add(dr);
            OleDbCommandBuilder oledbcb = new OleDbCommandBuilder(oledbdap);
            oledbcb.QuotePrefix = "[";                                       //Access 数据库需要使用这样的属性；sql server是不是需要设置这两个属性的
            oledbcb.QuoteSuffix = "]";                                       //Access 数据库需要使用这样的属性；sql server是不是需要设置这两个属性的
            if (ds.HasErrors) {
                ds.RejectChanges();
                return 1;
            }
            if (ds.HasChanges()) {
                oledbdap.Update(ds, str_temp_table);
                oledbdap.Dispose();
                return 0;
            }
            else { return 1;}
        }
        /// <summary>
        /// 向表中添加数据，全部是同一个数据
        /// </summary>
        /// <returns>OK:0   NOK:1</returns>
        public int TableName_Add(string str_con, string str_query, string newtablename, string add_data)
        {
            OleDbConnection oledbcon = new OleDbConnection(str_connection);
            OleDbDataAdapter oledbdap = new OleDbDataAdapter(str_query_adapter, oledbcon);
            DataSet ds = new DataSet();
            oledbdap.Fill(ds, newtablename);
            DataRow dr = ds.Tables[newtablename].NewRow();
            foreach (DataColumn co in ds.Tables[newtablename].Columns)
            {
                dr[co.ColumnName] = add_data;
            }
            ds.Tables[newtablename].Rows.Add(dr);
            OleDbCommandBuilder oledbcb = new OleDbCommandBuilder(oledbdap);
            oledbcb.QuotePrefix = "[";                                       //Access 数据库需要使用这样的属性；sql server是不是需要设置这两个属性的
            oledbcb.QuoteSuffix = "]";                                       //Access 数据库需要使用这样的属性；sql server是不是需要设置这两个属性的
            if (ds.HasErrors) {
                ds.RejectChanges();
                return 1; }
            if (ds.HasChanges()) {
                oledbdap.Update(ds, newtablename);
                oledbdap.Dispose();
                return 0;
            }
            else { return 1; }
        }
        /// <summary>
        /// 删除表中鼠标选中的行,若手动排序后再次删除选中的选项时，可能会误删除其他行！！！
        /// </summary>
        /// <returns>OK:0   NOK:1</returns>
        public int Del_Table_Field(string str_con, string str_query, string newtablename, int i)
        {
            OleDbDataAdapter oledbdap = new OleDbDataAdapter(str_query, str_con);
            DataSet ds = new DataSet();
            oledbdap.Fill(ds, newtablename);
            ds.Tables[newtablename].Rows[i].Delete(); //删除Student 表中第i个记录  
            OleDbCommandBuilder oledbcb = new OleDbCommandBuilder(oledbdap);
            oledbcb.QuotePrefix = "[";                                       //Access 数据库需要使用这样的属性；sql server是不是需要设置这两个属性的
            oledbcb.QuoteSuffix = "]";                                       //Access 数据库需要使用这样的属性；sql server是不是需要设置这两个属性的
            if (ds.HasErrors) {
                ds.RejectChanges();
                return 1;
            }
            if (ds.HasChanges()) {
                oledbdap.Update(ds, newtablename);
                oledbdap.Dispose();
                return 0;
            }
            else { return 1; }
        }

        /// <summary>
        /// 配合dataset中表设置主键而设计的方法
        /// </summary>
        /// <returns>null</returns
        private void AddAutoIncrementColumn(DataTable dt)
        {
            DataColumn column = new DataColumn();
            column.DataType = System.Type.GetType("System.Int32");
            column.AutoIncrement = true;
            column.AutoIncrementSeed = 1000000;
            column.AutoIncrementStep = 10000;
            // Add the column to a new DataTable.
            dt.Columns.Add(column);
        }
        /// <summary>
        /// 删除表中鼠标选中的行,此方法稳定不论怎么排序都不会影响删除！
        /// </summary>
        /// <returns>OK:0   NOK:1</returns>
        public int Del_Table_Field_Ok(string str_con, string str_query, string new_table_name, DataGrid datagrid, string master_key)
        {
            int x=0;
            x=datagrid.CurrentCell.RowNumber;
            object z = datagrid[x, 0];
            object[] findTheseVals = new object[1];
            findTheseVals[0] = z;

            OleDbDataAdapter oledbdap = new OleDbDataAdapter(str_query, str_con);
            //设置主键方法一
            oledbdap.MissingSchemaAction = MissingSchemaAction.AddWithKey;       //防止在复制表到dataset中时主键框架信息缺漏，加上后会在复制时把主键等等的框架信息添加到dataset中。
            OleDbCommandBuilder custCB = new OleDbCommandBuilder(oledbdap);
            custCB.QuotePrefix = "[";
            custCB.QuoteSuffix = "]";
            DataSet ds = new DataSet();
            oledbdap.Fill(ds, new_table_name);
            DataTable dt = (DataTable)ds.Tables[new_table_name];
            AddAutoIncrementColumn(dt);                                       //表中主键是自动编号时应该加上这句进行属性的设置。
            //设置主键方法二
            //DataColumn[] pKey = new DataColumn[1];
            //pKey[0] = ds.Tables[new_table_name].Columns[master_key];
            //ds.Tables[new_table_name].PrimaryKey = pKey;
            //设置主键方法三
            //oledbdap.FillSchema(ds, SchemaType.Source, new_table_name);

            //根据主键查找对应的行
            DataRow foundRow = dt.Rows.Find(findTheseVals);
           //DataRow foundRow = ds.Tables[new_table_name].Rows.Find(findTheseVals);
            if (foundRow != null) {
                foundRow.Delete();
                oledbdap.Update(dt);
                return 0;
            }
            else { return 1; }
        }
        /// <summary>
        /// 修改鼠标选中的行
        /// </summary>
        /// <returns>OK:0   NOK:1</returns>
        public int Modify_Table_Field(string str_con, string str_query, string newtablename, int i,string Field,string modify_data)
        {
            OleDbDataAdapter oledbdap = new OleDbDataAdapter(str_query, str_con);
            DataSet ds = new DataSet();
            oledbdap.Fill(ds, newtablename);
            ds.Tables[newtablename].Rows[i][Field] = modify_data;             //修改内存中表的第i个记录的"StudentName"字段的值为"田歌"
            OleDbCommandBuilder oledbcb = new OleDbCommandBuilder(oledbdap);
            oledbcb.QuotePrefix = "[";                                       //Access 数据库需要使用这样的属性；sql server是不是需要设置这两个属性的
            oledbcb.QuoteSuffix = "]";                                       //Access 数据库需要使用这样的属性；sql server是不是需要设置这两个属性的
            if (ds.HasErrors) {
                ds.RejectChanges();
                return 1;
            }
            if (ds.HasChanges()) {
                oledbdap.Update(ds, newtablename);
                oledbdap.Dispose();
                return 0;
            }
            else { return 1; }
        }
        /// <summary>
        /// 返回一个表的所有行的整型数
        /// </summary>
        /// <returns>ok:i</returns>
        ///
        public int Row_Count(string str_con, string str_query, string newtablename)
        {
            OleDbDataAdapter oledbdap = new OleDbDataAdapter(str_query, str_con);
            DataSet ds = new DataSet();
            oledbdap.Fill(ds, newtablename);
            int i = ds.Tables[0].Rows.Count;
            return i;
        }
        /// <summary>
        ///返回datagrid的单元格的数据，及其表的主键字段名称，及其所在列的字段名称。
        /// </summary>
        /// <returns>string</returns>
        public string Get_Cell_Data(string str_query_adapter, string str_connection,string table_alias)
        {
            int r = 0, c = 0;
            OleDbDataAdapter oledbdap = new OleDbDataAdapter(str_query_adapter, str_connection);
            oledbdap.MissingSchemaAction = MissingSchemaAction.AddWithKey;   //让复制表时把主键关系也复制到dataset中。
            OleDbCommandBuilder custCB = new OleDbCommandBuilder(oledbdap);
            DataSet ds = new DataSet();
            oledbdap.Fill(ds, table_alias);
            DataTable dt = (DataTable)ds.Tables[table_alias];
            AddAutoIncrementColumn(dt);                                     //表中主键是自动编号时应该加上这句进行属性的设置。
            r = datagrid1.CurrentRowIndex;
            c = datagrid1.CurrentCell.ColumnNumber;
            string content_return = r.ToString() + ",";
            content_return+=c.ToString()+";";
            content_return += dt.Rows[r][c].ToString() + ",";
            content_return += (ds.Tables["table_alias"].PrimaryKey)[0].ToString()+",";
            content_return += ds.Tables[table_alias].Columns[c].ColumnName;
            return content_return;
        }
        /// <summary>
        ///从界面中的datagrid中修改数据，更新到数据库中XXXXXXXXXXXXXXXXXXXXXX(yiliu)
        /// </summary>
        /// <returns>ok:0</returns>
        public void DataBase_MemoryToTable(string str_connection, string str_query,string new_table,DataGrid datagrid)
        {

        }
        /// <summary>
        /// 采用OleDbConnection和OleDbDataAdapter两个对象，刷新数据界面数据表datagrid
        /// </summary>
        /// <returns>null</returns>
        public void DatagridDisplay_Or_Refresh(string str_conn,string str_query,DataGrid datagrid)
        {
            OleDbConnection oledbcon = new OleDbConnection(str_conn);
            OleDbDataAdapter oledbdap = new OleDbDataAdapter(str_query, oledbcon);
            DataSet ds = new DataSet();
            oledbdap.Fill(ds, "table_alias");
            datagrid.DataSource = ds.Tables["table_alias"];
            if (oledbcon.State == ConnectionState.Open) {
                oledbcon.Close();
            }
        }
        /// <summary>
        /// 仅仅采用OleDbDataAdapter两个对象，刷新数据界面数据表datagrid
        /// </summary>
        /// <returns>null</returns>
        public void Repeate_DatagridDisplay_Or_Refresh(string str_conn, string str_query, DataGrid datagrid)
        {
            OleDbDataAdapter oledbdap = new OleDbDataAdapter(str_query, str_conn);
            DataSet ds = new DataSet();
            oledbdap.Fill(ds, "table_alias");
            datagrid.DataSource = ds.Tables["table_alias"];
        }
        /// <summary>
        /// 临时性清空datagrid中的数据
        /// </summary>
        /// <returns>null</returns>
        public void Datagrid_Del_All(DataGrid datagrid)
        {
            //((DataTable)datagrid1.DataSource).Clear();
            DataTable dt = (DataTable)datagrid.DataSource;
            dt.Rows.Clear();
        }
        /// <summary>
        /// 此方法仅只适合用于小程序，小数据量的访问操作时的选择。大数量连接访问会影响数据查询速率。
        /// </summary>
        ///
        public void command_connection_ExecuteReader(string str_conn, string str_query)
        {
            OleDbConnection olcon = new OleDbConnection(str_connection);
            OleDbCommand olcom = new OleDbCommand();
            olcom.CommandText = str_query;
            olcom.Connection = olcon;
            OleDbDataReader myreader;
            if (olcon.State == ConnectionState.Closed) {
                olcon.Open(); }
            //olcom.ExecuteNonQuery();  仅仅只用于执行SQL语句，并没有返回类型
            myreader = olcom.ExecuteReader();   //用于SQl执行，并读出sql中产生的数据，可以向外展示。
            while (myreader.Read()) {
                //MessageBox.Show(String.Format("{0}, {1}, {2}",myreader[0], myreader[1],myreader[2]));
                //textbox1.Text = myreader[0].ToString();
                //textbox2.Text = myreader[1].ToString();
                //textbox3.Text = myreader[2].ToString();
                //textbox4.Text = myreader[3].ToString();
                //MessageBox.Show(myreader[0].ToString() + "  , " + myreader[1].ToString() + "  , " + myreader[2].ToString() + " , " + myreader[3].ToString());
                string str_plus = " ";
                for (int i = 0; i < myreader.FieldCount; i++) {
                    str_plus += myreader[i].ToString() + ",";
                }
                MessageBox.Show(str_plus);
            }
            myreader.Close();           // Call Close when done reading.
            if (olcon.State == ConnectionState.Open) {
                olcon.Close();
            }
        }
        /// <summary>
        /// 窗体原的消息操作。
        /// </summary>
        /// <returns>return:0</returns>
        //WndProc(ref Message m)的用法很强大。
        private static bool Close_tag = false;
        protected override void WndProc(ref Message m)
        {
            const int WM_SYSCOMMAND = 0x0112;
            const int SC_CLOSE = 0xF060;   //禁止关闭按钮
            const int SC_MINIMIZE = 0xF020; //禁止最小化按钮

            const int SC_Restore_Revivification = 0xf120;   //禁止还原按钮   
            const int SC_MAX = 0xf030;  //禁止最大化按钮
            const int SC_DOUBLE_BAR = 0xf122;//禁止双击标题栏
            const int SC_DRAG = 0xF012, SC_RESTORE = 0xF010;//禁止拖拽标题栏还原窗体
            const int SC_X = 0xF063, SC_Y= 0xF093;//禁止双击标题栏关闭窗体
            if (m.Msg == WM_SYSCOMMAND && ((int)m.WParam == SC_MINIMIZE || (int)m.WParam == SC_CLOSE))  {
                //最小化到系统栏          
                this.Hide();
                Close_tag = true;
                return;
            }
            if (m.Msg == WM_SYSCOMMAND && ((int)m.WParam == SC_Restore_Revivification || (int)m.WParam == SC_DOUBLE_BAR)) {
                return;
            }
            base.WndProc(ref m);
        }

/// <summary>
/// 以下为事件处理代码
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
        private void Button_Del_All_Temp(object sender, EventArgs e)
        {
            if (Button_click == true)
            {
                Datagrid_Del_All(datagrid1);
                Button_click = false;
            }
            else { return; }
        }

        private void Button_Del_Selected(object sender,EventArgs e)
        {
            int x = datagrid1.CurrentCell.RowNumber;
            Del_Table_Field(str_connection, str_query_adapter, "newtablenames", x);
            MessageBox.Show("success to delate data!");
        }

        private void Button_MemoryToTable(object sender, EventArgs e)
        {
            string str_insert_sql = "insert into [table] (name,sex,age) values ('";
            str_insert_sql += textbox1.Text + "','";
            str_insert_sql += textbox2.Text + "','";
            str_insert_sql += textbox3.Text + "')";
            string str_delete_sql = "delete from [table] where age='";  //删除数据的sql书写格式："delete from [table] where age='18'";
            str_delete_sql += textbox4.Text + "'";


            string CellData_FieldNmae=Get_Cell_Data(str_query_adapter, str_connection, "table_alias");
            MessageBox.Show(CellData_FieldNmae);
        }

        private void Form2_Load(object sender, EventArgs e)
        {
            //datagrid操作
            datagrid1 = (DataGrid)this.Controls["My_DataGrid"];
            datagrid1.RowHeadersVisible = false;
            datagrid1.Anchor = AnchorStyles.Right | AnchorStyles.Left | AnchorStyles.Top;
            //初始化datagrid
            Button_click = true;
            DatagridDisplay_Or_Refresh(str_connection, str_query_adapter, datagrid1);
            //textbox操作
            textbox1 = (TextBox)this.Controls["NameTextbox1"];
            textbox2 = (TextBox)this.Controls["NameTextbox2"];
            textbox3 = (TextBox)this.Controls["NameTextbox3"];
            textbox4 = (TextBox)this.Controls["NameTextbox4"];
            button2 = (Button)this.Controls["button2"];
            Label1 = (Label)this.Controls["Label1"];
            bindingNavigator1 = (BindingNavigator)this.Controls["bindingNavigator1"];
            //form窗体基础设置
            this.WindowState = FormWindowState.Maximized;
            this.StartPosition = FormStartPosition.CenterScreen;
        }

        private void Button_modify_datagrid(object sender, EventArgs e)
        {
            int n = Row_Count(str_connection, str_query_adapter, "newtablename");
            for (int i = 0; i < n;i++ ) {
                Modify_Table_Field(str_connection, str_query_adapter, "newtablename", i, textbox3.Text, textbox4.Text);
            }
            if (Button_click == true) {
                Repeate_DatagridDisplay_Or_Refresh(str_connection, str_query_adapter, datagrid1);
            }
            MessageBox.Show("ok");
        }

        private void Duoble_Click_Cell(object sender, EventArgs e)
        {
            //MessageBox.Show("test");
        }

        private void Label_click_fuck(object sender,EventArgs e)
        {

        }

       private void Button_Del_An_Row(object sender,EventArgs e)
        {
           int n = Row_Count(str_connection, str_query_adapter, "test");
           if (n == 0) {
               MessageBox.Show("No data in table!");
               return;
           }
           int i = Del_Table_Field_Ok(str_connection, str_query_adapter, "test_table", datagrid1, "ID");
           if (i == 0) {
               if (Button_click == true) {
                   Repeate_DatagridDisplay_Or_Refresh(str_connection, str_query_adapter, datagrid1);
               }
               MessageBox.Show("success to delete data!");
           }
        }

        private void Button_datagrid_Add(object sender, EventArgs e)
        {
            //TableName_Add(str_connection, str_query_adapter, "newtablename", textbox1.Text);
            //MessageBox.Show("Success to add data !");
            DataBase_Add_Field(str_connection, str_query_adapter, "Mytable", textbox1.Text.Trim(), textbox2.Text.Trim(), textbox3.Text.Trim(), textbox4.Text.Trim());
            MessageBox.Show("Success to add data !");
            if (Button_click == true) {
                Repeate_DatagridDisplay_Or_Refresh(str_connection, str_query_adapter, datagrid1);
            }
        }

        private void Button_display_datagrid(object sender, EventArgs e)
        {
            Button_click = true;
            DatagridDisplay_Or_Refresh(str_connection, str_query_adapter, datagrid1);
        }

        private void Button_datagrid_refesh(object sender, EventArgs e)
        {
            if (Button_click == true)
            {
                Repeate_DatagridDisplay_Or_Refresh(str_connection, str_query_adapter, datagrid1);
                Label1.Text = "数据已刷新！";
                Label1.ForeColor = Color.Red;
            }
            else { return; }
        }
    }
}