程序对数据库的增删改查是一个业务的核心,频繁读写数据库是很正常的事情,所以封装出一个数据库连接对象,方便到一行代码直接得到DataTable或DataSet对象就是非常有必要的了,下面给出数据库连接类,数据库连接字符串一般会放在Web.config或App.config配置文件中,Web.config是B/S模式使用的,App.config一般是桌面应用程序使用的,后面也会给出相关的配置文件的写法
App.config
<?xml version="1.0" encoding="utf-8" ?>
<configuration>
<appSettings>
<add key="connectionString" value="test" />
</appSettings>
</configuration>
Web.config
<?xml version="1.0" encoding="utf-8"?>
<!--
有关如何配置 ASP.NET 应用程序的详细信息,请访问
https://go.microsoft.com/fwlink/?LinkId=169433
-->
<configuration>
<connectionStrings>
<add name="Provider" connectionString="System.Data.SqlClient" />
<add name="connectionString" connectionString="Server=.;user=sa;pwd=find/-perm4000;database=DBTEST" />
</connectionStrings>
</configuration>
接下来看看在程序中如何获取以上配置文件中的内容
获取App.config内容,在引用处选择添加引用,需要将System.Configuration.dll引入项目中
然后就可以在程序中引用相关类进行获取了
class Program
{
static void Main(string[] args)
{
string connectionString = ConfigurationManager.AppSettings["connectionString"];
Console.Write(connectionString);
Console.Read();
}
}
以上就是获取App.config的方式,下面看看怎么获取Web.config的内容
namespace WebAppTest
{
public partial class WebFormTest : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{
Response.Write(ConfigurationManager.ConnectionStrings["connectionString"]);
}
}
}
接下来就可以封装数据库连接类了,编写的类需要同时适用两种结构的程序,判断获取的值是否为空就行了,为空就取另一个,都为空那就是配置有问题
新建一个类库程序,并引入System.Configuration.dll(这里设计的数据库连接类只能基于Web.config或App.config运行,若小伙伴有其他需求请自行修改)
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Text;
namespace System.DBConnection
{
public class DBConnection
{
private static string connectionString;
private static SqlConnection sqlConnection;
public DBConnection() {
}
//设置静态构造函数,初始化相关静态变量
static DBConnection() {
//适配App.config和Web.config
if (ConfigurationManager.AppSettings["connectionString"] != null)
{
connectionString = ConfigurationManager.AppSettings["connectionString"].ToString();
}
else {
connectionString = ConfigurationManager.ConnectionStrings["connectionString"].ToString();
}
if (sqlConnection == null)
{
sqlConnection = new SqlConnection(connectionString);
}
}
/// <summary>
/// 通过sql查询数据库的结果并以DataTable的形式返回
/// 可以以传入参数的形式去执行,如select * from table where column1=@param1
/// 若要调用存储过程,可使用EXEC spTest @param这种方式(不支持输出参数和返回参数,若需要返回值可将值写入到table中)
///
/// </summary>
/// <param name="sqlstr">数据库查询sql字符串,如select * from table,也可以调用存储过程</param>
/// <param name="dictionary">调用sql或存储过程所需的参数,没有就不传</param>
/// <returns></returns>
public static DataTable GetDataTableBySQL(string sqlstr,Dictionary<string, string> dictionary = null)
{
SqlCommand sqlCommand = new SqlCommand(sqlstr, sqlConnection);
if (dictionary != null)
{
foreach (KeyValuePair<string, string> kvp in dictionary)
{
sqlCommand.Parameters.AddWithValue(kvp.Key, kvp.Value);
}
}
if (sqlConnection.State != ConnectionState.Open)
{
sqlConnection.Open();
}
SqlDataAdapter sqlDataAdapter = new SqlDataAdapter(sqlCommand);
DataTable dataTable = new DataTable();
sqlDataAdapter.Fill(dataTable);
sqlConnection.Close();
return dataTable;
}
/// <summary>
/// 通过sql查询数据库的结果并以DataSet的形式返回
/// 可以以传入参数的形式去执行,如select * from table where column1=@param1
/// 若要调用存储过程,可使用EXEC spTest @param这种方式(不支持输出参数和返回参数,若需要返回值可将值写入到tables中)
///
/// </summary>
/// <param name="sqlstr">数据库查询sql字符串,如select * from table,也可以调用存储过程</param>
/// <param name="dictionary">调用sql或存储过程所需的参数,没有就不传</param>
/// <returns></returns>
public static DataSet GetDataSetBySQL(string sqlstr, Dictionary<string, string> dictionary = null)
{
SqlCommand sqlCommand = new SqlCommand(sqlstr, sqlConnection);
if (dictionary != null)
{
foreach (KeyValuePair<string, string> kvp in dictionary)
{
sqlCommand.Parameters.AddWithValue(kvp.Key, kvp.Value);
}
}
if (sqlConnection.State != ConnectionState.Open)
{
sqlConnection.Open();
}
SqlDataAdapter sqlDataAdapter = new SqlDataAdapter(sqlCommand);
DataSet dataSet = new DataSet();
sqlDataAdapter.Fill(dataSet);
sqlConnection.Close();
return dataSet;
}
/// <summary>
/// 执行增删改sql语句,返回值为更新的行数,删除的行数或增加的行数
///
/// </summary>
/// <param name="sqlstr">sql字符串,如update table set column1='123' where column1='1'</param>
/// <param name="dictionary">调用sql所需的参数,没有就不传</param>
/// <returns></returns>
public static int ExecuteNonQuery(string sqlstr, Dictionary<string, string> dictionary = null)
{
SqlCommand sqlCommand = new SqlCommand(sqlstr, sqlConnection);
if (dictionary != null)
{
foreach (KeyValuePair<string, string> kvp in dictionary)
{
sqlCommand.Parameters.AddWithValue(kvp.Key, kvp.Value);
}
}
if (sqlConnection.State != ConnectionState.Open)
{
sqlConnection.Open();
}
int count = sqlCommand.ExecuteNonQuery();
sqlConnection.Close();
return count;
}
/// <summary>
/// 以DataTable的形式对数据库进行增删改操作,调用前自行给表设置名称,对应数据库中需要操作到的表名
/// 若模式为INSERT,则将传入的dataTable中的数据进行INSERT操作,自增型主键列不要放进来
/// 若模式为UPDATE,则将传入的dataTable中的数据根据主键进行UPDATE操作,将以第一列作为主键进行操作
/// 若模式为DELETE,则将传入的dataTable中的数据根据主键进行DELETE操作,将以第一列作为主键进行操作
///
/// 只支持简单sql的增删改,如delete table1 where column1='1' update table1 set column1='2' where column1='1'
/// 一定要开启事务处理,否则出现执行一半出错的情况会很麻烦(已实现)
///
/// </summary>
/// <param name="dataTable">被操作的数据表,依据DBMode选定的模式对数据库进行增删改操作</param>
/// <param name="DBMode">对数据库的操作模式</param>
/// <returns></returns>
public static Boolean UpdateByDataTable(DataTable dataTable,DBMode dbMode)
{
Boolean successFlg = false;
if (dataTable.TableName == "" || dataTable.TableName==null)
{
throw new DataTableNameException("请设置表的名称");
}
if (sqlConnection.State != ConnectionState.Open)
{
sqlConnection.Open();
}
SqlTransaction sqlTransaction = sqlConnection.BeginTransaction();
try
{
SqlCommand sqlCommand=null;
if (dbMode == DBMode.INSERT)
{
sqlCommand = new SqlCommand(DataTable2SQL(dataTable, "INSERT"), sqlConnection,sqlTransaction);
}
else if (dbMode == DBMode.UPDATE)
{
sqlCommand = new SqlCommand(DataTable2SQL(dataTable, "UPDATE"), sqlConnection, sqlTransaction);
}
else if (dbMode == DBMode.DELETE)
{
sqlCommand = new SqlCommand(DataTable2SQL(dataTable, "DELETE"), sqlConnection, sqlTransaction);
}
sqlCommand.ExecuteNonQuery();
sqlTransaction.Commit();
successFlg = true;
}
catch(Exception e)
{
sqlTransaction.Rollback();
throw e;
}
sqlConnection.Close();
return successFlg;
}
/// <summary>
/// DataTable转SQL字符串的功能,一般用在UPDATE,DELETE,INSERT中,使用该方法前一定要给DataTable命名,这样才能确定操作的表
/// 若使用UPDATE模式则在生成sql前判断行是否被修改,修改了才生成sql
/// 如果SQLMode操作模式选择UPDATE,DELETE时,将以第一列作为WHERE查询条件
/// 如果操作模式选择ISNERT,则不要将自增列放进dataTable
/// </summary>
/// <param name="dataTable"></param>
/// <param name="SQLMode">SQL操作模式,只设置3种,UPDATE,DELETE,INSERT</param>
/// <returns></returns>
private static string DataTable2SQL(DataTable dataTable, string SQLMode)
{
if (dataTable.TableName == "" || dataTable.TableName == null)
{
return "";
}
StringBuilder sb = new StringBuilder();
int i = 0;
int j = 0;
if (SQLMode == "INSERT")
{
for (i = 0; i < dataTable.Rows.Count; i++)
{
sb.AppendFormat("INSERT INTO {0}(", dataTable.TableName);
//拼前半段INSERT
for (j = 0; j < dataTable.Columns.Count; j++)
{
if (j == dataTable.Columns.Count - 1)
{
sb.AppendFormat("{0})", dataTable.Columns[j].Caption);
}
else
{
sb.AppendFormat("{0},", dataTable.Columns[j].Caption);
}
}
//拼后半段VALUES
sb.Append(" VALUES(");
for (j = 0; j < dataTable.Columns.Count; j++)
{
if (j == dataTable.Columns.Count - 1)
{
sb.AppendFormat("'{0}')", dataTable.Rows[i][j]);
}
else
{
sb.AppendFormat("'{0}',", dataTable.Rows[i][j]);
}
}
sb.AppendLine();
}
}
else if (SQLMode == "UPDATE")
{
for (i = 0; i < dataTable.Rows.Count; i++)
{
//判断行是否被修改,修改才生成sql
if (dataTable.Rows[i].RowState == DataRowState.Modified)
{
sb.AppendFormat("UPDATE {0} SET ", dataTable.TableName);
for (j = 1; j < dataTable.Columns.Count; j++)
{
if (j == dataTable.Columns.Count - 1)
{
sb.AppendFormat(" {0}='{1}'", dataTable.Columns[j].Caption, dataTable.Rows[i][j]);
}
else
{
sb.AppendFormat(" {0}='{1}',", dataTable.Columns[j].Caption, dataTable.Rows[i][j]);
}
}
sb.AppendFormat(" WHERE {0}='{1}'", dataTable.Columns[0].Caption, dataTable.Rows[i][0]);
sb.AppendLine();
}
}
}
else if (SQLMode == "DELETE")
{
sb.AppendFormat("DELETE {0} WHERE {1} IN(", dataTable.TableName, dataTable.Columns[0].Caption);
for (i = 0; i < dataTable.Rows.Count; i++)
{
if (i < dataTable.Rows.Count - 1)
{
sb.AppendFormat("'{0}',", dataTable.Rows[i][0]);
}
else
{
sb.AppendFormat("'{0}')", dataTable.Rows[i][0]);
}
}
}
return sb.ToString();
}
}
}
namespace System.DBConnection
{
public enum DBMode
{
INSERT=0,
UPDATE=1,
DELETE=2
}
}
namespace System.DBConnection
{
class DataTableNameException:Exception
{
public DataTableNameException(string message) : base(message)
{
}
}
}
将以上内容编译成dll文件,然后在项目中引入该dll,即可通过简单的一行代码得到DataTable,DataSet或者执行增删改操作
创建一个控制台应用程序,引入DBConnection.dll,然后按照上面说明App.Config中配置数据库链接信息
using System;
using System.Data;
using System.DBConnection;
namespace ConsoleCTest
{
class Program
{
static void Main(string[] args)
{
//一行代码拿到DataTable
DataTable dataTable = DBConnection.GetDataTableBySQL("select * from table1");
//获取列数
int columnCount = dataTable.Columns.Count;
//获取行数
int rowCount = dataTable.Rows.Count;
//打印列名
for (int i = 0; i < columnCount; i++)
{
Console.Write(dataTable.Columns[i].Caption + "\t");
}
Console.WriteLine();
//打印内容
for (int i = 0; i < rowCount; i++)
{
for (int j = 0; j < columnCount; j++)
{
Console.Write(dataTable.Rows[i][j] + "\t");
}
Console.WriteLine();
}
Console.Read();
}
}
}
查看运行结果:
也可以通过这种方式调用存储过程,数据库中创建一个存储过程spTest1
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE spTest1
@param VARCHAR(100)
AS
BEGIN
SET NOCOUNT ON;
--这里将传入的参数直接插入到table1表中然后查出来即可
INSERT INTO table1(tab_name) VALUES(@param)
--这里执行一次select,这样C#调用后可以得到DataTable
SELECT * FROM table1
END
GO
程序中调用:
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.DBConnection;
using System.Text;
namespace ConsoleCTest
{
class Program
{
static void Main(string[] args)
{
Dictionary<string, string> dictionary = new Dictionary<string, string>();
dictionary.Add("@param", "执行了存储过程");
DataTable dataTable = DBConnection.GetDataTableBySQL("EXEC spTest1 @param", dictionary);
dataTable.TableName = "table1";
//获取列数
int columnCount = dataTable.Columns.Count;
//获取行数
int rowCount = dataTable.Rows.Count;
//打印列名
for (int i = 0; i < columnCount; i++)
{
Console.Write(dataTable.Columns[i].Caption + "\t");
}
Console.WriteLine();
//打印内容
for (int i = 0; i < rowCount; i++)
{
for (int j = 0; j < columnCount; j++)
{
Console.Write(dataTable.Rows[i][j] + "\t");
}
Console.WriteLine();
}
Console.Read();
}
}
}
查看执行结果
然后以DataTable的形式进行UPDATE,DELETE,INSERT再看看
using System;
using System.Data;
using System.DBConnection;
using System.Text;
namespace ConsoleCTest
{
class Program
{
static void Main(string[] args)
{
DataTable dataTable = DBConnection.GetDataTableBySQL("SELECT * FROM table1");
dataTable.TableName = "table1";
//获取列数
int columnCount = dataTable.Columns.Count;
//获取行数
int rowCount = dataTable.Rows.Count;
//打印列名
for (int i = 0; i < columnCount; i++)
{
Console.Write(dataTable.Columns[i].Caption + "\t");
}
Console.WriteLine();
//打印内容
for (int i = 0; i < rowCount; i++)
{
for (int j = 0; j < columnCount; j++)
{
Console.Write(dataTable.Rows[i][j] + "\t");
}
Console.WriteLine();
}
Console.WriteLine("--------------------------");
dataTable.Rows[1]["tab_name"] = "testtest";
DBConnection.UpdateByDataTable(dataTable,DBMode.UPDATE);
dataTable = DBConnection.GetDataTableBySQL("SELECT * FROM table1");
//获取列数
columnCount = dataTable.Columns.Count;
//获取行数
rowCount = dataTable.Rows.Count;
//打印列名
for (int i = 0; i < columnCount; i++)
{
Console.Write(dataTable.Columns[i].Caption + "\t");
}
Console.WriteLine();
//打印内容
for (int i = 0; i < rowCount; i++)
{
for (int j = 0; j < columnCount; j++)
{
Console.Write(dataTable.Rows[i][j] + "\t");
}
Console.WriteLine();
}
Console.Read();
}
}
}
以上基于DataTable形式的增删改功能,接下来测试出现异常的情况事务是否可以回滚,还是以这个表为准进行INSERT测试,将第二行的字段长度设置了20个字符,那么可以设置超过这个长度的数据来引发异常进行测试,若第一行数据并没有被插入数据库则表示回滚成功
using System;
using System.Data;
using System.DBConnection;
using System.Text;
namespace ConsoleCTest
{
class Program
{
static void Main(string[] args)
{
DataTable dataTable = DBConnection.GetDataTableBySQL("SELECT * FROM table1");
dataTable.TableName = "table1";
//获取列数
int columnCount = dataTable.Columns.Count;
//获取行数
int rowCount = dataTable.Rows.Count;
//打印列名
for (int i = 0; i < columnCount; i++)
{
Console.Write(dataTable.Columns[i].Caption + "\t");
}
Console.WriteLine();
//打印内容
for (int i = 0; i < rowCount; i++)
{
for (int j = 0; j < columnCount; j++)
{
Console.Write(dataTable.Rows[i][j] + "\t");
}
Console.WriteLine();
}
//设置第二行第二列的字段数据
dataTable.Rows[1][1] = "11111111111111111111111111111111111111111111111111111111";
//移除自增列
dataTable.Columns.RemoveAt(0);
//增加
DBConnection.UpdateByDataTable(dataTable, DBMode.INSERT);
//再次查出打印查看
dataTable = DBConnection.GetDataTableBySQL("SELECT * FROM table1");
dataTable.TableName = "table1";
//获取列数
columnCount = dataTable.Columns.Count;
//获取行数
rowCount = dataTable.Rows.Count;
//打印列名
for (int i = 0; i < columnCount; i++)
{
Console.Write(dataTable.Columns[i].Caption + "\t");
}
Console.WriteLine();
//打印内容
for (int i = 0; i < rowCount; i++)
{
for (int j = 0; j < columnCount; j++)
{
Console.Write(dataTable.Rows[i][j] + "\t");
}
Console.WriteLine();
}
Console.Read();
}
}
}
可以看出,程序异常后,数据库仍然是2条数据,第一条实际上是插入了的,但第二条因为数据过长导致异常,事务回滚,第一条数据也就没了