c#sql Server 连接 数据库教程

admin管理员组

文章数量:1794759

c# sql Server 连接 数据库教程

demo下载 1.首先建立数据库连接： 首先说明一下数据库连接字符串： string conString = @"Data Source=bds2715212581.my3w;Initial Catalog=bds271521258_db;Persist Security Info=True;User ID=bds271521258;Password=123456"; 下面是sql Server2008 服务器启动界面： Data Source是服务器名称，即上图。 User ID是数据库帐号（登录名），即上图， Password数据库帐号密码，即上图。 Initial Catalog是数据库名称，打开数据库后，将会看到数据库名称，如下图： 蓝色箭头是创建数据库是默认的。其他的才是我们创建的，如上图红色箭头所指的名为“ bds271521258_db”的数据库。 所以我的数据库连接串 为： Initial Catalog=bds271521258_db。 Persist Security Info属性的意思是表示是否保存安全信，其实可以简单的理解为"ADO在数据库连接成功后是否保存密码信"，详细说明请看blog.csdn/yzsind/article/details/1507717 2建立数据库 我工程里有两个数据库，你要建数据库的时候应该就有一个。不要被上图误导了。 3建立连接 代码如下：很简单如下： class SQLServerConnectionTool { string conString = @"Data Source=bds2715212581.my3w;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(); sqlSelectCommand.CommandText = 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 * FROM UserInfor”； /// <summary> /// 执行spl语句 /// </summary> /// <param name="command"></param> public void SqlCommandExeCute(string command) { try { sqlCommand.CommandText = command; sqlCommand.ExecuteNonQuery(); } catch (Exception e) { Debug.WriteLine("语句执行错误" + e.ToString()); } //catch //{ //} } 到这里数据库建立与sql 命令就完成了。如果你不需要 D ataGridView表格显示，到这里就完成了。 5下面再说一下DataGridView表格组件和SqlDataAdapter类，DataTable类，和DataSet类。 最终实现表格与数据库的绑定：效果如下： 代码封装如下：其中DataTable参数就是要与DataGridView绑定的数据 #region sqlDataAdapter封装类查询 string selecetCmd = "SELECT * FROM UserInfor"; public DataTable DataTableSqlSelectExeCute(DataTable dataTable) { try { dataTable.Clear(); sqlCommand.CommandText = 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 { sqlCommand.CommandText = 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 { sqlCommand.CommandText = 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=bds2715212581.my3w;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(); sqlSelectCommand.CommandText = 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 { sqlCommand.CommandText = 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) { sqlCommand.CommandText = 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(); sqlCommand.CommandText = 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 { sqlCommand.CommandText = 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 { sqlCommand.CommandText = 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) { sqlCommand.CommandText = ""; 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 System.ComponentModel; 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下载

本文标签： 数据库教程SQLSERVER