Ado.net 操作数据库简单方法

using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;

namespace DbService.Dao
{
public class BaseDao
{
private readonly static string connectString = ConfigurationManager.AppSettings["default"];

/// <summary>
/// 执行Sql语句
/// </summary>
/// <param name="sqlText"></param>
/// <param name="parameterList"></param>
public static void ExcuteSqlText(string sqlText, List<SqlParameter> parameterList, bool isSave = true)
{
SqlConnection connection = null;
SqlCommand command = null;
try
{
connection = new SqlConnection(connectString);
command = connection.CreateCommand();
command.CommandText = sqlText;
command.CommandTimeout = 12 * 60 * 60;
command.CommandType = CommandType.Text;
if (parameterList != null && parameterList.Count > 0)
{
command.Parameters.AddRange(parameterList.ToArray());
}
if (isSave)
{
command.ExecuteNonQuery();
}
}
catch (Exception ex)
{
throw new Exception(ex.Message);
}
finally
{
command.Dispose();
connection.Close();
}
}

/// <summary>
/// 通过sql语句查询数据到DataTable
/// </summary>
/// <param name="sqlText"></param>
/// <param name="parameterList"></param>
/// <param name="isSave"></param>
/// <returns></returns>
public static DataTable GetListBySql(string sqlText, List<SqlParameter> parameterList, bool isSave = true)
{
SqlConnection connection = null;
SqlCommand command = null;
DataTable result = new DataTable();
try
{
connection = new SqlConnection(connectString);
command = connection.CreateCommand();
command.CommandText = sqlText;
command.CommandTimeout = 12 * 60 * 60;
command.CommandType = CommandType.Text;
if (parameterList != null && parameterList.Count > 0)
{
command.Parameters.AddRange(parameterList.ToArray());
}
if (isSave)
{
using (SqlDataReader reader = command.ExecuteReader())
{
while (reader.Read())
{
result.Load(reader);
}
}
}
}
catch (Exception ex)
{
throw new Exception(ex.Message);
}
finally
{
command.Dispose();
connection.Close();
}
return result;
}

public static DataTable GetDataSetBySql(string sqlText, List<SqlParameter> parameterList, bool isSave = true)
{
SqlConnection connection = null;
SqlCommand command = null;
DataTable dt = new DataTable();
try
{
connection = new SqlConnection(connectString);
command = connection.CreateCommand();
command.CommandText = sqlText;
command.CommandTimeout = 12 * 60 * 60;
command.CommandType = CommandType.Text;
if (parameterList != null && parameterList.Count > 0)
{
command.Parameters.AddRange(parameterList.ToArray());
}
if (isSave)
{
SqlDataAdapter adapter = new SqlDataAdapter(command);
adapter.Fill(dt);
}
}
catch (Exception ex)
{
throw new Exception(ex.Message);
}
finally
{
command.Dispose();
connection.Close();
}
return dt;
}

/// <summary>
/// 通过Sql语句查询数据
/// </summary>
/// <param name="strText"></param>
/// <param name="dic"></param>
/// <param name="isSave"></param>
/// <returns></returns>
public static DataTable GetListBySql(string strText, IDictionary<string, string> dic, bool isSave = true)
{
var result = new DataTable();

return result;
}

/// <summary>
/// 通过sql语句新增数据
/// </summary>
/// <param name="strText"></param>
/// <param name="dic"></param>
/// <param name="isSave"></param>
public static void Insert(string strText, IDictionary<string, string> dic, bool isSave = true)
{
if (string.IsNullOrWhiteSpace(strText))
{
return;
}

List<SqlParameter> parameters = new List<SqlParameter>();
if (dic != null && dic.Count > 0)
{
foreach (var kvp in dic)
{
SqlParameter parameter = new SqlParameter
{
ParameterName = kvp.Key,
DbType = DbType.String,
Value = kvp.Value
};
parameters.Add(parameter);
}
}
ExcuteSqlText(strText, parameters, isSave);
}

/// <summary>
/// 通过sql语句删除数据
/// </summary>
/// <param name="strText"></param>
/// <param name="isSave"></param>
public static void Delete(string strText, bool isSave = true)
{
ExcuteSqlText(strText, null, isSave);
}

/// <summary>
/// 通过sql语句更新数据
/// </summary>
/// <param name="strText"></param>
/// <param name="isSave"></param>
public static void Update(string strText, bool isSave = true)
{
ExcuteSqlText(strText, null, isSave);
}

/// <summary>
/// 批量插入数据到数据库
/// </summary>
/// <param name="dt">参数dt要保证与数据库表对应</param>
public static void BuilkInsert(DataTable dt)
{
using (SqlConnection conn = new SqlConnection(connectString))
{
//定义SqlBuilkCopy
SqlBulkCopy bulkCopy = new SqlBulkCopy(conn);
//表名称
bulkCopy.DestinationTableName = "Product";
bulkCopy.BatchSize = dt.Rows.Count;
conn.Open();

///新增的数据插入到DataTable中
for (int i = 0; i < 10000; i++)
{
DataRow dr = dt.NewRow();
dr[0] = Guid.NewGuid();
dr[1] = string.Format("商品", i);
dr[2] = (decimal)i;
dt.Rows.Add(dr);
}
if (dt != null && dt.Rows.Count != 0)
{
//将dt批量提交数据库，并插入
bulkCopy.WriteToServer(dt);
}
}
}
}
}