form1.cs

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

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Text;
using System.Windows.Forms;

using System.Data.SqlClient;

namespace DataApp
{
    public partial class Form1 : Form
    {
        public Form1()
        {
            InitializeComponent();
        }

        private void button1_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("CREATE TABLE myTable" + "(myId INTEGER CONSTRAINT PKeyMyId PRIMARY KEY," + "myName CHAR(50), myAddress CHAR(255), myBalance FLOAT)", conn);//创建一个表
                cmd.ExecuteNonQuery();//执行
                // 向表中添加记录
                cmd = new SqlCommand("INSERT INTO myTable(myId, myName,myAddress, myBalance) " + "VALUES (1001, 'Puneet Nehra', 'A 449 Sect 19, DELHI', 23.98 ) ", conn);
                cmd.ExecuteNonQuery();
                cmd = new SqlCommand("INSERT INTO myTable(myId, myName,myAddress, myBalance) " + "VALUES (1002, 'Anoop Singh', 'Lodi Road, DELHI', 353.64) ", conn);
                cmd.ExecuteNonQuery();
                cmd = new SqlCommand("INSERT INTO myTable(myId, myName, myAddress, myBalance) " + "VALUES (1003, 'Rakesh M', 'Nag Chowk, Jabalpur M.P.', 43.43) ", conn);
                cmd.ExecuteNonQuery();
                cmd = new SqlCommand("INSERT INTO myTable(myId, myName, myAddress, myBalance) " + "VALUES (1004, 'Madan Kesh', '4th Street, Lane 3, DELHI', 23.00) ", 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 button2_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("CREATE PROCEDURE myProc AS" + " SELECT myName, myAddress FROM myTable GO", 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 button3_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("CREATE VIEW myView AS SELECT myName FROM 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 button4_Click(object sender, EventArgs e)
        {
            SqlConnection conn = new SqlConnection("Integrated Security=SSPI;Initial Catalog=;Data Source=localhost;");//创建本地数据库的一个连接
            string sql = "CREATE DATABASE mydb ON PRIMARY" + "(name=test_data, filename ='C:\\mydb_data.mdf', size=3," + "maxsize=5, filegrowth=10%)log on" + "(name=mydbb_log,filename='C:\\mydb_log.ldf',size=3," + "maxsize=20,filegrowth=1)";//C:\\1目录要存在，否则会报错
            SqlCommand cmd = new SqlCommand(sql, conn);
            try
            {
                conn.Open();
                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 button5_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("DROP 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 button6_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("DROP PROCEDURE myProc", 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 button7_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("DROP VIEW myView", 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 button8_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 myTable", conn);// 创建数据适配器
                DataSet ds = new DataSet("myTable");// 创建一个数据集对象并填充数据，然后将数据显示在DataGridView控件中
                da.Fill(ds, "myTable");
                dataGridView1.DataSource = ds.Tables["myTable"].DefaultView;
                MessageBox.Show(this, "读取表数据成功。", "信息提示", MessageBoxButtons.OK, MessageBoxIcon.Information);
            }
            catch (Exception Mye)
            {
                MessageBox.Show(this, Mye.Message, "信息提示", MessageBoxButtons.OK, MessageBoxIcon.Error);
            }