当前位置：首页 > news >正文

进阶08：C#与SQL Server通信

news 2025/8/2 10:25:57

本节目标：

1）新建Winform项目，连接到SQL服务器；

2）新建/删除表，对表中的数据进行增、删、改、查等操作；

下面是对上面要求的最终结果，可以实现上面的所有功能。本节主要是用于作为SQL读写的参考范例。

1.1 界面设计

1）新建项目

2）界面设计

一个DataGridView（dataGridView1）；

一个TextBox（tb_TableName）；

六个按钮（btn_CreateTable、btn_DeleteTable、btn_ReadTable、btn_WriteTable、btn_ModifyLine、btn_DeleteRow）；

1.2 编程

主界面的程序流程图如下所示，程序主要两个部分组成：SQL类和调用程序。

1.2.1 SQLHelper类

1）依赖

using System.Data.SqlClient;
using System.Data;

2）变量

//其中ServerName填写自己的服务器名称
public static string connString = @"Server=ServerName;DataBase=DBTEST;Trusted_Connection=SSPI";

3）建表

public static int CreateTable(string table,DataTable dt,int primarykey)
{SqlConnection sqlCnt = new SqlConnection(connString);//1.连接     if (sqlCnt.State != ConnectionState.Open){sqlCnt.Open();}//2.定义SQL语句//string cmdStr = "create table " + table + "(C bit,Id int, name varchar(20),Min float,Max float,Value float,Result varchar(5))";string cmdstr = "create table " + table + "(";int columns = dt.Columns.Count;for (int i = 0; i < columns; i++){cmdstr += dt.Columns[i].ColumnName.ToString();switch (dt.Columns[i].DataType.Name.ToString()){case "Boolean":cmdstr += " bit";break;case "Double":cmdstr += " float";break;case "String":cmdstr += " varchar(20)";break;case "Int32":cmdstr += " int";break;default:break;}if (i == primarykey)cmdstr += " primary key,";elsecmdstr += ",";}cmdstr = cmdstr.Remove(cmdstr.Length - 1);cmdstr += ")";SqlCommand cmd = new SqlCommand(cmdstr, sqlCnt);try{cmd.ExecuteNonQuery();Console.WriteLine("数据表创建成功。");}catch (SqlException ae){Console.WriteLine("数据表创建失败。" + ae.Message.ToString());return 0;}finally{sqlCnt.Close();//对数据库操作完成后，需要关闭数据库，释放内存}return 1;
}

4）删除表

