using System;
using System.ComponentModel;
using System.Data;
using System.Data.SqlClient;
using System.IO;
using System.Reflection;
namespace RaywindStudio.DAL {
/// <summary>
/// MSSQL数据库操作类
/// </summary>
public static class SqlHelper {
/// <summary>
/// 是否写调试信息
/// 执行成功写到C:\\DebugSQL.txt；
/// 执行失败写到C:\\DebugTxt.txt；
/// </summary>
public static bool debug = false;
#region Select
/// <summary>
/// Select查表
/// </summary>
/// <param name="Columns">一条完整、直接执行的select语句</param>
/// <param name="TableName">一条完整、直接执行的select语句</param>
/// <param name="WP">SqlParameter</param>
/// <param name="sqlconn">一个SQL连接</param>
/// <returns>DataTable</returns>
public static DataTable SelectTable(string Columns, string TableName,
WPrm[] WP, SqlConnection sqlconn) {
return SelectTable(Columns, TableName, WP, "", sqlconn);
}
/// <summary>
/// Select查表
/// </summary>
/// <param name="Columns">一条完整、直接执行的select语句</param>
/// <param name="TableName">一条完整、直接执行的select语句</param>
/// <param name="WP">SqlParameter</param>
/// <param name="sqlconn">一个SQL连接</param>
/// <returns>DataTable</returns>
public static DataTable SelectTable(string Columns, string TableName,
WPrm WP, SqlConnection sqlconn) {
return SelectTable(Columns, TableName, new WPrm[] { WP }, "", sqlconn);
}
/// <summary>
/// Select查表
/// </summary>
/// <param name="Columns">一条完整、直接执行的select语句</param>
/// <param name="TableName">一条完整、直接执行的select语句</param>
/// <param name="sqlconn">一个SQL连接</param>
/// <returns>DataTable</returns>
public static DataTable SelectTable(string Columns, string TableName, SqlConnection sqlconn) {
return SelectTable(Columns, TableName, new WPrm(), sqlconn);
}
/// <summary>
/// Select查表
/// </summary>
/// <param name="Columns">一条完整、直接执行的select语句</param>
/// <param name="TableName">一条完整、直接执行的select语句</param>
/// <param name="ColumnsOrderByWithMode">排序列名加上ASC or DESC</param>
/// <param name="sqlconn">一个SQL连接</param>
/// <returns>DataTable</returns>
public static DataTable SelectTable(string Columns, string TableName, string ColumnsOrderByWithMode, SqlConnection sqlconn) {
return SelectTable(Columns, TableName, new WPrm(), ColumnsOrderByWithMode, sqlconn);
}
/// <summary>
/// Select查表
/// </summary>
/// <param name="Columns">一条完整、直接执行的select语句</param>
/// <param name="TableName">一条完整、直接执行的select语句</param>
/// <param name="ColumnsOrderByWithMode">排序列名加上ASC or DESC</param>
/// <param name="WP">SqlParameter</param>
/// <param name="sqlconn">一个SQL连接</param>
/// <returns>DataTable</returns>
public static DataTable SelectTable(string Columns, string TableName, WPrm WP,
string ColumnsOrderByWithMode, SqlConnection sqlconn) {
return SelectTable(Columns, TableName, new WPrm[] { WP },
ColumnsOrderByWithMode, sqlconn);
}
/// <summary>
/// Select查表
/// </summary>
/// <param name="Columns">一条完整、直接执行的select语句</param>
/// <param name="TableName">一条完整、直接执行的select语句</param>
/// <param name="ColumnsOrderByWithMode">排序列名加上ASC or DESC</param>
/// <param name="WPS">SqlParameter</param>
/// <param name="sqlconn">一个SQL连接</param>
/// <returns>DataTable</returns>
public static DataTable SelectTable(string Columns, string TableName, WPrm[] WPS,
string ColumnsOrderByWithMode, SqlConnection sqlconn) {
string sql = "Select " + Columns + " From " + TableName + " Where 1=1 ";
SqlParameter[] pr = new SqlParameter[WPS.Length];
if (WPS[0].SqlParam.ParameterName.Length > 0) sql += SqlWhereBuild(WPS, ref pr);
if (ColumnsOrderByWithMode.Length > 0) sql += " Order by " + ColumnsOrderByWithMode;
SqlCommand cmd = new SqlCommand(sql, sqlconn);
if (WPS[0].SqlParam.ParameterName.Length > 0) cmd.Parameters.AddRange(pr);
try {
if (sqlconn.State != ConnectionState.Open) sqlconn.Open();
using (DataTable dt = new DataTable()) {
using (SqlDataAdapter da = new SqlDataAdapter(cmd)) {
try {
debugSQL(cmd.CommandText);
da.Fill(dt);
return dt;
} catch (Exception ex) {
debugTxt(cmd.CommandText, ex);
throw new Exception("SelectTable:\n" + ex.Message);
}
}
}
} catch (Exception ex) {
debugTxt(cmd.CommandText, ex);
throw new Exception("SelectTable:\n" + ex.Message);
}
}
/// <summary>
/// Select查值
/// </summary>
/// <param name="Columns">一条完整、直接执行的select语句</param>
/// <param name="TableName">一条完整、直接执行的select语句</param>
/// <param name="sqlconn">一个SQL连接</param>
/// <param name="WPS">SqlParameter</param>
/// <returns>DataTable</returns>
public static object SelectValue(string Columns, string TableName, WPrm[] WPS, SqlConnection sqlconn) {
string sql = "Select " + Columns + " From " + TableName + " Where 1=1 ";
SqlParameter[] pr = new SqlParameter[WPS.Length];
if (WPS[0].SqlParam.ParameterName.Length > 0) sql += SqlWhereBuild(WPS, ref pr);
SqlCommand cmd = new SqlCommand(sql, sqlconn);
if (WPS[0].SqlParam.ParameterName.Length > 0) cmd.Parameters.AddRange(pr);
try {
if (sqlconn.State != ConnectionState.Open)
sqlconn.Open();
debugSQL(cmd.CommandText);
return cmd.ExecuteScalar();
} catch (Exception ex) {
debugTxt(cmd.CommandText, ex);
throw new Exception("SelectValue:\n" + ex.Message);
}
}
/// <summary>
/// Select查值
/// </summary>
/// <param name="Columns">一条完整、直接执行的select语句</param>
/// <param name="TableName">一条完整、直接执行的select语句</param>
/// <param name="sqlconn">一个SQL连接</param>
/// <param name="WP">SqlParameter</param>
/// <returns>DataTable</returns>
public static object SelectValue(string Columns, string TableName,
WPrm WP, SqlConnection sqlconn) {
return SelectValue(Columns, TableName, new WPrm[] { WP }, sqlconn);
}
#endregion
#region Insert
/// <summary>
/// 执行MSSQL插入表操作,默认列名：ColumnName=ParameterName.Replace("@","")
/// </summary>
/// <param name="TableName">表名称</param>
/// <param name="SP">SqlParameter</param>
/// <param name="sqlconn">一个SQL连接</param>
/// <returns>ExecuteNonQuery执行结果</returns>
public static int Insert(string TableName, SqlParameter[] SP, SqlConnection sqlconn) {
string sql = "Insert into " + TableName + "(";
for (int i = 0; i < SP.Length; i++)
sql += SP[i].ParameterName.Replace("@", "") + ",";
sql = sql.Substring(0, sql.Length - 1) + ") Values(";
for (int j = 0; j < SP.Length; j++)
sql += SP[j].ParameterName + ",";
sql = sql.Substring(0, sql.Length - 1) + ")";
SqlCommand cmd = new SqlCommand(sql, sqlconn);
cmd.Parameters.AddRange(SP);
try {
if (sqlconn.State != ConnectionState.Open)
sqlconn.Open();
debugSQL(cmd.CommandText);
return cmd.ExecuteNonQuery();
} catch (Exception ex) {
debugTxt(cmd.CommandText, ex);
throw new Exception("InsertCMD:ExecuteNonQuery\n" + ex.Message);
}
}
/// <summary>
/// 执行MSSQL插入表操作,默认列名：ColumnName=ParameterName.Replace("@","")
/// </summary>
/// <param name="TableName">表名称</param>
/// <param name="SP">SqlParameter</param>
/// <param name="sqlconn">一个SQL连接</param>
/// <returns>ExecuteNonQuery执行结果</returns>
public static int Insert(string TableName, SqlParameter SP, SqlConnection sqlconn) {
return Insert(TableName, new SqlParameter[] { SP }, sqlconn);
}
/// <summary>
/// 执行MSSQL插入表操作,默认列名：ColumnName=ParameterName.Replace("@","")
/// </summary>
/// <param name="TableName">表名称</param>
/// <param name="SPS">SqlParameter</param>
/// <param name="ColumnValues">ColumnValue键值对：弥补SqlParameter无法调用getdate()、year()等内部函数的不足。
/// 前后分别为Column和Value，添加在insert语句的column和value部分</param>
/// <param name="sqlconn">一个SQL连接</param>
/// <returns>ExecuteNonQuery执行结果</returns>
public static int Insert(string TableName, SqlParameter[] SPS,
string[,] ColumnValues, SqlConnection sqlconn) {
string sql = "Insert into " + TableName + "(";
for (int i = 0; i < SPS.Length; i++)
sql += SPS[i].ParameterName.Replace("@", "") + ",";
for (int ii = 0; ii < ColumnValues.GetLength(0); ii++)
sql += ColumnValues[ii, 0] + ",";
sql = sql.Substring(0, sql.Length - 1) + ") Values(";
for (int j = 0; j < SPS.Length; j++)
sql += SPS[j].ParameterName + ",";
for (int jj = 0; jj < ColumnValues.GetLength(0); jj++)
sql += ColumnValues[jj, 1] + ",";
sql = sql.Substring(0, sql.Length - 1) + ")";
SqlCommand cmd = new SqlCommand(sql, sqlconn);
cmd.Parameters.AddRange(SPS);
try {
if (sqlconn.State != ConnectionState.Open)
sqlconn.Open();
debugSQL(cmd.CommandText);
return cmd.ExecuteNonQuery();
} catch (Exception ex) {
debugTxt(cmd.CommandText, ex);
throw new Exception("InsertCMD:ExecuteNonQuery\n" + ex.Message);
}
}
#endregion
#region Update
/// <summary>
/// 执行MSSQL更新表操作,默认列名：ColumnName=ParameterName.Replace("@","")
/// </summary>
/// <param name="TableName">表名称</param>
/// <param name="SPS">SqlParameter</param>
/// <param name="sqlconn">一个SQL连接</param>
/// <param name="WPS">查询条件 默认列名：ColumnName=ParameterName.Replace("@","")</param>
/// <returns>ExecuteNonQuery执行结果</returns>
public static int Update(string TableName, SqlParameter[] SPS, WPrm[] WPS, SqlConnection sqlconn) {
string sql = "Update " + TableName + " Set ";
for (int i = 0; i < SPS.Length; i++)
sql += SPS[i].ParameterName.Replace("@", "")
+ "=" + SPS[i].ParameterName + ",";
sql = sql.Substring(0, sql.Length - 1)
+ " Where 1=1 ";
SqlParameter[] pr = new SqlParameter[WPS.Length];
if (WPS[0].SqlParam.ParameterName.Length > 0) sql += SqlWhereBuild(WPS, ref pr);
SqlCommand cmd = new SqlCommand(sql, sqlconn);
cmd.Parameters.AddRange(SPS);
if (WPS[0].SqlParam.ParameterName.Length > 0) cmd.Parameters.AddRange(pr);
try {
if (sqlconn.State != ConnectionState.Open)
sqlconn.Open();
debugSQL(cmd.CommandText);
return cmd.ExecuteNonQuery();
} catch (Exception ex) {
debugTxt(cmd.CommandText, ex);
throw new Exception("Update:ExecuteNonQuery\n" + ex.Message);
}
}
/// <summary>
/// 执行MSSQL更新表操作,默认列名：ColumnName=ParameterName.Replace("@","")
/// </summary>
/// <param name="TableName">表名称</param>
/// <param name="SPS">SqlParameter</param>
/// <param name="sqlconn">一个SQL连接</param>
/// <param name="WPS">查询条件 默认列名：ColumnName=ParameterName.Replace("@","")</param>
/// <returns>ExecuteNonQuery执行结果</returns>
public static int Update(string TableName, SqlParameter SPS, WPrm WPS, SqlConnection sqlconn) {
return Update(TableName, new SqlParameter[] { SPS }, new WPrm[] { WPS }, sqlconn);
}
/// <summary>
/// 执行MSSQL更新表操作,默认列名：ColumnName=ParameterName.Replace("@","")
/// </summary>
/// <param name="TableName">表名称</param>
/// <param name="SPS">SqlParameter</param>
/// <param name="ColumnValues">ColumnValue键值对：弥补SqlParameter无法调用getdate()、year()等内部函数的不足。
/// 前后分别为Column和Value，添加在insert语句的column和value部分</param>
/// <param name="WPS">SqlParameter</param>
/// <param name="ConditionsColumnValues">ColumnValue键值对：弥补SqlParameter无法调用getdate()、year()等内部函数的不足。
/// 前后分别为Column和Value，添加在insert语句的column和value部分</param>
/// <param name="sqlconn">一个SQL连接</param>
/// <returns>ExecuteNonQuery执行结果</returns>
public static int Update(string TableName, SqlParameter[] SPS, string[,] ColumnValues, WPrm[] WPS,
string[,] ConditionsColumnValues, SqlConnection sqlconn) {
string sql = "Update " + TableName + " Set ";
for (int i = 0; i < SPS.Length; i++)
sql += SPS[i].ParameterName.Replace("@", "")
+ "=" + SPS[i].ParameterName + ",";
for (int j = 0; j < ColumnValues.GetLength(0); j++)
sql += ColumnValues[j, 0] + "=" + ColumnValues[j, 1] + ",";
sql = sql.Substring(0, sql.Length - 1)
+ " Where 1=1 ";
SqlParameter[] pr = new SqlParameter[WPS.Length];
if (WPS[0].SqlParam.ParameterName.Length > 0) sql += SqlWhereBuild(WPS, ref pr);
for (int j = 0; j < ConditionsColumnValues.GetLength(0); j++)
sql += " and " + ConditionsColumnValues[j, 0] + "=" + ConditionsColumnValues[j, 1];
SqlCommand cmd = new SqlCommand(sql, sqlconn);
cmd.Parameters.AddRange(SPS);
if (WPS[0].SqlParam.ParameterName.Length > 0) cmd.Parameters.AddRange(pr);
try {
if (sqlconn.State != ConnectionState.Open)
sqlconn.Open();
debugSQL(cmd.CommandText);
return cmd.ExecuteNonQuery();
} catch (Exception ex) {
debugTxt(cmd.CommandText, ex);
throw new Exception("Update:ExecuteNonQuery\n" + ex.Message);
}
}
/// <summary>
/// 执行MSSQL更新表操作,默认列名：ColumnName=ParameterName.Replace("@","")
/// </summary>
/// <param name="TableName">表名称</param>
/// <param name="SP">SqlParameter</param>
/// <param name="ColumnValues">ColumnValue键值对：弥补SqlParameter无法调用getdate()、year()等内部函数的不足。
/// 前后分别为Column和Value，添加在insert语句的column和value部分</param>
/// <param name="WP">SqlParameter</param>
/// <param name="ConditionsColumnValues">ColumnValue键值对：弥补SqlParameter无法调用getdate()、year()等内部函数的不足。
/// 前后分别为Column和Value，添加在insert语句的column和value部分</param>
/// <param name="sqlconn">一个SQL连接</param>
/// <returns>ExecuteNonQuery执行结果</returns>
public static int Update(string TableName, SqlParameter SP, string[,] ColumnValues, WPrm WP,
string[,] ConditionsColumnValues, SqlConnection sqlconn) {
return Update(TableName, new SqlParameter[] { SP }, ColumnValues,
new WPrm[] { WP }, ConditionsColumnValues, sqlconn);
}
#endregion
#region Delete
/// <summary>
/// 执行MSSQL删除表内数据操作
/// </summary>
/// <param name="TableName">表名称</param>
/// <param name="sqlconn">一个SQL连接</param>
/// <param name="WPS">SqlParameter</param>
/// <returns>ExecuteNonQuery执行结果</returns>
public static int Delete(string TableName, WPrm[] WPS, SqlConnection sqlconn) {
string sql = "Delete From " + TableName + " Where 1=1 ";
SqlParameter[] pr = new SqlParameter[WPS.Length];
if (WPS[0].SqlParam.ParameterName.Length > 0) sql += SqlWhereBuild(WPS, ref pr);
SqlCommand cmd = new SqlCommand(sql, sqlconn);
if (WPS[0].SqlParam.ParameterName.Length > 0) cmd.Parameters.AddRange(pr);
try {
if (sqlconn.State != ConnectionState.Open)
sqlconn.Open();
debugSQL(cmd.CommandText);
return cmd.ExecuteNonQuery();
} catch (Exception ex) {
debugTxt(cmd.CommandText, ex);
throw new Exception("Delete:ExecuteNonQuery\n" + ex.Message);
}
}
/// <summary>
/// 执行MSSQL删除表内数据操作
/// </summary>
/// <param name="TableName">表名称</param>
/// <param name="sqlconn">一个SQL连接</param>
/// <param name="WPS">SqlParameter</param>
/// <returns>ExecuteNonQuery执行结果</returns>
public static int Delete(string TableName, WPrm WPS, SqlConnection sqlconn) {
return Delete(TableName, new WPrm[] { WPS }, sqlconn);
}
#endregion
#region Exec Proc
/// <summary>
/// 执行存储过程，无返回值
/// </summary>
/// <param name="procName">存储过程名称 </param>
/// <param name="parameters">SqlParameters</param>
/// <param name="sqlconn">一个SQL连接</param>
/// <returns>ExecuteNonQuery执行结果</returns>
public static void ExecProcNonReturn(string procName, SqlParameter[] parameters, SqlConnection sqlconn) {
SqlCommand cmd = new SqlCommand(procName, sqlconn);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddRange(parameters);
try {
if (sqlconn.State != ConnectionState.Open)
sqlconn.Open();
debugSQL(cmd.CommandText);
cmd.ExecuteNonQuery();
} catch (Exception ex) {
debugTxt(cmd.CommandText, ex);
throw new Exception("ExecProcNonReturn:ExecuteNonQuery\n" + ex.Message);
}
}
/// <summary>
/// 执行存储过程，无返回值
/// </summary>
/// <param name="procName">存储过程名称 </param>
/// <param name="parameters">SqlParameter</param>
/// <param name="sqlconn">一个SQL连接</param>
/// <returns>ExecuteNonQuery执行结果</returns>
public static void ExecProcNonReturn(string procName, SqlParameter parameters, SqlConnection sqlconn) {
ExecProcNonReturn(procName, new SqlParameter[] { parameters }, sqlconn);
}
/// <summary>
/// 执行存储过程，并直接返回执行的结果
/// </summary>
/// <param name="procName">存储过程名称 </param>
/// <param name="parameters">SqlParameter</param>
/// <param name="sqlconn">一个SQL连接</param>
/// <returns>ExecuteNonQuery执行结果</returns>
public static object ExecProc(string procName, SqlParameter[] parameters, SqlConnection sqlconn) {
SqlCommand cmd = new SqlCommand(procName, sqlconn);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddRange(parameters);
try {
if (sqlconn.State != ConnectionState.Open)
sqlconn.Open();
debugSQL(cmd.CommandText);
return cmd.ExecuteScalar();
} catch (Exception ex) {
debugTxt(cmd.CommandText, ex);
throw new Exception("ExecProc:ExecuteScalar\n" + ex.Message);
}
}
/// <summary>
/// 执行存储过程，并直接返回执行的结果
/// </summary>
/// <param name="procName">存储过程名称 </param>
/// <param name="parameters">SqlParameter</param>
/// <param name="sqlconn">一个SQL连接</param>
/// <returns>ExecuteNonQuery执行结果</returns>
public static object ExecProc(string procName, SqlParameter parameters, SqlConnection sqlconn) {
return ExecProc(procName, new SqlParameter[] { parameters }, sqlconn);
}
/// <summary>
/// 执行存储过程,带一个返回参数并返回此参数的执行结果
/// <para>Example:</para>
/// <para>SqlParameter[] sps = new SqlParameter[] {</para>
/// <para> new SqlParameter("@stageID", stgID), new SqlParameter("@sheepCode", sheepCode),</para>
/// <para> };</para>
/// <para>SqlParameter spout = new SqlParameter() {</para>
/// <para> ParameterName = "@ret", Value = "", Direction = ParameterDirection.Output,</para>
/// <para> DbType = DbType.String, Size = 4000</para>
/// <para>};</para>
/// <para>object obj = SqlHelper.ExecProcWithOut("pGetPgkNO", sps, spout, CFG.sqlconn);</para>
/// </summary>
/// <param name="procName">存储过程名称</param>
/// <param name="parameters">SqlParameter</param>
/// <param name="parameter_out">SqlParameter.Direction = ParameterDirection.Output;</param>
/// <param name="sqlconn">一个SQL连接</param>
/// <returns>ExecuteNonQuery执行结果</returns>
public static object ExecProcWithOut(string procName, SqlParameter[] parameters,
SqlParameter parameter_out, SqlConnection sqlconn) {
SqlCommand cmd = new SqlCommand(procName, sqlconn);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddRange(parameters);
cmd.Parameters.Add(parameter_out);
try {
if (sqlconn.State != ConnectionState.Open)
sqlconn.Open();
cmd.ExecuteNonQuery();
debugSQL(cmd.CommandText);
return parameter_out.Value;
} catch (Exception ex) {
debugTxt(cmd.CommandText, ex);
throw new Exception("ExecProc:ExecuteNonQuery\n" + ex.Message);
}
}
/// <summary>
/// 执行存储过程,带一个返回参数并返回此参数的执行结果
/// </summary>
/// <param name="procName">存储过程名称 </param>
/// <param name="parameters">SqlParameter</param>
/// <param name="parameter_out">SqlParameter.Direction = ParameterDirection.Output;</param>
/// <param name="sqlconn">一个SQL连接</param>
/// <returns>ExecuteNonQuery执行结果</returns>
public static object ExecProcWithOut(string procName, SqlParameter parameters,
SqlParameter parameter_out, SqlConnection sqlconn) {
return ExecProcWithOut(procName, new SqlParameter[] { parameters }, parameter_out, sqlconn);
}
/// <summary>
/// 执行存储过程，无返回值
/// </summary>
/// <param name="procName">存储过程名称 </param>
/// <param name="sqlconn">一个SQL连接</param>
/// <returns>ExecuteNonQuery执行结果</returns>
public static void ExecProcNonReturn(string procName, SqlConnection sqlconn) {
ExecProcNonReturn(procName, new SqlParameter[] { }, sqlconn);
}
/// <summary>
/// 执行存储过程，并直接返回执行的结果
/// </summary>
/// <param name="procName">存储过程名称 </param>
/// <param name="sqlconn">一个SQL连接</param>
/// <returns>ExecuteNonQuery执行结果</returns>
public static object ExecProc(string procName, SqlConnection sqlconn) {
return ExecProc(procName, new SqlParameter[] { }, sqlconn);
}
/// <summary>
/// 执行存储过程,带一个返回参数并返回此参数的执行结果
/// </summary>
/// <param name="procName">存储过程名称 </param>
/// <param name="parameter_out">SqlParameter.Direction = ParameterDirection.Output;</param>
/// <param name="sqlconn">一个SQL连接</param>
/// <returns>ExecuteNonQuery执行结果</returns>
public static object ExecProcWithOut(string procName, SqlParameter parameter_out, SqlConnection sqlconn) {
return ExecProcWithOut(procName, new SqlParameter[] { }, parameter_out, sqlconn);
}
#endregion
#region Debug
private static void debugSQL(string sql) {
if (debug) {
StreamWriter sw = new StreamWriter("C:\\DebugSQL.txt", true);
sw.WriteLine(DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss.fff") + "\t" + sql);
sw.AutoFlush = true;
sw.Close();
sw.Dispose();
}
}
private static void debugTxt(string sql, Exception ee) {
if (debug) {
StreamWriter sw = new StreamWriter("C:\\DebugTxt.txt", true);
sw.WriteLine(DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss.fff") + "\t" + sql + "\n" + ee.ToString());
sw.AutoFlush = true;
sw.Close();
sw.Dispose();
}
}
#endregion
#region Distinct
/// <summary>
/// 从DataRow集合中排除重复项，并返回一个DataTable
/// </summary>
/// <param name="drs">DataRow集合</param>
/// <param name="ColumnName">筛选的条件列名</param>
/// <param name="DataTableschemaClone">要将结果装入的空表的架构</param>
/// <returns>DataTable</returns>
public static DataTable DistInctTable(DataRow[] drs, string ColumnName, DataTable DataTableschemaClone) {
try {
DataTable dts = DataTableschemaClone.Clone();
foreach (DataRow dr in drs)
dts.ImportRow(dr);
for (int i = dts.Rows.Count - 1; i >= 0; i--)
if (dts.Select(ColumnName + "='" + dts.Rows[i][ColumnName].ToString() + "'").Length > 1)
dts.Rows.RemoveAt(i);
dts.AcceptChanges();
return dts;
} catch (Exception ex) {
throw new Exception("DistInctTable(From DataRow):\n" + ex.Message);
}
}
/// <summary>
/// 从DataTable中排除重复行，并返回一个DataTable
/// </summary>
/// <param name="dt">源DataTable</param>
/// <param name="ColumnName">筛选的条件列名</param>
/// <returns>DataTable</returns>
public static DataTable DistInctTable(DataTable dt, string ColumnName) {
try {
for (int i = dt.Rows.Count - 1; i >= 0; i--)
if (dt.Select(ColumnName + "='" + dt.Rows[i][ColumnName].ToString() + "'").Length > 1)
dt.Rows.RemoveAt(i);
dt.AcceptChanges();
return dt;
} catch (Exception ex) {
throw new Exception("DistInctTable(From DataTable):\n" + ex.Message);
}
}
#endregion
private static string SqlWhereBuild(WPrm[] wps, ref SqlParameter[] sps) {
if (wps[0].SqlParam.ParameterName.Length == 0) return "";
string sql = "";
for (int i = 0; i < wps.Length; i++) {
WPrm wp = wps[i];
sps[i] = wp.SqlParam;
switch (wp.SqlOperator) {
case OP.Like:
sql += " and " + wp.SqlParam.ParameterName.Replace("@", "") + " like '%" + wp.SqlParam.ParameterName + "%'";
break;
case OP.Null:
sql += " and " + wp.SqlParam.ParameterName.Replace("@", "") + " is null ";
break;
case OP.NNul:
sql += " and not " + wp.SqlParam.ParameterName.Replace("@", "") + " is null ";
break;
case OP.In:
sql += " and " + wp.SqlParam.ParameterName.Replace("@", "") + " in (" + wp.SqlParam.ParameterName + ")";
break;
default:
sql += " and " + wp.SqlParam.ParameterName.Replace("@", "")
+ WPrm.GetDesc(wp.SqlOperator) + wp.SqlParam.ParameterName;
break;
////case OP.Equ:
//// sql += " and " + wp.SqlParam.ParameterName.Replace("@", "") + "=" + wp.SqlParam.ParameterName;
//// break;
////case OP.UE:
//// sql += " and " + wp.SqlParam.ParameterName.Replace("@", "") + "<>" + wp.SqlParam.ParameterName;
//// break;
////case OP.More:
//// sql += " and " + wp.SqlParam.ParameterName.Replace("@", "") + ">" + wp.SqlParam.ParameterName;
//// break;
////case OP.MoE:
//// sql += " and " + wp.SqlParam.ParameterName.Replace("@", "") + ">=" + wp.SqlParam.ParameterName;
//// break;
////case OP.Less:
//// sql += " and " + wp.SqlParam.ParameterName.Replace("@", "") + "<" + wp.SqlParam.ParameterName;
//// break;
////case OP.LoE:
//// sql += " and " + wp.SqlParam.ParameterName.Replace("@", "") + "<=" + wp.SqlParam.ParameterName;
//// break;
}
}
return sql;
}
}
#region ConditionStringBuild
public class WPrm {
public SqlParameter SqlParam;
public OP SqlOperator;
public WPrm() {
SqlParam = new SqlParameter();
SqlOperator = OP.Equ;
}
/// <summary>
/// WhereParam构造
/// </summary>
/// <param name="SqlParamName">必须以@符号开始，否则将自动在开头加@</param>
/// <param name="SqlParamValue"></param>
/// <param name="opt"></param>
public WPrm(string SqlParamName, object SqlParamValue, OP opt = OP.Equ) {
if (!SqlParamName.StartsWith("@"))
SqlParamName = "@" + SqlParamName;
SqlParam = new SqlParameter(SqlParamName, SqlParamValue);
SqlOperator = opt;
}
/// <summary>
/// 获取enum对象的Description属性
/// </summary>
/// <param name="pEnum">pEnum对象</param>
/// <returns></returns>
public static string GetDesc(Enum pEnum) {
FieldInfo fi = pEnum.GetType().GetField(pEnum.ToString());
DescriptionAttribute[] arrDesc = (DescriptionAttribute[])fi.GetCustomAttributes(typeof(DescriptionAttribute), false);
return arrDesc[0].Description;
}
}
/// <summary>
/// SQL条件运算符
/// </summary>
public enum OP {
/// <summary>
/// 等于
/// </summary>
[Description("=")] Equ = 0,
/// <summary>
/// 大于
/// </summary>
[Description(">")] More = 1,
/// <summary>
/// 大于等于
/// </summary>
[Description(">=")] MoE = 2,
/// <summary>
/// 小于
/// </summary>
[Description("<")] Less = 3,
/// <summary>
/// 小于等于
/// </summary>
[Description("<=")] LoE = 4,
/// <summary>
/// 不等于
/// </summary>
[Description("<>")] UE = 5,
/// <summary>
/// like
/// </summary>
[Description("like")] Like = 6,
/// <summary>
/// is null
/// </summary>
[Description("is null")] Null = 7,
/// <summary>
/// not is null
/// </summary>
[Description("not is null")] NNul = 8,
/// <summary>
/// in
/// </summary>
[Description("in")] In = 9
};
#endregion
}
