关于ADO.NAT操作数据库相关

ADO操做数据库主要包括六个对象，Connection链接数据库，这个java一样，不同的是这个不需要显示的加载驱动，Command主要用来发送数据库命令，类似于java的satantment对象，DataReader逐条访问数据库，速度快，但需要一直保持数据库连接，DataSet，直接将数据库中的表加载进内存，可以不用一直连接数据库，dataTable数据库中的一张表，

也就是说，DataSet包括若干个DataTable，DataTable包括若干个DataReader。

***重要的是，连接sqlserver数据库的时候，一定将数据库的外部链接开启了：

1.进入sqlser配置管理器，右键这个协议打开

.

2.开启tcp/ip

3.修改ip3为数据库服务器地址，数据库在自己电脑就是127.0.0.1

4.修改ipall tcp为1433，然后重启sqlserver服务

 

下面是我写的一个数据库操作winform程序：

本意是可以操作多个表：

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Data.SqlClient;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;

namespace DataBaseDemo
{
    public partial class Form1 : Form
    {

        private string sqlStr;
        private SqlConnection con;
        private SqlCommand com;
        private SqlDataReader reader;
        private DataSet dataSet;
        private DataTable dataTable;
        private IDataAdapter dataAdapter;
        private Form smallForm;
        private string type;

        public Form1()
        {
            InitializeComponent();
        }

        private void button1_Click(object sender, EventArgs e)
        {
            //select
            this.listView1.Clear();
            this.com = new SqlCommand();
            if (this.con != null && this.con.State != ConnectionState.Closed)
            {
                this.com.Connection = this.con;
            }
            else
            {
                MessageBox.Show("select fail.");
                return; 
            }
            string sql = "select * from ["+this.textBox5.Text+"]";
            this.com.CommandType = CommandType.Text;
            this.com.CommandText = sql;
            this.reader = this.com.ExecuteReader();
            if (this.reader.HasRows)
            {

                this.listView1.BeginUpdate();

                ColumnHeader cch = new ColumnHeader();
                cch.TextAlign = HorizontalAlignment.Center;   //设置列的对齐方式 
                cch.Text = this.textBox5.Text + "表";  //设置列标题 
                cch.Width = this.listView1.Width / (this.reader.FieldCount + 1);    //设置列宽度 
                this.listView1.Columns.Add(cch);    //将列头添加到ListView控件。
                //添加列标题
                for (int i = 0; i < this.reader.FieldCount; i++)
                {
                    ColumnHeader ch = new ColumnHeader();
                    ch.TextAlign = HorizontalAlignment.Center;   //设置列的对齐方式 
                    ch.Text = this.reader.GetName(i);   //设置列标题 
                    ch.Width = this.listView1.Width / (this.reader.FieldCount + 1);    //设置列宽度 
                    this.listView1.Columns.Add(ch);    //将列头添加到ListView控件。 
                }
                while (this.reader.Read())
                {
                    ListViewItem lvi = new ListViewItem();
                    lvi.Text = DateTime.Now.Millisecond.ToString();
                    for (int i = 0; i < this.reader.FieldCount; i++)
                    {
                        lvi.SubItems.Add(this.reader.GetFieldValue<Object>(i).ToString());
                    }
                    this.listView1.Items.Add(lvi);
                }
                this.listView1.EndUpdate();
            }
            this.com.Clone();
            this.reader.Close();  
        }


        public delegate Button MyEventHandler();


        private void button2_Click(object sender, EventArgs e)
        {
            //获得选中行的列名，列值
            //Insert
            ShowSelectedLine();
            this.type = "insert";
            //插入操作
        }

        private void ShowSelectedLine()
        {
            ListViewItem lvi = this.listView1.FocusedItem;
            if (this.type != "insert")
            {
                if (lvi == null)
                {
                    MessageBox.Show("Don not selected.");
                    return;
                }
            }
            this.smallForm = new Form2();
            this.smallForm.MaximizeBox = false;
            this.smallForm.MinimizeBox = false;
            int x = 91;
            //添加一个按钮
            Button reset = new Button()
            {
                Width = 71,
                Height = 21,
                TextAlign = ContentAlignment.MiddleCenter,
                Text = "reset",
                Location = new Point(0, 0),
                Visible = true,
                Name = "reset",
            };
            this.smallForm.Controls.Add(reset);
            reset.Click += new EventHandler(Reset);
            Button submit = new Button()
            {
                Width = 71,
                Height = 21,
                TextAlign = ContentAlignment.MiddleCenter,
                Text = "Submit",
                Location = new Point(0, 26),
                Visible = true,
                Name = "submit",
            };
            this.smallForm.Controls.Add(submit);
            submit.Click += new EventHandler(Submit);
            foreach (var head in this.listView1.Columns)
            {
                //创建标签存储列头
                Label column = new Label();
                column.Width = 71;
                column.Height = 21;
                column.TextAlign = ContentAlignment.MiddleCenter;
                column.Text = head.ToString().Split(new char[] { ':' })[2];

                column.Location = new Point(x, 0);
                this.smallForm.Controls.Add(column);
                x += column.Width + 20;
            }
            x = 91;
            int i = 0;
            foreach (var item in lvi.SubItems)
            {
                //创建内容
                TextBox column = new TextBox();
                column.TextAlign = HorizontalAlignment.Center;
                column.Width = 71;
                column.Height = 21;
                column.Text = item.ToString().Split(new char[] { ':', '{', '}' })[2];
                column.Location = new Point(x, 26);
                column.Name = "TextBox"+i;
                this.smallForm.Controls.Add(column);
                x += column.Width + 20;
                i++;
            }
            this.smallForm.Show();
        }

