public Form1() { InitializeComponent(); //连接数据库 string str = "Data Source=IP;Initial Catalog=数据库名称;Persist Security Info=True;User ID=**; Password="; ConnectDatebase(str); } void ConnectDatebase(string sql) { SqlConnection connection = new SqlConnection(); connection.ConnectionString = sql; connection.Open(); SqlDataAdapter adapter = new SqlDataAdapter("select *from steelname", connection); DataSet dsMain = new DataSet(); adapter.Fill(dsMain, "steelname"); this.dataGridView1.DataSource = dsMain; this.dataGridView1.DataMember = "steelname"; }
对于数据库的更新操作
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; using System.Data.Common; namespace DateManagerTools { public partial class Form1 : Form { private DataSet dsMain; private SqlDataAdapter adapter; public Form1() { InitializeComponent(); } private SqlConnection getConnection() { SqlConnection connection = new SqlConnection(); connection.ConnectionString = "Data Source=IP;Initial Catalog=数据库名称;Persist Security Info=True;User ID=**; Password="; return connection; } private void Form1_Load(object sender, EventArgs e) { InitAdapter(); getData(); BindingControl(); } /// <summary> /// 初始化adapter变量 /// </summary> private void InitAdapter() { SqlConnection connection = this.getConnection(); adapter = new SqlDataAdapter("select * from steelname", connection); adapter.FillLoadOption = LoadOption.OverwriteChanges; //新增 SqlCommand InsertCommand = new SqlCommand(); InsertCommand.Connection = connection; InsertCommand.CommandText = "insert into steelname,Name) values(@ID,@Code,@Name)"; InsertCommand.Parameters.Add("@ID", SqlDbType.Int, 4, "ID"); InsertCommand.Parameters.Add("@Code", SqlDbType.Char, 20, "Code"); InsertCommand.Parameters.Add("@Name", SqlDbType.VarChar, 50, "Name"); adapter.InsertCommand = InsertCommand; //修改 SqlCommand UpdateCommand = new SqlCommand(); UpdateCommand.Connection = connection; UpdateCommand.CommandText = "update steelname set Code=@Code,Name=@Name where ID=@ID"; UpdateCommand.Parameters.Add("@ID", SqlDbType.Int, 4, "ID"); UpdateCommand.Parameters.Add("@Code", SqlDbType.Char, 20, "Code"); UpdateCommand.Parameters.Add("@Name", SqlDbType.VarChar, 50, "Name"); adapter.UpdateCommand = UpdateCommand; //删除 SqlCommand DeleteCommand = new SqlCommand(); DeleteCommand.Connection = connection; DeleteCommand.CommandText = "delete steelname where steelname_id=@steelname_id"; DeleteCommand.Parameters.Add("@steelname_id", SqlDbType.Int, 4, "steelname_id"); adapter.DeleteCommand = DeleteCommand; //添加表映射 //DataTableMapping TableMapping = new DataTableMapping(); //TableMapping = adapter.TableMappings.Add("Users", "Users"); //TableMapping.ColumnMappings.Add("Code", "Code"); //TableMapping.ColumnMappings.Add("Name", "Name"); //TableMapping.DataSetTable = "SteelName"; } /// <summary> /// 把控件绑定到数据源 /// </summary> private void BindingControl() { this.dataGridView1.DataSource = dsMain; this.dataGridView1.DataMember = "steelname"; //this.dataGridView1.Columns[0].Width = 40; //this.txtID.DataBindings.Add("Text", dsMain, "Users.ID"); //this.txtCode.DataBindings.Add("Text", dsMain, "Users.Code"); //this.txtName.DataBindings.Add("Text", dsMain, "Users.Name"); } /// <summary> /// 从Sql Server中获取数据 /// </summary> private void getData() { if (dsMain == null) { dsMain = new DataSet(); } else { dsMain.Clear(); } adapter.Fill(dsMain, "steelname"); } //新增 private void button1_Click(object sender, EventArgs e) { this.BindingContext[dsMain, "steelname"].AddNew(); this.BindingContext[dsMain, "steelname"].EndCurrentEdit();//结束编译 //this.txtCode.Focus(); } //删除 private void button2_Click(object sender, EventArgs e) { if (this.BindingContext[dsMain, "steelname"].Position > -1) { if (MessageBox.Show("是否要删除此记录?", "提示", MessageBoxButtons.YesNo) == DialogResult.Yes) { this.BindingContext[dsMain, "steelname"].RemoveAt(this.BindingContext[dsMain, "steelname"].Position); Save(); } } } //保存 private void button3_Click(object sender, EventArgs e) { this.BindingContext[dsMain, "steelname"].EndCurrentEdit(); Save(); } //刷新 private void button4_Click(object sender, EventArgs e) { getData(); } private void Save() { try { adapter.Update(dsMain, "steelname"); } catch (Exception ex) { MessageBox.Show(ex.Message); } } } }
表中的某一列有ComBox绑定在一起:
this.comboBox1.DataSource = dsMain.Tables[0]; //tables[0]第一列 comboBox1.DisplayMember = "steeltype_id"; comboBox1.ValueMember = "steeltype_id";