C#操作数据库，DataSet，DataGridView，更新数据库 [一] - ADO.NET入门之中

<?xml version="1.0" encoding="utf-8" ?>
<configuration>
<connectionStrings>
<add name = "myconnstr"  connectionString="server=192.168.15.175;database=wentest;user id=sa;password=19831221" providerName="System.Data.SqlClient"/>
</connectionStrings>
</configuration>

app.config配置文件

添加using System.Configuration;引用

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

namespace datagridview
{
public partial class Form1 : Form
    {
private SqlConnection sconn;
private static String connstr = ConfigurationManager.ConnectionStrings["myconnstr"].ConnectionString;
//private SqlCommandBuilder scbder = null;
        private DataSet ds = null;
        SqlDataAdapter sap = null;
public Form1()
        {
            InitializeComponent();
        }

private void button1_Click(object sender, EventArgs e)
        {
//MessageBox.Show(connstr);

            sconn.Open();
//MessageBox.Show(sconn.State.ToString());
            try
            {
                sap = new SqlDataAdapter("select top 10 * from adonet", sconn);
                SqlCommandBuilder scbder = new SqlCommandBuilder(sap); //这里自动生成的命令，应是在sap这个适配器里,即每改一条或增加一条记录都动态写入sql语句.所以局部变量就行了
                ds = new DataSet();
//sap.FillSchema(ds,SchemaType.Source, "test");
                sap.Fill(ds, "test");
                dataGridView1.DataSource = ds.Tables["test"];

            }
catch (SqlException ee)
          {
              MessageBox.Show(ee.ToString());
          }
finally
            {
if (sconn.State == ConnectionState.Open)
                {
//MessageBox.Show(sconn.State.ToString());
//SqlDataAdapter不用sconn.open()，它会自动打开连接，并且不改变sconn的状态，即当调用adapter之前
//sconn为open状态，那么调用完成之后他的状态也是open。
//MessageBox.Show(sconn.State.ToString());
                    sconn.Close();
                }
            }            
        }

private void Form1_Load(object sender, EventArgs e)
        {
            sconn = new SqlConnection(connstr);
        }

private void button2_Click(object sender, EventArgs e)
        {

if (ds.HasChanges()) //改动过
                {
try
                    {
                        sap.Update(ds.Tables["test"]);
                    }
catch (System.Exception ex)
                     {
                         MessageBox.Show(ex.ToString());
return;
                     }
finally
                     {
                         MessageBox.Show("更新成功!");
                     }

                }



        }

private void button3_Click(object sender, EventArgs e)
        {
if (ds.HasChanges())
            {
                MessageBox.Show("改动过");
            }
        }

private void dataGridView1_RowPostPaint(object sender, DataGridViewRowPostPaintEventArgs e)
        {
//在表的前面加行号
            SolidBrush b = new SolidBrush(this.dataGridView1.RowHeadersDefaultCellStyle.ForeColor);
            e.Graphics.DrawString((e.RowIndex + 1).ToString(System.Globalization.CultureInfo.CurrentUICulture), this.dataGridView1.DefaultCellStyle.Font, b, e.RowBounds.Location.X + 20, e.RowBounds.Location.Y + 4);

        }
    }
}

sqldataadapter执行存储过程，可以看到它的构造函数里有一个 sqldataadapter(sqlcommand) 的方法。那么执行服务器里的存储就是填充cmd的各个参数就OK

   private void button4_Click(object sender, EventArgs e)
        {
            SqlCommand cmd = new SqlCommand();
            cmd.Connection = sconn; 
            cmd.CommandText = "myfy_one"; //存储过程名                    
            cmd.CommandType = CommandType.StoredProcedure;
            SqlParameter param = cmd.CreateParameter();
            param.Direction = ParameterDirection.Input; //输入参数  
            param.ParameterName = "@size";
            param.DbType = DbType.Int32;
            param.Value = 50; //分页大小       
            SqlParameter param1 = cmd.CreateParameter();  
            param1.Direction = ParameterDirection.Input; 
            param1.ParameterName = "@number";   
            param1.DbType = DbType.Int32;
            param1.Value = 2;  //当前页    
            cmd.Parameters.Add(param); 
            cmd.Parameters.Add(param1);

try
            {
                sap = new SqlDataAdapter(cmd);
                SqlCommandBuilder scbder = new SqlCommandBuilder(sap);
                ds = new DataSet();
                sap.Fill(ds, "test");
                dataGridView1.DataSource = ds.Tables["test"];
            }
catch (SqlException ee)
            {
                MessageBox.Show(ee.ToString());
            }
finally
            {
if (sconn.State == ConnectionState.Open)
                {
                    sconn.Close();
                }
            } 

        }

 

 

更新按钮更改为          
if (ds.HasChanges()) //改动过
                {
                    Boolean check = true;
try
                    {
                        sap.Update(ds.Tables["test"]);
                    }
catch (System.Exception ex)
                     {
                         MessageBox.Show(ex.ToString());
//return;
                         check = false;
                     }
finally
                     {
if (check)
                         {
                             MessageBox.Show("更新成功!");
                         }
                     }

                }

 

 

 

简单的过程：

1 先生成一个Sqlconnection连接

2 创建一个SqldataAdapter对象，并与commandtxt，conn关联

3 new dataset

4 用adapter的fill填充数据到dataset对象,fill(ds,"表名"),dataset是由多个对象组成，其中一个datatables，填的数据就是填到这里

5 关联到datagridview，即datasource = dataset.tables["表名"]

6 若是要点一个按钮更新所有更改，那么要sqlcommandbuilder这个对象。

注意： 这里说的是单个表查询的数据，并且表要设置主键。 若是多个表，即要生成多个adapter.fill到本地的dataset表集合中（这个没试过）。

7

转载于:https://www.cnblogs.com/onepc/archive/2011/11/01/2230962.html