好久不接触这些闲暇时间回顾一下以前的基础。因为平常使用的时候都是直接调用SqlDB.dll这个类。先看这个类的结构
纸上得来终觉浅，绝知此事要躬行。个人觉得里面的标准操作就是对数据库增删查改 。特别适合初学者操作数据库，当初我也是那么来的。下面直接贴代码：
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web; using System.Data;
using System.Data.SqlClient;
using System.Collections;
using System.Configuration;
using System.Text; namespace FileUpDownThroou
{
/// <summary>
/// SqlServer访问类
/// </summary>
public class SqlDB
{
#region <——————————定义参数——————————>
/// <summary>
/// 数据库链接字符串
/// </summary>
protected readonly string SqlConning = ConfigurationManager.ConnectionStrings["ConnSqlString"].ConnectionString;
/// <summary>
/// 数据库链接对象
/// </summary>
private SqlConnection sqlConnection;
/// <summary>
/// 数据库事务对象
/// </summary>
private SqlTransaction sqlTransaction;
/// <summary>
/// 构造函数创建数据库链接
/// </summary>
#endregion #region<——————————构造函数——————————>
public SqlDB()
{
if(sqlConnection==null)
{
sqlConnection = new SqlConnection(SqlConning);
sqlConnection.Open();
}
}
#endregion #region <——————————事务处理——————————>
public SqlTransaction SqlTran
{
get { return sqlTransaction; }
set { sqlTransaction = value; }
}
/// <summary>
/// 打开数据链接
/// </summary>
public void OpenConnection()
{
if(sqlConnection != null && sqlConnection.State != ConnectionState.Open)
{
sqlConnection.Open();
}
}
/// <summary>
/// 关闭数据库链接
/// </summary>
public void CloseConnection()
{
if(sqlConnection != null && sqlConnection.State==ConnectionState.Open)
{
sqlConnection.Close();
}
}
/// <summary>
/// 开始一个数据库事务
/// </summary>
/// <param name="isolationlevel">链接的事务锁定行为</param>
public void BeginTransaction(IsolationLevel isolationlevel)
{
if(sqlConnection.State==ConnectionState.Closed)
{
sqlConnection.Open();
}
try
{
SqlTran = sqlConnection.BeginTransaction(isolationlevel);
}
catch(SqlException ex)
{
throw new Exception(ex.StackTrace);
}
catch(Exception e)
{
throw new Exception(e.StackTrace);
}
}
/// <summary>
/// 开始一个数据库事务
/// </summary>
public void BeginTransaction()
{
if (sqlConnection.State == ConnectionState.Closed)
{
sqlConnection.Open();
} try
{
SqlTran = sqlConnection.BeginTransaction();
}
catch (SqlException ex)
{
throw new Exception(ex.StackTrace);
}
catch (Exception e)
{
throw new Exception(e.StackTrace);
}
}
/// <summary>
/// 提交一个数据库事务
/// </summary>
public void CommitTransaction()
{
try
{
sqlTransaction.Commit();
CloseConnection();
}
catch(SqlException ex)
{
throw new Exception(ex.StackTrace);
}
catch(Exception e)
{
throw new Exception(e.StackTrace);
}
}
/// <summary>
/// 回退一个数据库事务
/// </summary>
public void RollBackTransaction()
{
try
{
SqlTran.Rollback();
CloseConnection(); }
catch (SqlException ex)
{
throw new Exception(ex.StackTrace);
}
catch (Exception e)
{
throw new Exception(e.StackTrace);
}
}
#endregion #region <——————————标准操作——————————> /// <summary>
/// 执行Sql语句返回SqlDataReader
/// </summary>
/// <param name="Sql">Sql语句</param>
/// <param name="parameters">参数集合</param>
/// <param name="commandType">命令类型</param>
/// <param name="commandBehavior">查询影响说明</param>
/// <returns>满足条件的SqlDataReader</returns>
public SqlDataReader ExecuteDataReader(string Sql, List<SqlParameter> parameters, CommandType commandType, CommandBehavior commandBehavior)
{
return CreateSqlCommand(Sql, parameters, commandType).ExecuteReader(commandBehavior);
} /// <summary>
/// 执行Sql语句返回SqlDataReader
/// </summary>
/// <param name="Sql">Sql语句</param>
/// <param name="parameters">参数集合</param>
/// <param name="commandType">命令类型</param>
/// <returns>满足条件的SqlDataReader</returns>
public SqlDataReader ExecuteDataReader(string Sql, List<SqlParameter> parameters, CommandType commandType)
{
return ExecuteDataReader(Sql, parameters, commandType, CommandBehavior.Default);
} /// <summary>
/// 执行Sql语句返回SqlDataReader
/// </summary>
/// <param name="Sql">Sql语句</param>
/// <param name="parameters">参数集合</param>
/// <returns>满足条件的SqlDataReader</returns>
public SqlDataReader ExecuteDataReader(string Sql, List<SqlParameter> parameters)
{
return ExecuteDataReader(Sql, parameters, CommandType.Text, CommandBehavior.Default);
} /// <summary>
/// 执行Sql语句返回SqlDataReader
/// </summary>
/// <param name="Sql">Sql语句</param>
/// <returns>满足条件的SqlDataReader</returns>
public SqlDataReader ExecuteDataReader(string Sql)
{
return ExecuteDataReader(Sql, null, CommandType.Text, CommandBehavior.Default);
} /// <summary>
/// 执行Sql语句返回单个对象
/// </summary>
/// <param name="Sql">Sql语句</param>
/// <param name="parameters">参数集合</param>
/// <param name="commandType">命令类型</param>
/// <returns>满足条件的单个对象</returns>
public object ExecuteScalar(string Sql, List<SqlParameter> parameters, CommandType commandType)
{
return CreateSqlCommand(Sql, parameters, commandType).ExecuteScalar();
} /// <summary>
/// 执行Sql语句返回单个对象
/// </summary>
/// <param name="Sql">Sql语句</param>
/// <param name="parameters">参数集合</param>
/// <returns>满足条件的单个对象</returns>
public object ExecuteScalar(string Sql, List<SqlParameter> parameters)
{
return ExecuteScalar(Sql, parameters, CommandType.Text);
} /// <summary>
/// 执行Sql语句返回单个对象
/// </summary>
/// <param name="Sql">Sql语句</param>
/// <returns>满足条件的单个对象</returns>
public object ExecuteScalar(string Sql)
{
return ExecuteScalar(Sql, null, CommandType.Text);
} /// <summary>
/// 执行非查询类的Sql语句
/// </summary>
/// <param name="Sql">Sql语句</param>
/// <param name="parameters">参数集合</param>
/// <param name="commandType">命令类型</param>
/// <returns>成功执行所影响的记录数</returns>
public int ExecuteNonQuery(string Sql, List<SqlParameter> parameters, CommandType commandType)
{
return CreateSqlCommand(Sql, parameters, commandType).ExecuteNonQuery();
} /// <summary>
/// 执行非查询类的Sql语句z
/// </summary>
/// <param name="Sql">Sql语句</param>
/// <param name="parameters">参数集合</param>
/// <returns>成功执行所影响的记录数</returns>
public int ExecuteNonQuery(string Sql, List<SqlParameter> parameters)
{
return ExecuteNonQuery(Sql, parameters, CommandType.Text);
} /// <summary>
/// 执行非查询类的Sql语句
/// </summary>
/// <param name="Sql">Sql语句</param>
/// <returns>成功执行所影响的记录数</returns>
public int ExecuteNonQuery(string Sql)
{
return ExecuteNonQuery(Sql, null, CommandType.Text);
} /// <summary>
/// 执行Sql语句返回DataTable
/// </summary>
/// <param name="Sql">Sql语句</param>
/// <param name="parameters">参数集合</param>
/// <param name="commandType">命令类型</param>
/// <returns>DataTable</returns>
public DataTable ExecuteDataTable(string Sql, List<SqlParameter> sqlParameters, CommandType commandType)
{
SqlDataAdapter dataAdapter = new SqlDataAdapter(CreateSqlCommand(Sql, sqlParameters, commandType));
DataTable dataTable = new DataTable();
dataAdapter.Fill(dataTable);
return dataTable;
} /// <summary>
/// 执行Sql语句返回DataTable
/// </summary>
/// <param name="Sql">Sql语句</param>
/// <param name="parameters">参数集合</param>
/// <returns>DataTable</returns>
public DataTable ExecuteDataTable(string Sql, List<SqlParameter> sqlParameters)
{
return ExecuteDataTable(Sql, sqlParameters, CommandType.Text);
} /// <summary>
/// 执行Sql语句返回DataTable
/// </summary>
/// <param name="Sql">Sql语句</param>
/// <returns>DataTable</returns>
public DataTable ExecuteDataTable(string Sql)
{
return ExecuteDataTable(Sql, null, CommandType.Text);
} /// <summary>
/// 执行Sql语句返回DataSet
/// </summary>
/// <param name="Sql">Sql语句</param>
/// <param name="parameters">参数集合</param>
/// <param name="commandType">命令类型</param>
/// <returns>DataSet</returns>
public DataSet ExecuteDataSet(string Sql, List<SqlParameter> sqlParameters, CommandType commandType)
{
SqlDataAdapter dataAdapter = new SqlDataAdapter(CreateSqlCommand(Sql, sqlParameters, commandType));
DataSet dataSet = new DataSet();
dataAdapter.Fill(dataSet);
return dataSet;
} /// <summary>
/// 执行Sql语句返回DataSet
/// </summary>
/// <param name="Sql">Sql语句</param>
/// <param name="parameters">参数集合</param>
/// <returns>DataSet</returns>
public DataSet ExecuteDataSet(string Sql, List<SqlParameter> sqlParameters)
{
return ExecuteDataSet(Sql, sqlParameters, CommandType.Text);
} /// <summary>
/// 执行Sql语句返回DataSet
/// </summary>
/// <param name="Sql">Sql语句</param>
/// <returns>DataSet</returns>
public DataSet ExecuteDataSet(string Sql)
{
return ExecuteDataSet(Sql, null, CommandType.Text);
} /// <summary>
/// 执行查询，返回查询的分页结果数据集
/// </summary>
/// <param name="Sql">Sql语句</param>
/// <param name="parameters">参数集合</param>
/// <param name="commandType">命令类型</param>
/// <param name="PageIndex">页索引</param>
/// <param name="PageSize">每页的记录数</param>
/// <returns>分页结果数据集</returns>
public DataSet ExecuteDataSet(string Sql, List<SqlParameter> sqlParameters, CommandType commandType, int PageIndex, int PageSize)
{
//设置导入的起始地址
int firstPage = PageIndex * PageSize; SqlDataAdapter dataAdapter = new SqlDataAdapter(CreateSqlCommand(Sql, sqlParameters, commandType));
DataSet dataSet = new DataSet();
dataAdapter.Fill(dataSet, firstPage, PageSize, "PranaTable"); return dataSet;
} /// <summary>
/// 执行查询，返回查询的分页结果数据集
/// </summary>
/// <param name="Sql">Sql语句</param>
/// <param name="parameters">参数集合</param>
/// <param name="PageIndex">页索引</param>
/// <param name="PageSize">每页的记录数</param>
/// <returns>分页结果数据集</returns>
public DataSet ExecuteDataSet(string Sql, List<SqlParameter> sqlParameters, int PageIndex, int PageSize)
{
return ExecuteDataSet(Sql, sqlParameters, CommandType.Text, PageIndex, PageSize);
} /// <summary>
/// 执行查询，返回查询的分页结果数据集
/// </summary>
/// <param name="Sql">Sql语句</param>
/// <param name="PageIndex">页索引</param>
/// <param name="PageSize">每页的记录数</param>
/// <returns>分页结果数据集</returns>
public DataSet ExecuteDataSet(string Sql, int PageIndex, int PageSize)
{
return ExecuteDataSet(Sql, null, CommandType.Text, PageIndex, PageSize);
} /// <summary>
/// 创建Sql命令对象
/// </summary>
/// <param name="Sql">Sql语句</param>
/// <param name="parameters">参数集合</param>
/// <param name="commandType">命令类型</param>
/// <returns>成功创建的SqlCommand对象</returns>
private SqlCommand CreateSqlCommand(string Sql, List<SqlParameter> parameters, CommandType commandType)
{
SqlCommand sqlCommand = new SqlCommand(Sql, sqlConnection, SqlTran);
sqlCommand.CommandType = commandType;
if (parameters != null)
{
foreach (SqlParameter parameter in parameters)
{
sqlCommand.Parameters.Add(parameter);
}
}
return sqlCommand;
} #endregion #region <——————————缓存操作——————————> /// <summary>
/// Hashtable中缓存的参数
/// </summary>
private static Hashtable parmCache = Hashtable.Synchronized(new Hashtable()); /// <summary>
/// 缓存查询的OleDb参数对象.
/// </summary>
/// <param name="cacheKey"></param>
/// <param name="cmdParms"></param>
public void CacheParameters(string cacheKey, params SqlParameter[] cmdParms)
{
parmCache[cacheKey] = cmdParms;
} /// <summary>
/// 从缓存获取指定的参数对象数组.
/// </summary>
/// <param name="cacheKey"></param>
/// <returns></returns>
public SqlParameter[] GetCachedParameters(string cacheKey)
{
SqlParameter[] cachedParms = (SqlParameter[])parmCache[cacheKey]; if (cachedParms == null)
{
return null;
} SqlParameter[] clonedParms = new SqlParameter[cachedParms.Length];
for (int i = , j = cachedParms.Length; i < j; i++)
{
clonedParms[i] = (SqlParameter)((ICloneable)cachedParms[i]).Clone();
} return clonedParms;
} #endregion #region <——————————增删改查——————————> private static bool haveTransaction = false; public static bool HaveTransaction
{
get
{
return haveTransaction;
}
set
{
haveTransaction = value;
}
} /// <summary>
/// 查询字符串的默认长度
/// </summary>
private const int defaultCommandTextLength = ; /// <summary>
/// 根据表名等参数创建数据库查询语句
/// </summary>
/// <param name="tableName">表名称</param>
/// <param name="topNum">返回的记录数</param>
/// <param name="field">字段名称</param>
/// <param name="condition">条件</param>
/// <param name="order">排序字段</param>
/// <returns>数据库查询语句</returns>
private StringBuilder CreateSqlCommandText(string tableName, int topNum, string field, string condition, string order)
{
StringBuilder Sql = new StringBuilder(defaultCommandTextLength);
Sql.Append("SELECT ");
if (topNum > )
{
Sql.AppendFormat("TOP {0} ", topNum);
}
Sql.AppendFormat("{0} FROM {1} ", field, tableName);
if (condition != null && condition.Trim().Length != )
{
Sql.AppendFormat("WHERE {0} ", condition);
}
if (order != null && order.Trim().Length != )
{
Sql.AppendFormat(" ORDER BY {0}", order);
}
return Sql;
} /// <summary>
/// 向表中插入数据
/// </summary>
/// <param name="tableName">表名称</param>
/// <param name="field">字段名称字符串</param>
/// <param name="fieldValue">字段值参数字符串</param>
/// <param name="parameters">字段参数集合</param>
/// <returns>成功插入的记录数</returns>
public int Insert(string tableName, string field, string fieldValue, List<SqlParameter> parameters)
{
try
{
if (HaveTransaction == false)
{
OpenConnection();
}
StringBuilder Sql = new StringBuilder(defaultCommandTextLength);
Sql.AppendFormat("INSERT INTO {0}({1}) VALUES({2});", tableName, field,
fieldValue);
return ExecuteNonQuery(Sql.ToString(), parameters);
}
finally
{
if (HaveTransaction == false)
{
CloseConnection();
}
} } /// <summary>
/// 从表中删除数据
/// </summary>
/// <param name="tableName">表名称</param>
/// <param name="condition">删除条件</param>
/// <param name="parameters">删除条件参数集合</param>
/// <returns>成功删除的记录数</returns>
public int Delete(string tableName, string condition, List<SqlParameter> parameters)
{
try
{
if (HaveTransaction == false)
{
OpenConnection();
}
StringBuilder Sql = new StringBuilder(defaultCommandTextLength);
Sql.AppendFormat("DELETE FROM {0} ", tableName);
if (condition != null && condition.Trim().Length != )
{
Sql.AppendFormat("WHERE {0}", condition);
}
return ExecuteNonQuery(Sql.ToString(), parameters);
}
finally
{
if (HaveTransaction == false)
{
CloseConnection();
}
}
} /// <summary>
/// 根据条件修改表中的数据
/// </summary>
/// <param name="tableName">表名称</param>
/// <param name="fieldValue">字段名城</param>
/// <param name="condition">修改条件</param>
/// <param name="parameters">修改参数集合</param>
/// <returns>成功修改的记录数</returns>
public int Update(string tableName, string fieldValue, string condition, List<SqlParameter> parameters)
{
try
{
if (HaveTransaction == false)
{
OpenConnection();
}
StringBuilder Sql = new StringBuilder(defaultCommandTextLength);
Sql.AppendFormat("UPDATE {0} SET {1} ", tableName, fieldValue);
if (condition != null && condition.Trim().Length != )
{
Sql.AppendFormat("WHERE {0}", condition);
}
return ExecuteNonQuery(Sql.ToString(), parameters);
}
finally
{
if (HaveTransaction == false)
{
CloseConnection();
}
}
} #endregion }
}
里面的每个方法都有注解，应该都看的清楚。增删查改都有不同的重载
就像这个DetSet就有5个重载 用的时候根据需要就可以。
/// <summary>
/// 执行Sql语句返回DataSet
/// </summary>
/// <param name="Sql">Sql语句</param>
/// <param name="parameters">参数集合</param>
/// <param name="commandType">命令类型</param>
/// <returns>DataSet</returns>
public DataSet ExecuteDataSet(string Sql, List<SqlParameter> sqlParameters, CommandType commandType)
{
SqlDataAdapter dataAdapter = new SqlDataAdapter(CreateSqlCommand(Sql, sqlParameters, commandType));
DataSet dataSet = new DataSet();
dataAdapter.Fill(dataSet);
return dataSet;
}
/// <summary>
/// 执行Sql语句返回DataSet
/// </summary>
/// <param name="Sql">Sql语句</param>
/// <param name="parameters">参数集合</param>
/// <returns>DataSet</returns>
public DataSet ExecuteDataSet(string Sql, List<SqlParameter> sqlParameters)
{
return ExecuteDataSet(Sql, sqlParameters, CommandType.Text);
}
/// <summary>
/// 执行Sql语句返回DataSet
/// </summary>
/// <param name="Sql">Sql语句</param>
/// <returns>DataSet</returns>
public DataSet ExecuteDataSet(string Sql)
{
return ExecuteDataSet(Sql, null, CommandType.Text);
}
/// <summary>
/// 执行查询，返回查询的分页结果数据集
/// </summary>
/// <param name="Sql">Sql语句</param>
/// <param name="parameters">参数集合</param>
/// <param name="commandType">命令类型</param>
/// <param name="PageIndex">页索引</param>
/// <param name="PageSize">每页的记录数</param>
/// <returns>分页结果数据集</returns>
public DataSet ExecuteDataSet(string Sql, List<SqlParameter> sqlParameters, CommandType commandType, int PageIndex, int PageSize)
{
//设置导入的起始地址
int firstPage = PageIndex * PageSize;
SqlDataAdapter dataAdapter = new SqlDataAdapter(CreateSqlCommand(Sql, sqlParameters, commandType));
DataSet dataSet = new DataSet();
dataAdapter.Fill(dataSet, firstPage, PageSize, "PranaTable");
return dataSet;
}
/// <summary>
/// 执行查询，返回查询的分页结果数据集
/// </summary>
/// <param name="Sql">Sql语句</param>
/// <param name="parameters">参数集合</param>
/// <param name="PageIndex">页索引</param>
/// <param name="PageSize">每页的记录数</param>
/// <returns>分页结果数据集</returns>
public DataSet ExecuteDataSet(string Sql, List<SqlParameter> sqlParameters, int PageIndex, int PageSize)
{
return ExecuteDataSet(Sql, sqlParameters, CommandType.Text, PageIndex, PageSize);
}
/// <summary>
/// 执行查询，返回查询的分页结果数据集
/// </summary>
/// <param name="Sql">Sql语句</param>
/// <param name="PageIndex">页索引</param>
/// <param name="PageSize">每页的记录数</param>
/// <returns>分页结果数据集</returns>
public DataSet ExecuteDataSet(string Sql, int PageIndex, int PageSize)
{
return ExecuteDataSet(Sql, null, CommandType.Text, PageIndex, PageSize);
}