public static int DeleteTable(string table){SqlConnection sqlCnt = new SqlConnection(connString);//1.连接     if (sqlCnt.State != ConnectionState.Open){sqlCnt.Open();}//2.定义SQL语句string cmdStr = "drop table " + table;SqlCommand cmd = new SqlCommand(cmdStr, sqlCnt);try{cmd.ExecuteNonQuery();Console.WriteLine("数据表删除成功。");}catch (SqlException ae){Console.WriteLine("数据表删除失败。" + ae.Message.ToString());return 0;}finally{sqlCnt.Close();//对数据库操作完成后，需要关闭数据库，释放内存}return 1;}

5）增加行

public static int InsertRow(string table, DataRow myRow)
{//************ 增加一行数据功能SqlConnection sqlCnt = new SqlConnection(connString);try{//1.连接             sqlCnt.Open();//2.读取表string cmdStr = "select * from " + table;SqlDataAdapter myDataAdapter = new SqlDataAdapter(cmdStr, sqlCnt);DataSet myDataSet = new DataSet();myDataAdapter.Fill(myDataSet);//3.添加一行 myDataSet.Tables[0].Rows.Add(myRow.ItemArray);//                    //4.将DataSet的修改提交至“数据库”SqlCommandBuilder mySqlCommandBuilder = new SqlCommandBuilder(myDataAdapter);myDataAdapter.Update(myDataSet);//5.关闭myDataSet.Dispose();        // 释放DataSet对象myDataAdapter.Dispose();    // 释放SqlDataAdapter对象             sqlCnt.Dispose();           // 释放数据库连接对象mySqlCommandBuilder.Dispose();return 1;}catch(Exception ex){Console.WriteLine(ex.Message);return 0;}finally{sqlCnt.Close();             // 关闭数据库连接}
}

6）删除行

public static int DeleteRow(string table,string condition)
{    //1.连接SqlConnection sqlCnt = new SqlConnection(connString);string cmdStr = "Delete from " + table + " " + condition;SqlCommand MyCommand=new SqlCommand(cmdStr,sqlCnt);//"Delete from TestCrud where TestCrudId = 1003;"try{sqlCnt.Open();MyCommand.ExecuteNonQuery();             return 1;}catch(Exception ex){Console.WriteLine(ex.Message);return 0;}      finally{sqlCnt.Close();}
}

7）读取表

public static DataTable SelectTable(string table,string condition)
{//查表，返回一个DataTableSqlConnection sqlCnt = new SqlConnection(connString);sqlCnt.Open();string cmdStr = "select * from " + table + " " + condition;SqlDataAdapter myDataAdapter = new SqlDataAdapter(cmdStr, sqlCnt);DataSet myDataSet = new DataSet();myDataAdapter.Fill(myDataSet);DataTable myTable = myDataSet.Tables[0];myDataSet.Dispose();        // 释放DataSet对象myDataAdapter.Dispose();    // 释放SqlDataAdapter对象sqlCnt.Close();             // 关闭数据库连接sqlCnt.Dispose();           // 释放数据库连接对象return myTable;
}

8）修改行

public static int UpdateRow(string table,string condition)
{SqlConnection sqlCnt = new SqlConnection(connString);try{//打开数据库连接sqlCnt.Open();//创建SqlCommand对象string cmdStr = "UPDATE " + table + " " + condition;//"UPDATE TestCrud SET TestCrudName = '小红' WHERE TestCrudId = '1008';"SqlCommand command = new SqlCommand(cmdStr, sqlCnt);//执行SQL语句int rowsAffected = command.ExecuteNonQuery();return 1;}catch (Exception ex){//处理异常Console.WriteLine(ex.Message);return 0;}finally{//关闭数据库连接sqlCnt.Close();}
}

1.2.2 主程序调用

1）依赖

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;

2）变量

DataTable dt = new DataTable();

3）DataGridView初始化

private void FormReportInit()
{dt.Columns.Add("c", typeof(bool));dt.Columns.Add("Id", typeof(int));dt.Columns.Add("Name", typeof(string));dt.Columns.Add("Min", typeof(double));dt.Columns.Add("Max", typeof(double));dt.Columns.Add("Value", typeof(double));dt.Columns.Add("Result", typeof(string));dataGridView1.DataSource = dt;dataGridView1.Columns["c"].Width = 20;dataGridView1.Columns["Id"].Width = 35;dataGridView1.Columns["Name"].Width = 70;dataGridView1.Columns["Min"].Width = 60;dataGridView1.Columns["Max"].Width = 60;dataGridView1.Columns["Value"].Width = 60;dataGridView1.Columns["Result"].Width = 60;Font font1 = new Font("", 10, FontStyle.Regular);dataGridView1.Font = font1;
}

4）创建表按钮事件

private void btn_CreateTable_Click(object sender, EventArgs e)
{            SQLHelper.CreateTable(tb_TableName.Text.ToString(),dt,1);//Id 是Primary key，值不能重复，重复写不进去
}

5）删除表按钮事件

private void btn_DeleteTable_Click(object sender, EventArgs e)
{SQLHelper.DeleteTable(tb_TableName.Text.ToString());
}

6）读表按钮事件

private void btn_ReadTable_Click(object sender, EventArgs e)
{dt = SQLHelper.SelectTable(tb_TableName.Text.ToString(), "");dataGridView1.DataSource = dt;
}

7）写入服务器事件

private void btn_WriteTable_Click(object sender, EventArgs e)
{foreach (DataRow myRow in dt.Rows){int ii = SQLHelper.InsertRow(tb_TableName.Text.ToString(), myRow);}               
}

8）删除行事件

private void btn_DeleteRow_Click(object sender, EventArgs e)
{//删除行int selectedRowIndex = dataGridView1.CurrentCell.RowIndex;int id = (int)dataGridView1.Rows[selectedRowIndex].Cells[1].Value;int ii = SQLHelper.DeleteRow(tb_TableName.Text.ToString(), "where id = " + id.ToString());//再读取一遍dt = SQLHelper.SelectTable(tb_TableName.Text.ToString(), "");dataGridView1.DataSource = dt;
}

9）修改行事件

private void btn_ModifyLine_Click(object sender, EventArgs e)
{//修改int selectedRowIndex = dataGridView1.CurrentCell.RowIndex;int id = (int)dataGridView1.Rows[selectedRowIndex].Cells[1].Value;string cmdstr = "set "+ "c = '" + dataGridView1.Rows[selectedRowIndex].Cells[0].Value.ToString() + "', "+"name = '" + dataGridView1.Rows[selectedRowIndex].Cells[2].Value.ToString() + "', "+ "Min = '" + dataGridView1.Rows[selectedRowIndex].Cells[3].Value.ToString() +"', "+ "Max = '" + dataGridView1.Rows[selectedRowIndex].Cells[4].Value.ToString() + "', "+ "Value = '" + dataGridView1.Rows[selectedRowIndex].Cells[5].Value.ToString() + "', "+ "Result = '" + dataGridView1.Rows[selectedRowIndex].Cells[6].Value.ToString() + "' "+ "where id = " + id.ToString();int ii = SQLHelper.UpdateRow(tb_TableName.Text.ToString(), cmdstr);//再读取一遍dt = SQLHelper.SelectTable(tb_TableName.Text.ToString(), "");dataGridView1.DataSource = dt;
}