C#：数据库通用访问类 SqlHelper
2024-08-24 12:51:45
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using System.Globalization;
using System.IO;
using System.Text.RegularExpressions;
using System.Xml;
using System.Reflection; using System.Configuration;
namespace CarolLib
{
public static class SqlHelper
{
#region Execute private static int commandTimeOut = string.IsNullOrEmpty(System.Configuration.ConfigurationManager.AppSettings["TimeOut"]) ? : int.Parse(System.Configuration.ConfigurationManager.AppSettings["TimeOut"]); public static SqlDataReader ExecuteReader(SqlConnection conn, String name, params SqlParameter[] parameters)
{
using (SqlCommand command = new SqlCommand(name, conn))
{
command.CommandTimeout = commandTimeOut;
command.CommandType = CommandType.StoredProcedure;
if (parameters != null)
{
command.Parameters.AddRange(parameters);
}
conn.Open();
SqlDataReader reader = command.ExecuteReader(CommandBehavior.CloseConnection);
return reader;
}
} public static SqlDataReader ExecuteReader(SqlConnection conn, String name, CommandType type, params SqlParameter[] parameters)
{
using (SqlCommand command = new SqlCommand(name, conn))
{
command.CommandTimeout = commandTimeOut;
command.CommandType = type;
if (parameters != null)
{
command.Parameters.AddRange(parameters);
}
conn.Open();
SqlDataReader reader = command.ExecuteReader(CommandBehavior.CloseConnection);
return reader;
}
} public static object ExecuteScalar(SqlConnection conn, String name, params SqlParameter[] parameters)
{
using (SqlCommand command = new SqlCommand(name, conn))
{ command.CommandTimeout = commandTimeOut;
command.CommandType = CommandType.StoredProcedure;
if (parameters != null)
{
command.Parameters.AddRange(parameters);
}
conn.Open();
object returnValue = command.ExecuteScalar();
conn.Close();
return returnValue;
}
} public static object ExecuteScalar(SqlConnection conn, string sql, string type, params SqlParameter[] parameters)
{
using (SqlCommand command = new SqlCommand(sql, conn))
{ command.CommandTimeout = commandTimeOut;
command.CommandType = CommandType.Text;
if (parameters != null)
{
command.Parameters.AddRange(parameters);
}
conn.Open();
object returnValue = command.ExecuteScalar();
conn.Close();
return returnValue;
}
} public static long ExecuteNonQuery(SqlConnection conn, String name, CommandType type, params SqlParameter[] parameters)
{
using (SqlCommand command = new SqlCommand(name, conn))
{ command.CommandType = type;
command.CommandTimeout = commandTimeOut;
if (parameters != null)
{
command.Parameters.AddRange(parameters);
}
conn.Open();
long returnValue = command.ExecuteNonQuery();
conn.Close();
return returnValue;
}
} public static long ExecuteNonQuery(SqlConnection conn, String name, params SqlParameter[] parameters)
{
try
{
using (SqlCommand command = new SqlCommand(name, conn))
{ command.CommandType = CommandType.StoredProcedure; command.CommandTimeout = commandTimeOut;
if (parameters != null)
{
command.Parameters.AddRange(parameters);
}
conn.Open();
long returnValue = command.ExecuteNonQuery();
conn.Close();
return returnValue;
}
}
catch (Exception ex)
{
ex.Data["name"] = name;
int i = ;
foreach (var para in parameters)
{
ex.Data["para" + i] = para.Value;
i++;
} throw;
}
} public static T ExecuteWithReturn<T>(SqlConnection conn, String name, params SqlParameter[] parameters)
{
using (SqlCommand command = new SqlCommand(name, conn))
{
command.CommandType = CommandType.StoredProcedure; command.CommandTimeout = commandTimeOut;
if (parameters != null)
{
command.Parameters.AddRange(parameters);
}
command.Parameters.Add(new SqlParameter("ReturnValue",
SqlDbType.Int, , ParameterDirection.ReturnValue,
false, , , string.Empty, DataRowVersion.Default, null));
conn.Open();
command.ExecuteNonQuery();
T returnValue = (T)command.Parameters["ReturnValue"].Value;
conn.Close();
return returnValue;
}
} public static DataSet ExecuteDataSet(SqlConnection conn, String name, params SqlParameter[] parameters)
{
using (SqlDataAdapter da = new SqlDataAdapter(name, conn))
{
using (SqlCommand command = new SqlCommand(name, conn))
{
if (parameters != null)
{
command.Parameters.AddRange(parameters);
}
DataSet ds = new DataSet(); da.SelectCommand = command;
da.Fill(ds); return ds;
}
}
} //返回SqlCommand
public static SqlCommand CreateCmd(string proName, SqlParameter[] prams, SqlConnection Conn)
{
SqlConnection SqlConn = Conn;
if (SqlConn.State.Equals(ConnectionState.Closed))
{
SqlConn.Open(); //关闭
}
SqlCommand Cmd = new SqlCommand();
Cmd.CommandType = CommandType.StoredProcedure;
Cmd.Connection = SqlConn;
Cmd.CommandText = proName;
if (prams != null)
{
foreach (SqlParameter Parameter in prams)
{
if (Parameter != null)
{
Cmd.Parameters.Add(Parameter);
}
}
}
return Cmd;
}
//重载返回SqlCommand
public static SqlCommand CreateCmd(string sqlStr, SqlParameter[] prams, SqlConnection Conn, CommandType Ctype)
{
SqlConnection SqlConn = Conn;
if (SqlConn.State.Equals(ConnectionState.Closed))
{
SqlConn.Open();
}
SqlCommand Cmd = new SqlCommand();
Cmd.CommandType = Ctype;
Cmd.Connection = SqlConn;
Cmd.CommandText = sqlStr;
if (prams != null)
{
foreach (SqlParameter parameter in prams)
{
if (parameter != null)
{
Cmd.Parameters.Add(parameter);
}
}
}
return Cmd;
} //返回DataTable
public static DataTable RunProGetTable(string proName, SqlParameter[] prams, SqlConnection Conn)
{
try
{
SqlCommand Cmd = CreateCmd(proName, prams, Conn);
SqlDataAdapter Da = new SqlDataAdapter();
DataSet Ds = new DataSet();
Da.SelectCommand = Cmd;
Da.Fill(Ds);
DataTable Dt = Ds.Tables[];
return Dt; }
catch (Exception ex)
{
throw ex;
}
finally
{
Conn.Close();
}
} public static DataTable RunProGetTable(string sqlStr, SqlParameter[] prams, SqlConnection Conn, CommandType Ctype)
{
try
{
SqlCommand Cmd = CreateCmd(sqlStr, prams, Conn, Ctype);
SqlDataAdapter Da = new SqlDataAdapter();
DataSet Ds = new DataSet();
Da.SelectCommand = Cmd;
Da.Fill(Ds);
DataTable Dt = Ds.Tables[];
return Dt;
}
catch (Exception ex)
{
throw ex;
}
finally
{
Conn.Close(); } } #endregion #region Create Parameter public static SqlParameter CreateNTextInParameter(String name, String s)
{
return CreateInParameter(name, SqlDbType.NText,
s != null ? s.Length : , s);
} public static SqlParameter CreateImageInParameter(String name, Byte[] bytes)
{
return CreateInParameter(name, SqlDbType.Image,
bytes != null ? bytes.Length : , bytes);
} public static SqlParameter CreateInParameter(String name, SqlDbType datatype, int size, Object value)
{
SqlParameter parameter = new SqlParameter();
parameter.ParameterName = name;
parameter.Direction = ParameterDirection.Input;
parameter.SqlDbType = datatype;
parameter.Size = size;
parameter.Value = value;
return parameter;
} public static SqlParameter CreateOutParameter(String name, SqlDbType datatype, int size)
{
SqlParameter parameter = new SqlParameter();
parameter.ParameterName = name;
parameter.Direction = ParameterDirection.Output;
parameter.SqlDbType = datatype;
parameter.Size = size;
return parameter;
} #endregion #region Ids public static object IDFromString(String id)
{
return id != null && id.Length > ? (object)Int64.Parse(id) : DBNull.Value;
} public static string CleanSearchString(string searchString)
{
if (string.IsNullOrEmpty(searchString))
return null; // Do wild card replacements
searchString = searchString.Replace("*", "%"); // Strip any markup characters
//searchString = Transforms.StripHtmlXmlTags(searchString); // Remove known bad SQL characters
searchString = Regex.Replace(searchString, "--|;|'|\"", " ", RegexOptions.Compiled | RegexOptions.Multiline); // Finally remove any extra spaces from the string
searchString = Regex.Replace(searchString, " {1,}", " ", RegexOptions.IgnoreCase | RegexOptions.Compiled | RegexOptions.Multiline); return searchString;
} public static List<int> PopulateReadersToIds(IDataReader dr, string key)
{
return PopulateReadersToIds<int>(dr, key);
} public static List<T> PopulateReadersToIds<T>(IDataReader dr, string key)
{
List<T> ids = new List<T>();
//Dictionary<T, bool> existsIds = new Dictionary<T, bool>();
while (dr.Read())
{
T id = (T)dr[key];
//if (!existsIds.ContainsKey(id))
//{
//existsIds.Add(id, true);
ids.Add(id);
//}
}
return ids;
} /// <summary>
/// convert datareader to dictionary
/// </summary>
/// <param name="dr"></param>
/// <param name="key"></param>
/// <param name="value"></param>
/// <returns></returns>
public static Dictionary<string, DateTime> PopulateReadersToDic(IDataReader dr, string key, string value)
{
return PopulateReadersToDic<string, DateTime>(dr, key, value);
} public static Dictionary<T1, T2> PopulateReadersToDic<T1, T2>(IDataReader dr, string key, string value)
{
Dictionary<T1, T2> result = new Dictionary<T1, T2>(); while (dr.Read())
{
T1 name = (T1)dr[key];
T2 date = (T2)dr[value]; result.Add(name, date); }
return result; } public static object ConvertIdsToXML<T>(string itemName, T[] ids)
{
string rootName = itemName + "s";
string idName = "i";
return ConvertIdsToXML<T>(rootName, itemName, idName, ids);
} public static object ConvertModelListToXML<T>(string itemName, IEnumerable<T> modelList)
{
if (modelList == null)
{
return DBNull.Value;
} StringWriter sw = new StringWriter();
XmlTextWriter writer = new XmlTextWriter(sw);
writer.WriteStartElement(itemName + "s"); var type = typeof(T);
if (type.IsValueType)
{//添加对值类型的支持
foreach (T model in modelList)
{
writer.WriteStartElement(itemName);
writer.WriteAttributeString(type.Name, type.IsEnum ? Convert.ToInt32(model).ToString() : model.ToString());
writer.WriteEndElement();
}
}
else
{
foreach (T model in modelList)
{
writer.WriteStartElement(itemName);
foreach (PropertyInfo p in type.GetProperties(BindingFlags.Public | BindingFlags.Instance))
{
try
{
if (p.PropertyType.IsGenericType)
{
var ut = Nullable.GetUnderlyingType(p.PropertyType);
if (ut != null && ut.IsEnum)
{
var v = p.GetValue(model, null);
if (v == null)
{
writer.WriteAttributeString(p.Name, string.Empty);
}
else
{
writer.WriteAttributeString(p.Name, Convert.ToString((int)v));
}
//writer.WriteAttributeString(p.Name, Convert.ToString((int?)p.GetValue(model, null)));
}
else
{
writer.WriteAttributeString(p.Name, Convert.ToString(p.GetValue(model, null)));
}
}
else if (p.PropertyType.IsEnum)
writer.WriteAttributeString(p.Name, Convert.ToString((int)p.GetValue(model, null)));
else
writer.WriteAttributeString(p.Name, Convert.ToString(p.GetValue(model, null)));
}
catch (Exception e)
{
throw e;
}
}
writer.WriteEndElement();
}
}
writer.WriteEndElement();
writer.Close();
return sw.ToString();
} public static object ConvertIdsToXML<T>(string rootName, string itemName, string idName, T[] ids)
{
if (ids == null)
return DBNull.Value; StringWriter sw = new StringWriter();
XmlTextWriter writer = new XmlTextWriter(sw);
writer.WriteStartElement(rootName);
foreach (T id in ids)
{
writer.WriteStartElement(itemName);
writer.WriteAttributeString(idName, typeof(T).IsEnum ? Convert.ToInt32(id).ToString() : id.ToString());
writer.WriteEndElement();
}
writer.WriteEndElement();
writer.Close();
return sw.ToString();
} #endregion #region SQL TypeSafe public static object GetSafeSqlDateTime(DateTime? date)
{
if (date == null)
return DBNull.Value;
return GetSafeSqlDateTime(date.Value);
} public static DateTime GetSafeSqlDateTime(DateTime date)
{
if (date < SqlDateTime.MinValue)
{
return (DateTime)SqlDateTime.MinValue;
}
if (date > SqlDateTime.MaxValue)
{
return (DateTime)SqlDateTime.MaxValue;
}
return date;
} public static string GetSafeSqlDateTimeFormat(DateTime date)
{
return date.ToString(CultureInfo.CurrentCulture.DateTimeFormat.SortableDateTimePattern);
} public static int GetSafeSqlInt(int i)
{
if (i <= ((int)SqlInt32.MinValue))
{
return (((int)SqlInt32.MinValue) + );
}
if (i >= ((int)SqlInt32.MaxValue))
{
return (((int)SqlInt32.MaxValue) - );
}
return i;
} public static object StringOrNull(string text)
{
if (string.IsNullOrEmpty(text))
{
return DBNull.Value;
}
return text;
} #endregion
}
}
最新文章
- 【Java EE 学习 29 下】【JDBC编程中操作Oracle数据库】【调用存储过程的方法】
- android应用刷新系统多媒体库（增加or删除多媒体文件）
- nginx查看安装了哪些模块
- AngularJS - 快速入门
- 【poj1009】 Edge Detection
- ApacheBench（ab）使用详解
- Java SortedSet接口
- IE9中jquery发生Object未定义原因及解决办法
- width(),innerHTML(),outerHTML()
- sublime text 几种常用插件
- 关于C++中的拷贝构造函数和赋值函数
- redis安装异常的解决的办法
- 用phantomjs进行web界面自动化测试的几个注意点
- 从输入url到页面展示到底发生了什么
- CSS Zoom属性
- spring读取properties的几种方式
- [AI]神经网络章1 神经网络基本工作原理
- 【noip模拟赛1】古韵之刺绣
- gulp css 压缩 合并
- js导出到word、json、excel、csv