/// <summary>
///根据id修改一条数据(返)修改结果）
/// </summary>
public bool Update(Models.DynamicPicture Models)
{
string sqlStr = " update [MS_DynamicPicture] set CREATE_GUID=@CREATE_GUID,STATE=@STATE,LAST_CHANGE_TIME=@LAST_CHANGE_TIME,LAST_CHANGE_IP=@LAST_CHANGE_IP,LAST_CHANGE_GUID=@LAST_CHANGE_GUID where id = @id";
using (var connection = new ConnectionCode().GetConnection())
{
return(connection.Execute(sqlStr, Models) > 0);
}
}
/// <summary>
///根据id删除一条数据(返)修改结果）
/// </summary>
public bool Delete(int id)
{
string sqlStr = " delete [MS_DynamicPicture] where id = @id";
using (var connection = new ConnectionCode().GetConnection())
{
return(connection.Execute(sqlStr, new{ id = id }) > 0);
}
}
/// <summary>
/// 取所有数据的数量
/// </summary>
public int GetCount()
{
string sqlStr = " select count(*) from [MS_DynamicPicture] ";
using (var connection = new ConnectionCode().GetConnection())
{
return(connection.Query <int>(sqlStr).SingleOrDefault());
}
}
/// <summary>
///添加一条数据(返)插入的数据的id）
/// </summary>
public int Add(Models.DynamicPicture Models)
{
string sqlStr = " insert into [MS_DynamicPicture](CREATE_GUID,STATE,LAST_CHANGE_TIME,LAST_CHANGE_IP,LAST_CHANGE_GUID) values(@CREATE_GUID,@STATE,@LAST_CHANGE_TIME,@LAST_CHANGE_IP,@LAST_CHANGE_GUID);select @@identity ";
using (var connection = new ConnectionCode().GetConnection())
{
return(connection.Query <int>(sqlStr, Models).SingleOrDefault());
}
}
/// <summary>
///根据id修改一条数据(返)修改结果）
/// </summary>
public bool Update(Models.VisitLog Models)
{
string sqlStr = " update [MS_VisitLog] set ENTRY_URL=@ENTRY_URL,VISIT_IP=@VISIT_IP,VISIT_CITY=@VISIT_CITY,SESSION_GUID=@SESSION_GUID,STATE=@STATE where id = @id";
using (var connection = new ConnectionCode().GetConnection())
{
return(connection.Execute(sqlStr, Models) > 0);
}
}
/// <summary>
///根据id取得数据模型(返)一个Models）
/// </summary>
public Models.DynamicPicture GetModels(int id)
{
string sqlStr = " select top 1 * from [MS_DynamicPicture] where id = @id ";
using (var connection = new ConnectionCode().GetConnection())
{
return(connection.Query <Models.DynamicPicture>(sqlStr, new{ id = id }).SingleOrDefault());
}
}
/// <summary>
///根据id修改一条数据(返)修改结果）
/// </summary>
public bool Update(Models.OperationLog Models)
{
string sqlStr = " update [MS_OperationLog] set GUID=@GUID,IP_ADDRESS=@IP_ADDRESS,CITY=@CITY,OPERATION_URL=@OPERATION_URL,PARAM_STRING=@PARAM_STRING,RESULT_STRING=@RESULT_STRING,STATE=@STATE where id = @id";
using (var connection = new ConnectionCode().GetConnection())
{
return(connection.Execute(sqlStr, Models) > 0);
}
}
/// <summary>
///添加一条数据(返)插入的数据的id）
/// </summary>
public int Add(Models.VisitLog Models)
{
string sqlStr = " insert into [MS_VisitLog](ENTRY_URL,VISIT_IP,VISIT_CITY,SESSION_GUID,STATE) values(@ENTRY_URL,@VISIT_IP,@VISIT_CITY,@SESSION_GUID,@STATE);select @@identity ";
using (var connection = new ConnectionCode().GetConnection())
{
return(connection.Query <int>(sqlStr, Models).SingleOrDefault());
}
}
/// <summary>
///根据id修改一条数据(返)修改结果）
/// </summary>
public bool Update(Models.Class Models)
{
string sqlStr = " update [MS_Class] set CREATE_GUID=@CREATE_GUID,CLASS_NAME=@CLASS_NAME,CLASS_ID=@CLASS_ID,CLASS_NO=@CLASS_NO,SORT=@SORT,LAST_CHANGE_TIME=@LAST_CHANGE_TIME,LAST_CHANGE_IP=@LAST_CHANGE_IP,LAST_CHANGE_GUID=@LAST_CHANGE_GUID,STATE=@STATE where id = @id";
using (var connection = new ConnectionCode().GetConnection())
{
return(connection.Execute(sqlStr, Models) > 0);
}
}
/// <summary>
///添加一条数据(返)插入的数据的id）
/// </summary>
public int Add(Models.OperationLog Models)
{
string sqlStr = " insert into [MS_OperationLog](GUID,IP_ADDRESS,CITY,OPERATION_URL,PARAM_STRING,RESULT_STRING,STATE) values(@GUID,@IP_ADDRESS,@CITY,@OPERATION_URL,@PARAM_STRING,@RESULT_STRING,@STATE);select @@identity ";
using (var connection = new ConnectionCode().GetConnection())
{
return(connection.Query <int>(sqlStr, Models).SingleOrDefault());
}
}
/// <summary>
///根据id取得数据模型(返)一个Models）
/// </summary>
public Models.OperationLog GetModels(int id)
{
string sqlStr = " select top 1 * from [MS_OperationLog] where id = @id ";
using (var connection = new ConnectionCode().GetConnection())
{
return(connection.Query <Models.OperationLog>(sqlStr, new{ id = id }).SingleOrDefault());
}
}
/// <summary>
///添加一条数据(返)插入的数据的id）
/// </summary>
public int Add(Models.Class Models)
{
string sqlStr = " insert into [MS_Class](CREATE_GUID,CLASS_NAME,CLASS_ID,CLASS_NO,SORT,LAST_CHANGE_TIME,LAST_CHANGE_IP,LAST_CHANGE_GUID,STATE) values(@CREATE_GUID,@CLASS_NAME,@CLASS_ID,@CLASS_NO,@SORT,@LAST_CHANGE_TIME,@LAST_CHANGE_IP,@LAST_CHANGE_GUID,@STATE);select @@identity ";
using (var connection = new ConnectionCode().GetConnection())
{
return(connection.Query <int>(sqlStr, Models).SingleOrDefault());
}
}
/// <summary>
/// 执行SQL返回DataSet
/// </summary>
/// <param name="sql"></param>
/// <returns></returns>
public static DataSet ExecuteSql(string sql)
{
using (var conn = new ConnectionCode().GetConnection()) {
SqlCommand comm = new SqlCommand(sql, conn);
SqlDataAdapter sda = new SqlDataAdapter(comm);
DataSet ds = new DataSet();
sda.Fill(ds);
return(ds);
}
}
/// <summary>
/// 根据Sql取得数据行数
/// </summary>
/// <param name="sql"></param>
/// <returns></returns>
public static int GetCount(string sql)
{
using (var conn = new ConnectionCode().GetConnection()) {
SqlCommand comm = new SqlCommand(sql, conn);
string res = comm.ExecuteScalar().ToString();
int count = 0;
if (!string.IsNullOrWhiteSpace(res))
{
count = Convert.ToInt32(res);
}
return(count);
}
}
/// <summary>
///根据条件修改数据(返)受影响的行数）
/// </summary>
public int Update(string field, string cond)
{
string sqlStr = " update [MS_NAV] set " + field + " where (1=2) ";
if (!string.IsNullOrEmpty(cond))
{
sqlStr += " or " + cond;
}
using (var connection = new ConnectionCode().GetConnection())
{
return(connection.Execute(sqlStr));
}
}
/// <summary>
///按条件取多个字段的列表(返)一个List<Models>）
/// </summary>
public List <Models.NAV> GetListFields(string fields, string cond)
{
string sqlStr = " select " + fields + " from [MS_NAV] where state not in (2) ";
if (!string.IsNullOrEmpty(cond))
{
sqlStr += " and " + cond;
}
using (var connection = new ConnectionCode().GetConnection())
{
return(connection.Query <Models.NAV>(sqlStr).AsList());
}
}
/// <summary>
/// 按条件取所有数据的数量
/// </summary>
public int GetCount(string cond)
{
string sqlStr = " select count(*) from [MS_NAV] where state not in (2) ";
if (!string.IsNullOrEmpty(cond))
{
sqlStr += " and " + cond;
}
using (var connection = new ConnectionCode().GetConnection())
{
return(connection.Query <int>(sqlStr).SingleOrDefault());
}
}
/// <summary>
///根据条件取得数据模型(返)一个Models）
/// </summary>
public Models.NAV GetModels(string cond)
{
string sqlStr = " select top 1 * from [MS_NAV] where state not in (2) ";
if (!string.IsNullOrEmpty(cond))
{
sqlStr += " and " + cond;
}
using (var connection = new ConnectionCode().GetConnection())
{
return(connection.Query <Models.NAV>(sqlStr).SingleOrDefault());
}
}
/// <summary>
///按条件取一个字段的值(返)一个string）
/// </summary>
public string GetOneField(string field, string cond)
{
string sqlStr = " select top 1 " + field + " from [MS_NAV] where state not in (2) ";
if (!string.IsNullOrEmpty(cond))
{
sqlStr += " and " + cond;
}
using (var connection = new ConnectionCode().GetConnection())
{
return(connection.Query <string>(sqlStr).SingleOrDefault());
}
}
/// <summary>
///按条件取多个字段的值(返)一个Models）
/// </summary>
public Models.SelfSign GetFields(string fields, string cond)
{
string sqlStr = " select top 1 " + fields + " from [MS_SelfSign] where state not in (2) ";
if (!string.IsNullOrEmpty(cond))
{
sqlStr += " and " + cond;
}
using (var connection = new ConnectionCode().GetConnection())
{
return(connection.Query <Models.SelfSign>(sqlStr).SingleOrDefault());
}
}
/// <summary>
///根据条件删除数据(返)受影响的行数）
/// </summary>
public int Delete(string cond)
{
string sqlStr = " delete [MS_NAV] where (1=2) ";
if (!string.IsNullOrEmpty(cond))
{
sqlStr += " or " + cond;
}
using (var connection = new ConnectionCode().GetConnection())
{
return(connection.Execute(sqlStr));
}
}
/// <summary>
///按条件取一个字段的列表(返)一个List<string>）
/// </summary>
public List <string> GetListField(string field, string cond)
{
string sqlStr = " select " + field + " from [MS_SelfSign] where state not in (2) ";
if (!string.IsNullOrEmpty(cond))
{
sqlStr += " and " + cond;
}
using (var connection = new ConnectionCode().GetConnection())
{
return(connection.Query <string>(sqlStr).AsList());
}
}
/// <summary>
///按条件取得模型列表(返)一个List<Models>）
/// </summary>
public List <Models.SelfSign> GetListModels(string cond)
{
string sqlStr = " select * from [MS_SelfSign] where state not in (2) ";
if (!string.IsNullOrEmpty(cond))
{
sqlStr += " and " + cond;
}
using (var connection = new ConnectionCode().GetConnection())
{
return(connection.Query <Models.SelfSign>(sqlStr).AsList());
}
}
/// <summary>
///一次添加多条数据(返)插入的行数）
/// </summary>
public int Adds(List <Models.LoginLog> Models)
{
string sqlStr = " insert into [MS_LoginLog](LOGIN_ID,LOGIN_PWD,GUID,IP_ADDRESS,CITY,SUCCESS,STATE) ";
for (int i = 0; i < Models.Count; i++)
{
sqlStr += " select ";
foreach (var a in Models[i].GetType().GetProperties())
{
sqlStr += " '" + a + "',";
}
sqlStr = sqlStr.Substring(0, sqlStr.Length - 1);
if (i < Models.Count - 1)
{
sqlStr += " union all ";
}
}
using (var connection = new ConnectionCode().GetConnection())
{
return(connection.Execute(sqlStr));
}
}
/// <summary>
///一次添加多条数据(返)插入的行数）
/// </summary>
public int Adds(List <Models.NAV> Models)
{
string sqlStr = " insert into [MS_NAV](TITLE,URL,POWER,CLASS,TYPE,STATE,BACKUP1) ";
for (int i = 0; i < Models.Count; i++)
{
sqlStr += " select ";
foreach (var a in Models[i].GetType().GetProperties())
{
sqlStr += " '" + a + "',";
}
sqlStr = sqlStr.Substring(0, sqlStr.Length - 1);
if (i < Models.Count - 1)
{
sqlStr += " union all ";
}
}
using (var connection = new ConnectionCode().GetConnection())
{
return(connection.Execute(sqlStr));
}
}
/// <summary>
///一次添加多条数据(返)插入的行数）
/// </summary>
public int Adds(List <Models.SelfSign> Models)
{
string sqlStr = " insert into [MS_SelfSign](CREATE_GUID,CONTENT,CLASS_ID,STATE,LAST_CHANGE_TIME,LAST_CHANGE_IP,LAST_CHANGE_GUID) ";
for (int i = 0; i < Models.Count; i++)
{
sqlStr += " select ";
foreach (var a in Models[i].GetType().GetProperties())
{
sqlStr += " '" + a + "',";
}
sqlStr = sqlStr.Substring(0, sqlStr.Length - 1);
if (i < Models.Count - 1)
{
sqlStr += " union all ";
}
}
using (var connection = new ConnectionCode().GetConnection())
{
return(connection.Execute(sqlStr));
}
}
/// <summary>
///一次添加多条数据(返)插入的行数）
/// </summary>
public int Adds(List <Models.ReaderLog> Models)
{
string sqlStr = " insert into [MS_ReaderLog](ENTRY_URL,ARTICE_ID,PROJECT_ID,VISIT_IP,VISIT_CITY,SESSION_GUID,STATE,OUT_TIME) ";
for (int i = 0; i < Models.Count; i++)
{
sqlStr += " select ";
foreach (var a in Models[i].GetType().GetProperties())
{
sqlStr += " '" + a + "',";
}
sqlStr = sqlStr.Substring(0, sqlStr.Length - 1);
if (i < Models.Count - 1)
{
sqlStr += " union all ";
}
}
using (var connection = new ConnectionCode().GetConnection())
{
return(connection.Execute(sqlStr));
}
}
/// <summary>
///一次添加多条数据(返)插入的行数）
/// </summary>
public int Adds(List <Models.HeadPortrait> Models)
{
string sqlStr = " insert into [MS_HeadPortrait](TITLE,FILE_URL,STATE,LAST_CHANGE_TIME,LAST_CHANGE_IP,LAST_CHANGE_GUID) ";
for (int i = 0; i < Models.Count; i++)
{
sqlStr += " select ";
foreach (var a in Models[i].GetType().GetProperties())
{
sqlStr += " '" + a + "',";
}
sqlStr = sqlStr.Substring(0, sqlStr.Length - 1);
if (i < Models.Count - 1)
{
sqlStr += " union all ";
}
}
using (var connection = new ConnectionCode().GetConnection())
{
return(connection.Execute(sqlStr));
}
}
/// <summary>
///一次添加多条数据(返)插入的行数）
/// </summary>
public int Adds(List <Models.TouchMsg> Models)
{
string sqlStr = " insert into [MS_TouchMsg](USER_EMAIL,MSG,STATE,LAST_CHANGE_TIME,LAST_CHANGE_IP) ";
for (int i = 0; i < Models.Count; i++)
{
sqlStr += " select ";
foreach (var a in Models[i].GetType().GetProperties())
{
sqlStr += " '" + a + "',";
}
sqlStr = sqlStr.Substring(0, sqlStr.Length - 1);
if (i < Models.Count - 1)
{
sqlStr += " union all ";
}
}
using (var connection = new ConnectionCode().GetConnection())
{
return(connection.Execute(sqlStr));
}
}
/// <summary>
///一次添加多条数据(返)插入的行数）
/// </summary>
public int Adds(List <Models.OperationLog> Models)
{
string sqlStr = " insert into [MS_OperationLog](GUID,IP_ADDRESS,CITY,OPERATION_URL,PARAM_STRING,RESULT_STRING,STATE) ";
for (int i = 0; i < Models.Count; i++)
{
sqlStr += " select ";
foreach (var a in Models[i].GetType().GetProperties())
{
sqlStr += " '" + a + "',";
}
sqlStr = sqlStr.Substring(0, sqlStr.Length - 1);
if (i < Models.Count - 1)
{
sqlStr += " union all ";
}
}
using (var connection = new ConnectionCode().GetConnection())
{
return(connection.Execute(sqlStr));
}
}