        private void Reset(object sender, EventArgs e)
        {
            //重置
            foreach (var con in this.smallForm.Controls) {
                if (con.GetType() == typeof(TextBox))
                {
                    ((TextBox)con).Text = "";
                }
            }
        }

        private void Submit(object sender, EventArgs e)
        {
            //判断哪个按钮被按下，执行的是什么操作

            this.com = new SqlCommand();
            if (this.con != null)
            {
                this.com.Connection = this.con;
            }
            else
            {
                MessageBox.Show("Updata or delete or insert fail.");
                return;
            }
            string sql = "" ;
            if ("insert".Equals(this.type))
            {
                //获取输入框文本
                //表
                sql = "INSERT INTO [" + this.textBox5.Text + "] VALUES( ";
                //字段
                //string filed = "";
                //值
                //string value = "";
                foreach (var con in this.smallForm.Controls)
                {
                    if (con.GetType() == typeof(TextBox))
                    {
                        //获取name
                        //获取text
                        string name = ((TextBox)con).Name;
                        string value = ((TextBox)con).Text;
                        if (name != "TextBox0"&&name != "TextBox1")
                        {
                            //拼接列名
                            sql += "'"+value.TrimEnd() + "',";
                        }  
                    }
                }
                //拼接完成后,将最后的逗号换成）
                //MessageBox.Show(sql);
                sql = sql.TrimEnd(new char[] { ',' })+")";
                MessageBox.Show(sql);
               
            }
            if ("updata".Equals(this.type))
            {

            }
            if ("delete".Equals(this.type))
            {

                //获取输入框文本
                //表
                sql = "delete from [" + this.textBox5.Text + "] where id= ";
                //字段
                //string filed = "";
                //值
                //string value = "";
                foreach (var con in this.smallForm.Controls)
                {
                    if (con.GetType() == typeof(TextBox))
                    {
                        //获取name
                        //获取text
                        string name = ((TextBox)con).Name;
                        string value = ((TextBox)con).Text;
                        if (name == "TextBox1")
                        {
                            //拼接列名
                            sql += "'" + value.TrimEnd() + "'";
                        }
                    }
                }
                //拼接完成后,将最后的逗号换成）
                //MessageBox.Show(sql);
            }

            this.com.CommandType = CommandType.Text;
            this.com.CommandText = sql;
            int count = this.com.ExecuteNonQuery();
            if (count > 0)
            {
                MessageBox.Show("seccess.");
                this.smallForm.Close();
            }

            else
            {
                MessageBox.Show("fail.");
            }
        }

        private void button3_Click(object sender, EventArgs e)
        {
            //Updata
            ShowSelectedLine();
            this.type = "updata";
        }

        private void button4_Click(object sender, EventArgs e)
        {
            //Delete
            ShowSelectedLine();
            this.type = "delete";
        }

        private void button5_Click(object sender, EventArgs e)
        {
            //open
            if (this.textBox1.Text.Equals("")|| this.textBox2.Text.Equals("") || this.textBox3.Text.Equals("") ||
                this.textBox4.Text.Equals("") || this.textBox5.Text.Equals(""))
            {
                MessageBox.Show("open fail.");
                return;
            }
            try
            {
                if (this.sqlStr != "Server=" + this.textBox4.Text + ";DataBase=" + this.textBox1.Text + ";Uid=" + this.textBox2.Text + ";Pwd=" + this.textBox3.Text)
                {
                    
                    if (this.con != null)
                    {
                        this.con.Close();
                        this.con.Dispose();
                    }
                    this.sqlStr = "Server=" + this.textBox4.Text + ";DataBase=" + this.textBox1.Text + ";Uid=" + this.textBox2.Text + ";Pwd=" + this.textBox3.Text;
                    this.con = new SqlConnection(this.sqlStr);
                }
       
                if (con.State == ConnectionState.Open)
                {
                    MessageBox.Show("Open to  open success.");
                }
                if (con.State == ConnectionState.Closed)
                {
                    con.Open();
                    MessageBox.Show("Close to open success.");
                }
            }
            catch (Exception s)
            {
                MessageBox.Show(s.Message);
            }
        }

        private void button6_Click(object sender, EventArgs e)
        {
            //stop
            if (this.con == null) {
                MessageBox.Show("Connection stop success.");
            }
            if (this.con.State == ConnectionState.Closed)
            {
                MessageBox.Show("Connection stop success.");
            }
            if (this.con.State == ConnectionState.Open)
            {
                this.con.Close();
                MessageBox.Show("Connection stop success.");
            }
     
        }

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

        private void Form1_Load(object sender, EventArgs e)
        {
            this.listView1.View = View.Details;
        }

        private void listView1_ItemSelectionChanged(object sender, ListViewItemSelectionChangedEventArgs e)
        {
            //MessageBox.Show("listView1_ItemSelectionChanged");
        }

        private void insertToolStripMenuItem_Click(object sender, EventArgs e)
        {
            this.button2_Click(sender,e);
        }

        private void updataToolStripMenuItem_Click(object sender, EventArgs e)
        {

        }

        private void deleteToolStripMenuItem_Click(object sender, EventArgs e)
        {
            this.button4_Click(sender, e);
        }
    }
}