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"]) ? 60 : 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 = 0;
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, 4, ParameterDirection.ReturnValue,
false, 0, 0, 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[0];
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[0];
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 : 16, s);
}
public static SqlParameter CreateImageInParameter(String name, Byte[] bytes)
{
return CreateInParameter(name, SqlDbType.Image,
bytes != null ? bytes.Length : 16, 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 > 0 ? (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) + 1);
}
if (i >= ((int)SqlInt32.MaxValue))
{
return (((int)SqlInt32.MaxValue) - 1);
}
return i;
}
public static object StringOrNull(string text)
{
if (string.IsNullOrEmpty(text))
{
return DBNull.Value;
}
return text;
}
#endregion
}
}
