namespace Syaccp.Feast.ProcDAL.SqlServer
{
//系统命名空间
using System.Collections.Generic;
using System.Data;
using System;
using System.Data.SqlClient;
using System.Text;
using System.Configuration;
//第三方命名空间
using log4net;
using System.Reflection;
using Syaccp.Feast.Util;
using Syaccp.Feast.Model.Search.Proc;
/// <summary>
/// 实现对SQL数据库的操作的基类
/// </summary>
public class BaseDAL
{
#region Log日志对象
/// <summary>
/// 获得当前应用程序运行的方法,并返回对应方法的对象
/// </summary>
private static readonly ILog _log = LogManager.GetLogger(MethodBase.GetCurrentMethod().DeclaringType);
/// <summary>
/// Log日志对象
/// </summary>
public ILog Log
{
get { return _log; }
}
#endregion
#region 数据库的连接
/// <summary>
/// 数据库连接字符串(web.config来配置),可以动态更改connectionString支持多数据库.
/// </summary>
private static readonly string connectionString = ConfigurationManager.ConnectionStrings["Feast"].ToString();
/// <summary>
/// 数据库连接字符串(web.config来配置),可以动态更改connectionString支持多数据库.
/// </summary>
public static string ConnectionString
{
get
{
if (connectionString != null)
{
return BaseDAL.connectionString;
}
else
{
return @"server=192.168.0.100/sa;database=Feast;uid=xlive-1;pwd=xlive";
}
}
}
#endregion
#region 创建对象
/// <summary>
/// 创建对象
/// </summary>
/// <typeparam name="T">要创建的类名</typeparam>
/// <returns>对象</returns>
public static T CreateInstance<T>()
{
return Activator.CreateInstance<T>();
}
#endregion
#region 数据库的增删改查
/// <summary>
/// 验证一条数据是否存在(返回 1:存在;0:不存在 -2:异常)
/// </summary>
/// <param name="sql">要查询的T-SQL</param>
/// <returns> int</returns>
protected virtual int Exists(string sql)
{
using (SqlConnection connection = new SqlConnection(ConnectionString))
{
using (SqlCommand cmd = new SqlCommand(sql, connection))
{
try
{
connection.Open();
//cmd.Prepare();
int x = (int)cmd.ExecuteScalar();
return x >= 1 ? 1 : 0;
}
catch (SqlException e)
{
connection.Close();
Console.WriteLine(e.Message);
return -1;
}
}
}
}
/// <summary>
/// 验证一条数据是否存在(返回1:存在 0:不存在 -2:异常)
/// </summary>
/// <param name="paramList">sql语句所对应的参数</param>
/// <param name="sql">带占位符的sql语句例如:select * from user where id = @id</param>
/// <returns>int</returns>
protected virtual int Exists(List<SqlParameter> paramList, string sql)
{
using (SqlConnection connection = new SqlConnection(ConnectionString))
{
using (SqlCommand cmd = new SqlCommand(sql, connection))
{
cmd.Parameters.AddRange(paramList.ToArray());
try
{
connection.Open();
cmd.Prepare();
int x = (int)cmd.ExecuteScalar();
return x >= 1 ? 1 : 0;
}
catch (SqlException e)
{
connection.Close();
Console.WriteLine(e.Message);
return -1;
}
}
}
}
#region 增加
/// <summary>
/// 增加一条数据
/// </summary>
/// <param name="sql">要查询的T-SQL</param>
/// <returns> int</returns>
protected virtual int Insert(string sql)
{
using (SqlConnection connection = new SqlConnection(ConnectionString))
{
using (SqlCommand cmd = new SqlCommand(sql, connection))
{
try
{
connection.Open();
int x = (int)cmd.ExecuteNonQuery();
return x >= 1 ? 1 : 0;
}
catch (Exception e)
{
connection.Close();
Console.WriteLine(e.Message);
return -1;
}
}
}
}
/// <summary>
/// 增加一条数据(带参数)
/// </summary>
/// <param name="sql">要查询的T-SQL例如:insert into user values(@id)</param>
/// <param name="paramList">sql语句中需要的参数集合</param>
/// <returns> int</returns>
protected virtual int Insert(string sql,List<SqlParameter> paramList)
{
using (SqlConnection connection = new SqlConnection(ConnectionString))
{
using (SqlCommand cmd = new SqlCommand(sql, connection))
{
cmd.Parameters.AddRange(paramList.ToArray());
try
{
connection.Open();
cmd.Prepare();
int x = (int)cmd.ExecuteNonQuery();
return x >= 1 ? 1 : 0;
}
catch (Exception e)
{
connection.Close();
Console.WriteLine(e.Message);
return -1;
}
}
}
}
/// <summary>
/// 插入多条数据(带参数)
/// </summary>
/// <param name="paramList">存储过程和参数的集合</param>
/// <returns>插入数据的条数(int)</returns>
protected virtual int Inserts(IList<ProcModel> list)
{
using (SqlConnection connection = new SqlConnection(ConnectionString))
{
using (SqlCommand cmd = new SqlCommand())
{
connection.Open();
cmd.Connection = connection;
SqlTransaction tx = connection.BeginTransaction();
cmd.CommandType = System.Data.CommandType.StoredProcedure;
cmd.Transaction = tx;
try
{
int count = 0;
foreach (ProcModel item in list)
{
cmd.CommandText = item.key;
for (int i = 0; i < item.value.Count; i++)
{
cmd.Parameters.Add(item.value[i]);
}
count += cmd.ExecuteNonQuery();
}
tx.Commit();
return count >= list.Count ? 3 : 0;
}
catch (SqlException e)
{
tx.Rollback();
connection.Close();
Console.WriteLine(e.Message);
return -1;
}
}
}
}
/// <summary>
/// 插入多条数据
/// </summary>
/// <param name="sql">要查询的T-SQL集合</param>
/// <returns>插入数据的条数(int)</returns>
protected virtual int Inserts(IList<string> sql)
{
using (SqlConnection connection = new SqlConnection(ConnectionString))
{
using (SqlCommand cmd = new SqlCommand())
{
connection.Open();
cmd.Connection = connection;
SqlTransaction tx = connection.BeginTransaction();
cmd.Transaction = tx;
try
{
int count = 0;
for (int n = 0; n < sql.Count; n++)
{
string strsql = sql[n];
if (strsql.Trim().Length > 1)
{
cmd.CommandText = strsql;
count += cmd.ExecuteNonQuery();
}
}
tx.Commit();
return count >= sql.Count ? 3 : 0;
}
catch (SqlException e)
{
tx.Rollback();
connection.Close();
Console.WriteLine(e.Message);
return -1;
}
}
}
}
#endregion
#region 更新
/// <summary>
/// 更新一条数据
/// </summary>
/// <param name="sql">要执行的T-SQL</param>
/// <returns>int</returns>
protected virtual int Update(string sql)
{
using (SqlConnection connection = new SqlConnection(ConnectionString))
{
using (SqlCommand cmd = new SqlCommand(sql, connection))
{
try
{
connection.Open();
int x = (int)cmd.ExecuteNonQuery();
return x >= 1 ? 1 : 0;
}
catch (SqlException e)
{
connection.Close();
Console.WriteLine(e.Message);
return -1;
}
}
}
}
/// <summary>
/// 更新一条数据(需要参数)
/// </summary>
/// <param name="sql">要执行的T-SQL例如:update user set id=@id</param>
/// <param name="parameter">sql语句中需要的参数集合</param>
/// <returns>int</returns>
protected virtual int Update(string sql,List<SqlParameter> parameter)
{
using (SqlConnection connection = new SqlConnection(ConnectionString))
{
using (SqlCommand cmd = new SqlCommand(sql, connection))
{
cmd.Parameters.AddRange(parameter.ToArray());
try
{
connection.Open();
cmd.Prepare();
int x = (int)cmd.ExecuteNonQuery();
return x >= 1 ? 1 : 0;
}
catch (SqlException e)
{
connection.Close();
Console.WriteLine(e.Message);
return -1;
}
}
}
}
/// <summary>
/// 更新多条数据(带参数)
/// </summary>
/// <param name="paramList">存储过程名称和参数的集合</param>
/// <returns>返回受影响的行数(int)</returns>
protected virtual int Updates(IList<ProcModel> list)
{
using (SqlConnection connection = new SqlConnection(ConnectionString))
{
using (SqlCommand cmd = new SqlCommand())
{
connection.Open();
cmd.Connection = connection;
SqlTransaction tx = connection.BeginTransaction();
cmd.CommandType = System.Data.CommandType.StoredProcedure;
cmd.Transaction = tx;
try
{
int count = 0;
foreach (ProcModel item in list)
{
cmd.CommandText = item.key;
for (int i = 0; i < item.value.Count; i++)
{
cmd.Parameters.Add(item.value[i]);
}
count += cmd.ExecuteNonQuery();
}
tx.Commit();
return count >= list.Count ? 3 : 0;
}
catch (SqlException e)
{
tx.Rollback();
connection.Close();
Console.WriteLine(e.Message);
return -1;
}
}
}
}
/// <summary>
/// 更新多条数据
/// </summary>
/// <param name="sql">要更新的T-SQL集合</param>
/// <returns>返回受影响的行数(int)</returns>
protected virtual int Updates(IList<string> sql)
{
using (SqlConnection connection = new SqlConnection(ConnectionString))
{
using (SqlCommand cmd = new SqlCommand())
{
connection.Open();
cmd.Connection = connection;
SqlTransaction tx = connection.BeginTransaction();
cmd.Transaction = tx;
try
{
int count = 0;
for (int n = 0; n < sql.Count; n++)
{
string strsql = sql[n];
if (strsql.Trim().Length > 1)
{
cmd.CommandText = strsql;
count += cmd.ExecuteNonQuery();
}
}
tx.Commit();
return count > 0 ? 3 : 0;
}
catch (SqlException e)
{
tx.Rollback();
connection.Close();
Console.WriteLine(e.Message);
return -1;
}
}
}
}
#endregion
#region 删除
/// <summary>
/// 删除多条数据
/// </summary>
/// <param name="sql">要删除的T-SQL集合</param>
/// <returns>删除数据的条数(int)</returns>
protected virtual int Deletes(IList<string> sql)
{
using (SqlConnection connection = new SqlConnection(ConnectionString))
{
using (SqlCommand cmd = new SqlCommand())
{
connection.Open();
cmd.Connection = connection;
SqlTransaction tx = connection.BeginTransaction();
cmd.Transaction = tx;
try
{
int count = 0;
for (int n = 0; n < sql.Count; n++)
{
string strsql = sql[n];
if (strsql.Trim().Length > 1)
{
cmd.CommandText = strsql;
count += cmd.ExecuteNonQuery();
}
}
tx.Commit();
return count > sql.Count ? 3 : 0;
}
catch (SqlException e)
{
tx.Rollback();
connection.Close();
Console.WriteLine(e.Message);
return -1;
}
}
}
}
/// <summary>
/// 删除多条数据(带参数)
/// </summary>
/// <param name="sql">要删除的T-SQL集合</param>
/// <returns>删除数据的条数(int)</returns>
protected virtual int Deletes(IList<ProcModel> list)
{
using (SqlConnection connection = new SqlConnection(ConnectionString))
{
using (SqlCommand cmd = new SqlCommand())
{
connection.Open();
cmd.Connection = connection;
SqlTransaction tx = connection.BeginTransaction();
cmd.Transaction = tx;
try
{
int count = 0;
foreach (ProcModel item in list)
{
cmd.CommandText = item.key;
for (int i = 0; i < item.value.Count; i++)
{
cmd.Parameters.Add(item.value[i]);
}
count += cmd.ExecuteNonQuery();
}
tx.Commit();
return count >= list.Count ? 3 : 0;
}
catch (SqlException e)
{
tx.Rollback();
connection.Close();
Console.WriteLine(e.Message);
return -1;
}
}
}
}
/// <summary>
/// 删除一条数据
/// </summary>
/// <param name="sql">要查询的T-SQL</param>
/// <returns>bool</returns>
protected virtual int Delete(string sql)
{
using (SqlConnection connection = new SqlConnection(ConnectionString))
{
using (SqlCommand cmd = new SqlCommand(sql, connection))
{
try
{
connection.Open();
int x = (int)cmd.ExecuteNonQuery();
return x >= 1 ? 1 : 0;
}
catch (SqlException e)
{
connection.Close();
Console.WriteLine(e.Message);
return -1;
}
}
}
}
/// <summary>
/// 删除一条数据(带参数)
/// </summary>
/// <param name="sql">要查询的T-SQL例如:delete user where id=@id</param>
/// <param name="paramList">sql语句中需要的参数集合</param>
/// <returns>bool</returns>
protected virtual int Delete(string sql,List<SqlParameter> paramList)
{
using (SqlConnection connection = new SqlConnection(ConnectionString))
{
using (SqlCommand cmd = new SqlCommand(sql, connection))
{
cmd.Parameters.AddRange(paramList.ToArray());
try
{
connection.Open();
cmd.Prepare();
int x = (int)cmd.ExecuteNonQuery();
return x >= 1 ? 1 : 0;
}
catch (SqlException e)
{
connection.Close();
Console.WriteLine(e.Message);
return -1;
}
}
}
}
/// <summary>
/// 读取一行一列数据
/// </summary>
/// <param name="sql">要查询的T-SQL</param>
/// <returns>返回 object 类型</returns>
protected virtual object Select(string sql)
{
using (SqlConnection connection = new SqlConnection(ConnectionString))
{
using (SqlCommand cmd = new SqlCommand(sql, connection))
{
try
{
connection.Open();
object x = cmd.ExecuteScalar();
return x;
}
catch (SqlException e)
{
connection.Close();
throw e;
}
}
}
}
/// <summary>
/// 读取一行一列数据(带参数)
/// </summary>
/// <param name="sql">要查询的T-SQL</param>
/// <param name="paramList">sql语句中需要的参数集合</param>
/// <returns>返回 object 类型</returns>
protected virtual object Select(string sql,List<SqlParameter> paramList)
{
using (SqlConnection connection = new SqlConnection(ConnectionString))
{
using (SqlCommand cmd = new SqlCommand(sql, connection))
{
cmd.Parameters.AddRange(paramList.ToArray());
try
{
connection.Open();
cmd.Prepare();
object x = cmd.ExecuteScalar();
return x;
}
catch (SqlException e)
{
connection.Close();
throw e;
}
}
}
}
/// <summary>
/// 读取多条数据
/// </summary>
/// <typeparam name="T">泛型</typeparam>
/// <param name="sql">要查询的T-SQL</param>
/// <returns>返回 泛型集合</returns>
protected virtual IList<T> SelectIn<T>(string sql)
{
using (SqlConnection connection = new SqlConnection(ConnectionString))
{
using (SqlDataAdapter sda = new SqlDataAdapter(sql, connection))
{
DataSet ds = new DataSet();
sda.Fill(ds);
return DataSetToIList<T>(ds, 0);
}
}
}
/// <summary>
/// 读取多条数据(存储过程)
/// </summary>
/// <param name="sql">存储过程名称</param>
/// <param name="list">包含的参数如果没有为null</param>
/// <returns>返回 泛型集合</returns>
protected virtual IList<T> SelectIn<T>(string sql, List<SqlParameter> list)
{
using (SqlConnection connection = new SqlConnection(ConnectionString))
{
SqlCommand command = new SqlCommand(sql, connection);
command.CommandType = CommandType.StoredProcedure;
command.Parameters.AddRange(list.ToArray());
command.Prepare();
SqlDataAdapter sda = new SqlDataAdapter(command);
DataSet ds = new DataSet();
sda.Fill(ds);
return DataSetToIList<T>(ds, 0);
}
}
/// <summary>
/// 读取一行数据
/// </summary>
/// <param name="sql">存储过程名称</param>
/// <param name="list">包含的参数如果没有为null</param>
/// <returns>泛型集合实体</returns>
protected virtual T SelectByObject<T>(string sql,List<SqlParameter> list)
{
using (SqlConnection connection = new SqlConnection(ConnectionString))
{
SqlCommand command = new SqlCommand(sql, connection);
command.CommandType = CommandType.StoredProcedure;
command.Parameters.AddRange(list.ToArray());
command.Prepare();
SqlDataAdapter sda = new SqlDataAdapter(command);
DataSet ds = new DataSet();
sda.Fill(ds);
return DataSetToT<T>(ds, 0);
}
}
/// <summary>
/// 读取一行数据
/// </summary>
/// <typeparam name="T">泛型T-SQL</typeparam>
/// <param name="sql">要查询的T-SQL</param>
/// <returns>泛型集合实体</returns>
protected virtual T SelectByObject<T>(string sql)
{
using (SqlConnection connection = new SqlConnection(ConnectionString))
{
using (SqlDataAdapter sda = new SqlDataAdapter(sql, connection))
{
DataSet ds = new DataSet();
sda.Fill(ds);
return DataSetToT<T>(ds, 0);
}
}
}
/// <summary>
/// 操作多条数据的方法
/// </summary>
/// <param name="sql">要执行的T-SQL集合</param>
/// <returns>执行数据的条数(int)</returns>
protected virtual int Operate(IList<string> sql)
{
using (SqlConnection connection = new SqlConnection(ConnectionString))
{
using (SqlCommand cmd = new SqlCommand())
{
connection.Open();
cmd.Connection = connection;
SqlTransaction tx = connection.BeginTransaction();
cmd.Transaction = tx;
try
{
int count = 0;
for (int n = 0; n < sql.Count; n++)
{
string strsql = sql[n];
if (strsql.Trim().Length > 1)
{
cmd.CommandText = strsql;
count += cmd.ExecuteNonQuery();
}
}
tx.Commit();
return count >= sql.Count ? 3 : 0;
}
catch (SqlException e)
{
tx.Rollback();
connection.Close();
Console.WriteLine(e.Message);
return -1;
}
}
}
}
#endregion
#region 泛型和DataSet互换方法
/// <summary>
/// DataSet转换为泛型集合
/// </summary>
/// <typeparam name="T">泛型</typeparam>
/// <param name="p_DataSet">DataSet</param>
/// <param name="p_TableIndex">待转换数据表索引</param>
/// <returns>泛型集合</returns>
private static IList<T> DataSetToIList<T>(DataSet p_DataSet, int p_TableIndex)
{
if (p_DataSet == null || p_DataSet.Tables.Count < 0)
return null;
if (p_TableIndex > p_DataSet.Tables.Count - 1)
return null;
if (p_TableIndex < 0)
p_TableIndex = 0;
if (p_DataSet.Tables[p_TableIndex].Rows.Count <= 0)
return null;
DataTable p_Data = p_DataSet.Tables[p_TableIndex];
// 返回值初始化
IList<T> result = new List<T>();
for (int j = 0; j < p_Data.Rows.Count; j++)
{
T _t = (T)Activator.CreateInstance(typeof(T));
PropertyInfo[] propertys = _t.GetType().GetProperties();
foreach (PropertyInfo pi in propertys)
{
for (int i = 0; i < p_Data.Columns.Count; i++)
{
// 属性与字段名称一致的进行赋值
if (pi.Name.ToLower().Equals(p_Data.Columns[i].ColumnName.ToLower()))
{
// 数据库NULL值单独处理
if (p_Data.Rows[j][i] != DBNull.Value)
{
pi.SetValue(_t, p_Data.Rows[j][i], null);
}
else
pi.SetValue(_t, null, null);
break;
}
}
}
result.Add(_t);
}
return result;
}
/// <summary>
/// DataSet转换为实体类
/// </summary>
/// <typeparam name="T">实体类</typeparam>
/// <param name="p_DataSet">DataSet</param>
/// <param name="p_TableIndex">待转换数据表索引</param>
/// <returns>实体类</returns>
private static T DataSetToT<T>(DataSet p_DataSet, int p_TableIndex)
{
if (p_DataSet == null || p_DataSet.Tables.Count < 0)
return default(T);
if (p_TableIndex > p_DataSet.Tables.Count - 1)
return default(T);
if (p_TableIndex < 0)
p_TableIndex = 0;
if (p_DataSet.Tables[p_TableIndex].Rows.Count <= 0)
return default(T);
DataTable p_Data = p_DataSet.Tables[p_TableIndex];
// 返回值初始化
IList<T> result = new List<T>();
for (int j = 0; j < p_Data.Rows.Count; j++)
{
T _t = (T)Activator.CreateInstance(typeof(T));
PropertyInfo[] propertys = _t.GetType().GetProperties();
foreach (PropertyInfo pi in propertys)
{
for (int i = 0; i < p_Data.Columns.Count; i++)
{
// 属性与字段名称一致的进行赋值
if (pi.Name.ToLower().Equals(p_Data.Columns[i].ColumnName.ToLower()))
{
// 数据库NULL值单独处理
if (p_Data.Rows[j][i] != DBNull.Value)
{
pi.SetValue(_t, p_Data.Rows[j][i], null);
}
else
{
pi.SetValue(_t, null, null);
}
break;
}
}
}
result.Add(_t);
}
return result[0];
}
#endregion
#region 查询删除字符串拼接
/// <summary>
/// SQL语句的拼接
/// </summary>
/// <typeparam name="T">需要拼接SQL的实体名</typeparam>
/// <param name="sql">T-SQL</param>
/// <param name="_t">实体名</param>
/// <returns>返回一个完整的SQL语句</returns>
protected virtual string GetSqlBySelectData<T>(string sql, T _t)
{
StringBuilder sb = new StringBuilder(sql);
// SqlCommand comm = new SqlCommand();
//comm.Parameters.AddRange(new List<string>().ToArray());
char[] arr = { ' ', 'd', 'n', 'a' };
try
{
PropertyInfo[] propertys = _t.GetType().GetProperties();
foreach (PropertyInfo pi in propertys)
{
if (pi.GetValue(_t, null) != null)
{
if (pi.PropertyType.Name.Equals("Int32") || pi.PropertyType.Name.Equals("Double"))
sb.Append(pi.Name + "=" + pi.GetValue(_t, null) + " and ");
else
sb.Append(pi.Name + "='" + pi.GetValue(_t, null) + "' and ");
}
}
return sb.ToString().Trim(arr);
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
return sb.ToString().Trim(arr);
}
}
#endregion
#region 添加数据的SQL拼接
/// <summary>
/// 添加数据的SQL拼接(例如 insert into jobs values)
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="sql"></param>
/// <param name="_t"></param>
/// <returns>返回一个完整的添加SQL语句</returns>
protected virtual string GetSqlByAddData<T>(string sql, T _t)
{
try
{
char[] arr = { ' ', ',' };
StringBuilder sb = new StringBuilder(sql);
sb.Append("(");
PropertyInfo[] propertys = _t.GetType().GetProperties();
foreach (PropertyInfo pi in propertys)
{
if (pi.GetValue(_t, null) != null)
{
if (pi.PropertyType.Name.Equals("Int32") || pi.PropertyType.Name.Equals("Double"))
{
sb.Append(pi.GetValue(_t, null) + " , ");
}
else
{
string value = pi.GetValue(_t, null).ToString();
for (int i = 0; i < value.Length; i++)
{
int index1 = value.IndexOf("'");
int index2 = value.IndexOf('"');
if (index1 >= 0)
value = value.Replace("'", "");
if (index2 >= 0)
value = value.Replace('"', ' ');
}
sb.Append("'" + value + "' , ");
}
}
else
{
sb.Append("null");
}
}
string sum = sb.ToString().Trim(arr);
sum += ")";
return sum;
}
catch (Exception)
{
return sql;
}
}
#endregion
#region 修改数据的SQL拼接
/// <summary>
/// 修改数据的SQL拼接(例如 update jobs set )(只返回一个不带条件的T-SQL)
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="sql"></param>
/// <param name="_t"></param>
/// <returns>返回一个完整的修改SQL语句</returns>
protected virtual string GetSqlByUpdateData<T>(string sql, T _t)
{
string id_name = "";
string code_name = "";
Guid id = Guid.Empty;
int code = int.MinValue;
StringBuilder sb = new StringBuilder(sql);
char[] arr = { ' ', ',' };
try
{
PropertyInfo[] propertys = _t.GetType().GetProperties();
foreach (PropertyInfo pi in propertys)
{
if (pi.Name.IndexOf("_ID") < 0 && pi.Name.IndexOf("_Code") < 0)
{
if (pi.GetValue(_t, null) != null)
{
if (pi.PropertyType.Name.Equals("Int32") || pi.PropertyType.Name.Equals("Double"))
{
sb.Append(pi.Name + "=" + pi.GetValue(_t, null) + " , ");
}
else
{
string value = pi.GetValue(_t, null).ToString();
for (int i = 0; i < value.Length; i++)
{
int index1 = value.IndexOf("'");
int index2 = value.IndexOf('"');
if (index1 >= 0)
value = value.Replace("'", "☆");
if (index2 >= 0)
value = value.Replace('"', '★');
}
sb.Append(pi.Name + "='" + value + "' , ");
}
}
}
else
{
if (pi.Name.IndexOf("_ID") > 0)
{
id_name = pi.Name;
id = (Guid)pi.GetValue(_t, null);
}
else
{
code_name = pi.Name;
code = (int)pi.GetValue(_t, null);
}
}
}
string sum = sb.ToString().Trim(arr) + "where 1=1 ";
if (id != Guid.Empty)
sum += "and " + id_name + "='" + id + "'";
if (code > 0)
sum += "and " + code_name + "=" + code;
return sum;
}
catch (Exception)
{
return sql;
}
}
#endregion
#region 计算每周一是几号
/// <summary>
/// 计算每周一是几号
/// </summary>
/// <returns>返回DateTime对象</returns>
public DateTime GetDateTimeDayToWeek()
{
try
{
int day = DateTime.Now.Day;
int month = DateTime.Now.Month;
switch (DateTime.Now.DayOfWeek.ToString().ToLower())
{
case "monday":
return(new DateTime(DateTime.Now.Year, DateTime.Now.Month, (DateTime.Now.Day)));
case "tuesday":
if (day - 2 < 0)
{
if (month != 1)
return(new DateTime(DateTime.Now.Year, DateTime.Now.Month - 1, DateTime.DaysInMonth(DateTime.Now.Year, DateTime.Now.Month - 1) - 0));
else
return(new DateTime(DateTime.Now.Year - 1, 12, DateTime.DaysInMonth(DateTime.Now.Year, 12) - 0));
}
else
return(new DateTime(DateTime.Now.Year, DateTime.Now.Month, (DateTime.Now.Day - 1)));
case "wednesday":
if (day - 3 < 0)
{
if (month != 1)
return(new DateTime(DateTime.Now.Year, DateTime.Now.Month - 1, DateTime.DaysInMonth(DateTime.Now.Year, DateTime.Now.Month - 1) - 1));
else
return(new DateTime(DateTime.Now.Year - 1, 12, DateTime.DaysInMonth(DateTime.Now.Year, 12) - 1));
}
else
return(new DateTime(DateTime.Now.Year, DateTime.Now.Month, (DateTime.Now.Day - 2)));
case "thursday":
if (day - 4 < 0)
{
if (month != 1)
return(new DateTime(DateTime.Now.Year, DateTime.Now.Month - 1, DateTime.DaysInMonth(DateTime.Now.Year, DateTime.Now.Month - 1) - 2));
else
return(new DateTime(DateTime.Now.Year - 1, 12, DateTime.DaysInMonth(DateTime.Now.Year, 12) - 2));
}
else
return(new DateTime(DateTime.Now.Year, DateTime.Now.Month, (DateTime.Now.Day - 3)));
case "friday":
if (day - 5 < 0)
{
if (month != 1)
return(new DateTime(DateTime.Now.Year, DateTime.Now.Month - 1, DateTime.DaysInMonth(DateTime.Now.Year, DateTime.Now.Month - 1) - 3));
else
return(new DateTime(DateTime.Now.Year - 1, 12, DateTime.DaysInMonth(DateTime.Now.Year, 12) - 3));
}
else
return(new DateTime(DateTime.Now.Year, DateTime.Now.Month, (DateTime.Now.Day - 4)));
case "saturday":
if (day - 6 < 0)
{
if (month != 1)
return(new DateTime(DateTime.Now.Year, DateTime.Now.Month - 1, DateTime.DaysInMonth(DateTime.Now.Year, DateTime.Now.Month - 1) - 4));
else
return(new DateTime(DateTime.Now.Year - 1, 12, DateTime.DaysInMonth(DateTime.Now.Year, 12) - 4));
}
else
return(new DateTime(DateTime.Now.Year, DateTime.Now.Month, (DateTime.Now.Day - 5)));
case "sunday":
if (day - 7 < 0)
{
if (month != 1)
return(new DateTime(DateTime.Now.Year, DateTime.Now.Month - 1, DateTime.DaysInMonth(DateTime.Now.Year, DateTime.Now.Month - 1) - 5));
else
return(new DateTime(DateTime.Now.Year - 1, 12, DateTime.DaysInMonth(DateTime.Now.Year, 12) - 5));
}
else
return(new DateTime(DateTime.Now.Year, DateTime.Now.Month, (DateTime.Now.Day - 6)));
}
return new DateTime();
}
catch (Exception)
{
return new DateTime();
}
}
/// <summary>
/// 计算当前季度的第一天是几号
/// </summary>
/// <returns>返回DateTime对象</returns>
protected DateTime GetDateToQuarter()
{
switch (DateTime.Now.Month)
{
case 1:
case 2:
case 3:
return new DateTime(DateTime.Now.Year, 1, (1));
case 4:
case 5:
case 6:
return new DateTime(DateTime.Now.Year, 4, (1));
case 7:
case 8:
case 9:
return new DateTime(DateTime.Now.Year, 7, (1));
case 10:
case 11:
case 12:
return new DateTime(DateTime.Now.Year, 10, (1));
}
return new DateTime();
}
/// <summary>
/// 计算上个月是几号
/// </summary>
/// <returns>返回DateTime对象</returns>
protected DateTime GetDateToMonth()
{
int month = DateTime.Now.Month;
if (month == 1)
return new DateTime(DateTime.Now.Year - 1, 12, 1);
else
return new DateTime(DateTime.Now.Year, DateTime.Now.Month - 1, 1);
}
protected DateTime GetDateToMonthLast()
{
int month = DateTime.Now.Month;
if (month == 1)
return new DateTime(DateTime.Now.Year - 1, 12, DateTime.DaysInMonth(DateTime.Now.Year - 1, 12));
else
return new DateTime(DateTime.Now.Year, DateTime.Now.Month - 1, DateTime.DaysInMonth(DateTime.Now.Year - 1, DateTime.Now.Month - 1));
}
/// <summary>
/// 计算上两个月是几号
/// </summary>
/// <returns>返回DateTime对象</returns>
protected DateTime GetDateToMonthTwo()
{
int month = DateTime.Now.Month;
if (month == 2)
return new DateTime(DateTime.Now.Year - 1, 12, 1);
else if(month == 1)
return new DateTime(DateTime.Now.Year - 1, 11, 1);
else
return new DateTime(DateTime.Now.Year, DateTime.Now.Month - 2, 1);
}
#endregion
#endregion
}
}