demo下载
1.首先建立数据库连接：首先说明一下数据库连接字符串：string conString = @"Data Source=;Initial Catalog=bds271521258_db;Persist Security Info=True;User ID=bds271521258;Password=123456";
下面是sql server 服务器启动界面：Data Source是服务器名称，即上图。User ID是数据库帐号（登录名），即上图，Password数据库帐号密码，即上图。Initial Catalog是数据库名称，打开数据库后，将会看到数据库名称，如下图：
蓝色箭头是创建数据库是默认的。其他的才是我们创建的，如上图红色箭头所指的名为“bds271521258_db”的数据库。
所以我的数据库连接串 为：Initial Catalog=bds271521258_db。
Persist Security Info属性的意思是表示是否保存安全信息，其实可以简单的理解为"ADO在数据库连接成功后是否保存密码信息"，详细说明请看/yzsind/article/details/1507717
2建立数据库
我工程里有两个数据库，你要建数据库的时候应该就有一个。不要被上图误导了。3建立连接代码如下：很简单如下：
class SQLServerConnectionTool{string conString = @"Data Source=;Initial Catalog=bds271521258_db;Persist Security Info=True;User ID=bds271521258;Password=123456";string cmd = "SELECT * FROM UserInfor";/// <summary> sql命令</summary>SqlCommand sqlCommand;/// <summary> sql查询语句</summary>SqlCommand sqlSelectCommand;/// <summary> 连接服务器</summary>SqlConnection sqlConnection;/// <summary> 建立数据库和dataGridView组建的桥梁-----》填充DataTable（表示数据库中一个库中的一个表）或者DataSet（表示数据库的一个库）类型</summary>SqlDataAdapter sqlDataAdapter;public SQLServerConnectionTool(){sqlConnection = new SqlConnection(conString);sqlCommand = new SqlCommand();sqlCommand.Connection = sqlConnection;sqlSelectCommand = new SqlCommand();mandText = cmd;sqlSelectCommand.Connection = sqlConnection;sqlDataAdapter = new SqlDataAdapter();}/// <summary>/// 建立一个数据库连接/// </summary>public void serverConnetion(){try{sqlConnection.Open();//sqlConnection.OpenAsync(cancellationToken);}catch (InvalidOperationException e){Debug.WriteLine(e.ToString());}catch (SqlException e){Debug.WriteLine(e.Message);}}｝
4 建立连接后我们就可以发送sql指令了，下面是发送指令的方法。其中command就是sql语句，如“SELECT*FROMUserInfor”；
/// <summary>/// 执行spl语句/// </summary>/// <param name="command"></param>public void SqlCommandExeCute(string command){try{mandText = command;sqlCommand.ExecuteNonQuery();}catch (Exception e){Debug.WriteLine("语句执行错误" + e.ToString());}//catch//{//}}
到这里数据库建立与sql 命令就完成了。如果你不需要DataGridView表格显示，到这里就完成了。
5下面再说一下DataGridView表格组件和SqlDataAdapter类，DataTable类，和DataSet类。
最终实现表格与数据库的绑定：效果如下：代码封装如下：其中DataTable参数就是要与DataGridView绑定的数据
#region sqlDataAdapter封装类查询string selecetCmd = "SELECT * FROM UserInfor";public DataTable DataTableSqlSelectExeCute(DataTable dataTable){try{dataTable.Clear();mandText = selecetCmd;sqlDataAdapter.SelectCommand = sqlCommand;sqlDataAdapter.Fill(dataTable);}catch (Exception e){MessageBox.Show("数据查询错误" + e.Message);}return dataTable;}string addCmd = "insert into UserInfor values({0},'{1}',{2},{3},'{4}')";public void DataTableSqlInsertExeCute(DataTable dataTable, int userId, string name, int sex, int age, string birthday){try{mandText = string.Format(addCmd, userId, name, sex, age, birthday);sqlDataAdapter.InsertCommand = sqlCommand;dataTable.Rows.Add(userId, name, sex, age, birthday);sqlDataAdapter.Update(dataTable);dataTable.Clear();sqlDataAdapter.Fill(dataTable);}catch (Exception e){MessageBox.Show("数据插入错误" + e.Message);}}string delete = "DELETE FROM UserInfor WHERE UserId = {0}";public DataTable DataTableSqlDeleteExeCute(DataTable dataTable, int row){try{mandText = string.Format(delete, dataTable.Rows[row]["UserId"]);sqlDataAdapter.DeleteCommand = sqlCommand;dataTable.Rows[row].Delete();sqlDataAdapter.Update(dataTable);dataTable.Clear();sqlDataAdapter.Fill(dataTable);}catch (Exception e){MessageBox.Show("删除错误" + e.Message);}return dataTable;}
上面要注意SqlDataAdapter类的InsertCommand（必须初始化sql语句（如上面sqlDataAdapter.InsertCommand = sqlCommand;））、DeleteCommand（必须初始化sql语句（如上面sqlDataAdapter.DeleteCommand = sqlCommand;））。
当使用InsertCommand添加记录时，dataTable表也必须添加一条新记录（如上面的代码，dataTable.Rows.Add(userId, name, sex, age, birthday)；），保持一一对应关系。
当使用DeleteCommand删除记录时，dataTable表也必须删除相应的记录（如上面的代码， dataTable.Rows[row].Delete();），这个也要保持一致性。
所有代码：
using System;using System.Collections.Generic;using System.Data;using System.Data.SqlClient;using System.Diagnostics;using System.Linq;using System.Text;using System.Threading.Tasks;using System.Windows.Forms;namespace SQLServer{class SQLServerConnectionTool{string conString = @"Data Source=;Initial Catalog=bds271521258_db;Persist Security Info=True;User ID=bds271521258;Password=123456";string cmd = "SELECT * FROM UserInfor";/// <summary> sql命令</summary>SqlCommand sqlCommand;/// <summary> sql查询语句</summary>SqlCommand sqlSelectCommand;/// <summary> 连接服务器</summary>SqlConnection sqlConnection;/// <summary> 建立数据库和dataGridView组建的桥梁-----》填充DataTable（表示数据库中一个库中的一个表）或者DataSet（表示数据库的一个库）类型</summary>SqlDataAdapter sqlDataAdapter;public SQLServerConnectionTool(){sqlConnection = new SqlConnection(conString);sqlCommand = new SqlCommand();sqlCommand.Connection = sqlConnection;sqlSelectCommand = new SqlCommand();mandText = cmd;sqlSelectCommand.Connection = sqlConnection;sqlDataAdapter = new SqlDataAdapter();}/// <summary>/// 建立一个数据库连接/// </summary>public void serverConnetion(){try{sqlConnection.Open();//sqlConnection.OpenAsync(cancellationToken);}catch (InvalidOperationException e){Debug.WriteLine(e.ToString());}catch (SqlException e){Debug.WriteLine(e.Message);}}/// <summary>/// 关闭连接，清理数据/// </summary>public void serverClose(){sqlCommand = null;sqlSelectCommand = null;sqlDataAdapter = null;try{if (sqlConnection != null){sqlConnection.Close();sqlConnection = null;}}catch{}}#region SQL语句/// <summary>/// 执行spl语句/// </summary>/// <param name="command"></param>public void SqlCommandExeCute(string command){try{mandText = command;sqlCommand.ExecuteNonQuery();}catch (Exception e){Debug.WriteLine("语句执行错误" + e.ToString());}//catch//{//}}#region sql包装 未使用public void SqlCommandInsertExeCute(string command){SqlCommandExeCute(command);}public void SqlCommandDeleteExeCute(string command){SqlCommandExeCute(command);}public void SqlCommandSelectExeCute(string command){mandText = command;List<SqlParameter> list = new List<SqlParameter>();SqlDataReader sqlDataAdapter = sqlCommand.ExecuteReader();//添加参数sqlCommand.Parameters.AddRange(list.ToArray());while (sqlDataAdapter.Read()){Console.WriteLine("{0}\t\t{1}", sqlDataAdapter["用户名"], sqlDataAdapter["性别"]);}sqlDataAdapter.Close();}#endregion#endregion#region sqlDataAdapter封装类查询string selecetCmd = "SELECT * FROM UserInfor";public DataTable DataTableSqlSelectExeCute(DataTable dataTable){try{dataTable.Clear();mandText = selecetCmd;sqlDataAdapter.SelectCommand = sqlCommand;sqlDataAdapter.Fill(dataTable);}catch (Exception e){MessageBox.Show("数据查询错误" + e.Message);}return dataTable;}string addCmd = "insert into UserInfor values({0},'{1}',{2},{3},'{4}')";public void DataTableSqlInsertExeCute(DataTable dataTable, int userId, string name, int sex, int age, string birthday){try{mandText = string.Format(addCmd, userId, name, sex, age, birthday);sqlDataAdapter.InsertCommand = sqlCommand;dataTable.Rows.Add(userId, name, sex, age, birthday);sqlDataAdapter.Update(dataTable);dataTable.Clear();sqlDataAdapter.Fill(dataTable);}catch (Exception e){MessageBox.Show("数据插入错误" + e.Message);}}string delete = "DELETE FROM UserInfor WHERE UserId = {0}";public DataTable DataTableSqlDeleteExeCute(DataTable dataTable, int row){try{mandText = string.Format(delete, dataTable.Rows[row]["UserId"]);//sqlDataAdapter.SelectCommand = sqlSelectCommand;sqlDataAdapter.DeleteCommand = sqlCommand;dataTable.Rows[row].Delete();sqlDataAdapter.Update(dataTable);dataTable.Clear();sqlDataAdapter.Fill(dataTable);}catch (Exception e){MessageBox.Show("删除错误" + e.Message);}return dataTable;}/// <summary>/// 更新函数未实现/// </summary>/// <param name="dataTable"></param>/// <returns></returns>public DataTable DataTableSqlUpdateExeCute(DataTable dataTable){mandText = "";sqlDataAdapter.UpdateCommand = sqlSelectCommand;sqlDataAdapter.DeleteCommand = sqlCommand;sqlDataAdapter.Update(dataTable);sqlDataAdapter.Fill(dataTable);return dataTable;}#endregion~SQLServerConnectionTool(){serverClose();}}}
然后建立一个form窗体：
窗体代码如下：
using System;using System.Collections.Generic;using ponentModel;using System.Data;using System.Drawing;using System.Linq;using System.Text;using System.Threading.Tasks;using System.Windows.Forms;namespace SQLServer{public partial class Form1 : Form{SQLServerConnectionTool sQLServerConnectionTool;public Form1(){InitializeComponent();sQLServerConnectionTool = new SQLServerConnectionTool();sQLServerConnectionTool.serverConnetion();dataTable = new DataTable();dataGridView1.DataSource = dataTable;}string cmd = "SELECT * FROM UserInfor";string addCmd = "insert into UserInfor values(5,'zcy',0,26,'')";string delete = "DELETE FROM UserInfor WHERE UserId = 3";DataTable dataTable;private void button1_Click(object sender, EventArgs e){//sQLServerConnectionTool.SqlCommandExeCute(delete);//sQLServerConnectionTool.SqlCommandExeCute(addCmd);//dataGridView1.DataSource = sQLServerConnectionTool.DataTableSqlDeleteExeCute(delete);sQLServerConnectionTool.DataTableSqlSelectExeCute(dataTable);// sQLServerConnectionTool.SqlCommandSelectExeCute(cmd);//sQLServerConnectionTool.DataTableSqlInsertExeCute(addCmd);//dataGridView1.DataSource = sQLServerConnectionTool.DataTableSqlSelectExeCute(cmd);//dataGridView1.DataSource = sQLServerConnectionTool.DataTableSqlInsertExeCute(addCmd);}private void button2_Click(object sender, EventArgs e){int row;if(int.TryParse(textBox1.Text, out row))sQLServerConnectionTool.DataTableSqlDeleteExeCute(dataTable,row);}private void button3_Click(object sender, EventArgs e){if (dataTable.Columns.Count != 5){MessageBox.Show("请先查询数据");}int userId;int sex;int age;if (int.TryParse(textBox2.Text, out userId) && int.TryParse(textBox4.Text, out sex) && int.TryParse(textBox5.Text, out age)){sQLServerConnectionTool.DataTableSqlInsertExeCute(dataTable, userId, textBox3.Text, sex, age, textBox6.Text);}else{MessageBox.Show("数据填写错误");}}private void button4_Click(object sender, EventArgs e){sQLServerConnectionTool.SqlCommandExeCute(textBox7.Text); //执行sql语句sQLServerConnectionTool.DataTableSqlSelectExeCute(dataTable); //刷新界面}private void textBox2_KeyPress(object sender, KeyPressEventArgs e){if (!char.IsNumber(e.KeyChar) && e.KeyChar != 8){e.Handled = true;MessageBox.Show("请输入数字");}//else//{// ////}}private void textBox4_KeyPress(object sender, KeyPressEventArgs e){if (!char.IsNumber(e.KeyChar) && e.KeyChar != 8){e.Handled = true;MessageBox.Show("请输入数字");}}private void textBox5_KeyPress(object sender, KeyPressEventArgs e){if (!char.IsNumber(e.KeyChar) && e.KeyChar != 8){e.Handled = true;MessageBox.Show("请输入数字");}}}}
上面代码需要和你设计的窗体组件对应起来。嫌麻烦可以看我的Demo。
demo下载
如果觉得《c# sql Server 连接 数据库教程》对你有帮助，请点赞、收藏，并留下你的观点哦！
