form1.cs

SQL数据库各种操作的C#源代码

            conn.Close();
        }

        private void button9_Click(object sender, EventArgs e)
        {
            SqlConnection conn = new SqlConnection("Data Source=" + textBox1.Text + ",1433;Network Library=DBMSSOCN;Initial Catalog=sq_psec;User ID=" + textBox2.Text + ";Password=" + textBox3.Text);//创建一个连接
            try
            {
                conn.Open();// 打开数据库连接
                SqlDataAdapter da = new SqlDataAdapter("myProc", conn);// 创建数据适配器
                DataSet ds = new DataSet("SP");// 创建一个数据集对象并填充数据，然后将数据显示在DataGridView控件中
                da.Fill(ds, "SP");
                dataGridView1.DataSource = ds.Tables["SP"].DefaultView;
                MessageBox.Show(this, "读取存贮过程数据成功。", "信息提示", MessageBoxButtons.OK, MessageBoxIcon.Information);
            }
            catch (Exception Mye)
            {
                MessageBox.Show(this, Mye.Message, "信息提示", MessageBoxButtons.OK, MessageBoxIcon.Error);
            }
            conn.Close();
        }

        private void button10_Click(object sender, EventArgs e)
        {
            SqlConnection conn = new SqlConnection("Data Source=" + textBox1.Text + ",1433;Network Library=DBMSSOCN;Initial Catalog=sq_psec;User ID=" + textBox2.Text + ";Password=" + textBox3.Text);//创建一个连接
            try
            {
                conn.Open();// 打开数据库连接
                SqlDataAdapter da = new SqlDataAdapter("SELECT * FROM myView", conn);// 创建数据适配器
                DataSet ds = new DataSet("myView");// 创建一个数据集对象并填充数据，然后将数据显示在DataGridView控件中
                da.Fill(ds, "myView");
                dataGridView1.DataSource = ds.Tables["myView"].DefaultView;
                MessageBox.Show(this, "读取视图数据成功。", "信息提示", MessageBoxButtons.OK, MessageBoxIcon.Information);
            }
            catch (Exception Mye)
            {
                MessageBox.Show(this, Mye.Message, "信息提示", MessageBoxButtons.OK, MessageBoxIcon.Error);
            }
            conn.Close();
        }

        private void button11_Click(object sender, EventArgs e)
        {
            SqlConnection conn = new SqlConnection("Data Source=" + textBox1.Text + ",1433;Network Library=DBMSSOCN;Initial Catalog=sq_psec;User ID=" + textBox2.Text + ";Password=" + textBox3.Text);//创建一个连接
            try
            {
                conn.Open();// 打开数据库连接
                SqlCommand cmd = new SqlCommand("ALTER TABLE myTable ADD newCol CHAR(50)", conn);//为表添加一个字段
                cmd.ExecuteNonQuery();//执行
                MessageBox.Show(this, "为表添加一个字段成功。", "信息提示", MessageBoxButtons.OK, MessageBoxIcon.Information);
            }
            catch (Exception Mye)
            {
                MessageBox.Show(this, Mye.Message, "信息提示", MessageBoxButtons.OK, MessageBoxIcon.Error);
            }
            conn.Close();
        }

        private void button12_Click(object sender, EventArgs e)
        {
            SqlConnection conn = new SqlConnection("Data Source=" + textBox1.Text + ",1433;Network Library=DBMSSOCN;Initial Catalog=sq_psec;User ID=" + textBox2.Text + ";Password=" + textBox3.Text);//创建一个连接
            try
            {
                conn.Open();// 打开数据库连接
                SqlCommand cmd = new SqlCommand("ALTER TABLE myTable DROP column newCol", conn);//为表添加一个字段
                cmd.ExecuteNonQuery();//执行
                MessageBox.Show(this, "为表删除一个字段成功。", "信息提示", MessageBoxButtons.OK, MessageBoxIcon.Information);
            }
            catch (Exception Mye)
            {
                MessageBox.Show(this, Mye.Message, "信息提示", MessageBoxButtons.OK, MessageBoxIcon.Error);
            }
            conn.Close();
        }

        private void button13_Click(object sender, EventArgs e)
        {
            SqlConnection conn = new SqlConnection("Data Source=" + textBox1.Text + ",1433;Network Library=DBMSSOCN;Initial Catalog=sq_psec;User ID=" + textBox2.Text + ";Password=" + textBox3.Text);//创建一个连接
            try
            {
                conn.Open();// 打开数据库连接
                SqlCommand cmd = new SqlCommand("TRUNCATE TABLE myTable", conn);//清空表所有行数据
                cmd.ExecuteNonQuery();//执行
                MessageBox.Show(this, "清空表所有行数据成功。", "信息提示", MessageBoxButtons.OK, MessageBoxIcon.Information);
            }
            catch (Exception Mye)
            {
                MessageBox.Show(this, Mye.Message, "信息提示", MessageBoxButtons.OK, MessageBoxIcon.Error);
            }
            conn.Close();
        }

        private void button14_Click(object sender, EventArgs e)
        {
            SqlConnection conn = new SqlConnection("Data Source=" + textBox1.Text + ",1433;Network Library=DBMSSOCN;Initial Catalog=sq_psec;User ID=" + textBox2.Text + ";Password=" + textBox3.Text);//创建一个连接
            try
            {
                conn.Open();// 打开数据库连接
                SqlCommand cmd = new SqlCommand("INSERT INTO myTable(myId, myName,myAddress, myBalance) " + "VALUES (9999, '中国', '重庆市渝北区', 636.64 ) ", conn);// 向表中添加记录
                cmd.ExecuteNonQuery();
                MessageBox.Show(this, "向表中添加行数据成功。", "信息提示", MessageBoxButtons.OK, MessageBoxIcon.Information);
            }
            catch (Exception Mye)
            {
                MessageBox.Show(this, Mye.Message, "信息提示", MessageBoxButtons.OK, MessageBoxIcon.Error);
            }
            conn.Close();
        }

        private void button15_Click(object sender, EventArgs e)
        {
            SqlConnection conn = new SqlConnection("Data Source=" + textBox1.Text + ",1433;Network Library=DBMSSOCN;Initial Catalog=sq_psec;User ID=" + textBox2.Text + ";Password=" + textBox3.Text);//创建一个连接
            try
            {
                conn.Open();// 打开数据库连接
                SqlCommand cmd = new SqlCommand("DELETE myTable WHERE myId=1002 OR myName='Madan Kesh'", conn);//删除表中符合条件的行数据
                cmd.ExecuteNonQuery();
                MessageBox.Show(this, "删除表中符合条件的行数据成功。", "信息提示", MessageBoxButtons.OK, MessageBoxIcon.Information);
            }
            catch (Exception Mye)
            {
                MessageBox.Show(this, Mye.Message, "信息提示", MessageBoxButtons.OK, MessageBoxIcon.Error);
            }
            conn.Close();
        }

        private void button16_Click(object sender, EventArgs e)
        {
            SqlConnection conn = new SqlConnection("Data Source=" + textBox1.Text + ",1433;Network Library=DBMSSOCN;Initial Catalog=sq_psec;User ID=" + textBox2.Text + ";Password=" + textBox3.Text);//创建一个连接
            try
            {
                conn.Open();// 打开数据库连接
                SqlCommand cmd = new SqlCommand("UPDATE myTable SET myName='程序员秘书' WHERE myId=1001", conn);//更新表中符合条件的行数据
                cmd.ExecuteNonQuery();
                MessageBox.Show(this, "更新表中符合条件的行数据成功。", "信息提示", MessageBoxButtons.OK, MessageBoxIcon.Information);
            }
            catch (Exception Mye)
            {
                MessageBox.Show(this, Mye.Message, "信息提示", MessageBoxButtons.OK, MessageBoxIcon.Error);
            }
            conn.Close();
        }

        private void button17_Click(object sender, EventArgs e)
        {
            SqlConnection conn = new SqlConnection("Data Source=" + textBox1.Text + ",1433;Network Library=DBMSSOCN;Initial Catalog=sq_psec;User ID=" + textBox2.Text + ";Password=" + textBox3.Text);//创建一个连接
            try
            {
                conn.Open();// 打开数据库连接
                SqlDataAdapter adapter = new SqlDataAdapter("SELECT * FROM myTable", conn);// 创建数据适配器;
                SqlCommandBuilder scb = new SqlCommandBuilder(adapter);//自动产生各种命令
                DataView dataview = (DataView)dataGridView1.DataSource;
                adapter.Update(dataview.Table.DataSet, "myTable");
                MessageBox.Show(this, "用dataGridView数据更新整个表数据成功。", "信息提示", MessageBoxButtons.OK, MessageBoxIcon.Information);
            }
            catch (Exception Mye)
            {
                MessageBox.Show(this, Mye.Message, "信息提示", MessageBoxButtons.OK, MessageBoxIcon.Error);
            }
            conn.Close();
        }
    }
}