public bool ARegister(string managerName, string managerPhone, string managerUser, string managerPassword)
{
DbHelper db = new DbHelper();
DbCommand cmd = db.GetStoredProcCommond("sp_ARegister");
db.AddInParameter(cmd, "@ManagerName", DbType.String, managerName);
db.AddInParameter(cmd, "@ManagerPhone", DbType.String, managerPhone);
db.AddInParameter(cmd, "@ManagerUser", DbType.String, managerUser);
db.AddInParameter(cmd, "@ManagerPassword", DbType.String, managerPassword);
db.AddReturnParameter(cmd, "@retValue", DbType.Int32);
db.ExecuteNonQuery(cmd);
string result = cmd.Parameters["@retValue"].Value.ToString();
if (result == "1")
{
//管理员注册成功
return(true);
}
else
{
return(false);
}
}
public bool Register(string rusername, string rpassword)
{
DbHelper db = new DbHelper();
DbCommand cmd = db.GetStoredProcCommond("sp_Register");
db.AddInParameter(cmd, "@Pname", DbType.String, rusername);
db.AddInParameter(cmd, "@Password", DbType.String, rpassword);
db.AddReturnParameter(cmd, "@retValue", DbType.Int32);
db.ExecuteNonQuery(cmd);
string result = cmd.Parameters["@retValue"].Value.ToString();
if (result == "1")
{
//注册成功
return(true);
}
else
{
return(false);
}
}
public HttpResponseMessage AddStatistics(string pageid, string userid, string personid)
{
Boolean flag = false;
string Msg = "方法异常";
int code = 205;
try
{
userid = userid == null ? "" : userid;
personid = personid == null ? "" : personid;
DbCommand cmd = db.GetStoredProcCommond("user_save_AddStatistics");
db.AddInParameter(cmd, "@pageid", DbType.String, pageid);
db.AddInParameter(cmd, "@userid", DbType.String, userid);
db.AddInParameter(cmd, "@personid", DbType.String, personid);
DataTable dt = db.ExecuteDataTable(cmd);
Msg = dt.Rows[0][0].ToString();
flag = true;
code = 200;
String returnString = "{\"Success\":\"" + flag.ToString() + "\",\"Msg\":\"" + Msg.ToString() + "\", \"Data\":[],\"Code\":" + code + "}";
String decryptStr = aes.Encrypt(returnString);
String ret = "{\"data\":\"" + decryptStr + "\"}";
return(ToJson.toJson(ret.ToString()));
}
catch (Exception ex)
{
logger.WriteLogFile("AddStatistics(string userid)", ex.Message.ToString());
String returnString = "{\"Success\":\"" + flag.ToString() + "\",\"Msg\":\"" + Msg.ToString() + "\", \"Data\":[],\"Code\":" + code + "}";
String decryptStr = aes.Encrypt(returnString);
String ret = "{\"data\":\"" + decryptStr + "\"}";
return(ToJson.toJson(ret.ToString()));
}
}
//新建景点DAL代码
public bool ToString(string attractionsID, string attractions_name, string attractions_title, string level_RadioButton, string address, string openTime, string introduce, string contact, string noteAttractions, string Point)
{
DbHelper db = new DbHelper();
DbCommand cmd = db.GetStoredProcCommond("sp_Attractions");
db.AddInParameter(cmd, "@AttractionsID", DbType.String, attractionsID);
db.AddInParameter(cmd, "@AttractionsName", DbType.String, attractions_name);
db.AddInParameter(cmd, "@AttractionsTitle", DbType.String, attractions_title);
db.AddInParameter(cmd, "@AttractionsLevel", DbType.String, level_RadioButton);
db.AddInParameter(cmd, "@AttractionsAddress", DbType.String, address);
db.AddInParameter(cmd, "@OpenTime", DbType.String, openTime);
db.AddInParameter(cmd, "@AttractionsSynopsis", DbType.String, introduce);
db.AddInParameter(cmd, "@AttractionsContact", DbType.String, contact);
db.AddInParameter(cmd, "@NoteAttractions", DbType.String, noteAttractions);
db.AddInParameter(cmd, "@Point", DbType.String, Point);
db.AddReturnParameter(cmd, "@retValue", DbType.Int32);
db.ExecuteNonQuery(cmd);
string result = cmd.Parameters["@retValue"].Value.ToString();
if (result == "1")
{
//新建景点成功
return(true);
}
else
{
return(false);
}
}
void QueryOrderPrdInfo(HttpContext context)
{
int pageSize = Convert.ToInt32(context.Request["rows"]); //通过这个获取得到pageSize
int pageNum = Convert.ToInt32(context.Request["page"]); //通过这个获取得到pageNum（当前页码）
//string order_number = context.Request["order_number"];
string sub_order = context.Request["sub_order"];
var sort = context.Request["sort"]; //排序字段
var order = context.Request["order"]; //升序降序
string condition = " where 1=1"; //查询条件
if (!string.IsNullOrEmpty(sub_order))
{
condition += " and wo_number like '" + sub_order.Replace('*', '%') + "'";
}
string sqlcount = "select count(1) from hprdroute" + condition;
string sql = "select * from hprdroute" + condition;
string mysort = "wo_number,sequence";//排序情况
if (!string.IsNullOrEmpty(sort))
{
mysort = sort + " " + order;
}
DbHelper db = new DbHelper();
int count = db.ExecuteScalar(sqlcount); //总行数，用于分页
DbCommand cmd = db.GetStoredProcCommond("spPagingQuery");
db.AddInParameter(cmd, "@SQL", DbType.String, sql);
db.AddInParameter(cmd, "@Page", DbType.Int16, pageNum);
db.AddInParameter(cmd, "@RecordsPerPage", DbType.Int16, pageSize);
db.AddInParameter(cmd, "@ID", DbType.String, "ID");
db.AddInParameter(cmd, "@Sort", DbType.String, mysort);
DataTable dt = db.ExecuteDataTable(cmd);
System.Collections.Hashtable ht = new System.Collections.Hashtable();
ht.Add("total", count);
ht.Add("rows", dt);
string strJson = Newtonsoft.Json.JsonConvert.SerializeObject(ht);//序列化datatable
context.Response.Clear();
context.Response.ContentEncoding = Encoding.UTF8;
context.Response.ContentType = "application/json";
context.Response.Write(strJson);
context.Response.Flush();
context.Response.End();
}
public void ProcessRequest(HttpContext context)
{
int pageSize = Convert.ToInt32(context.Request["rows"]); //通过这个获取得到pageSize
int pageNum = Convert.ToInt32(context.Request["page"]); //通过这个获取得到pageNum
string keyname = context.Request["searchKey"]; //查询的关键字
string keyvalue = context.Request["searchValue"]; //查询的字段
var sort = context.Request["sort"]; //排序字段
var order = context.Request["order"]; //升序降序
string condition = " where 1=1"; //查询条件
if (keyname != null)
{
condition += " and " + keyname + " like '%" + keyvalue + "%'";
}
string sqlcount = "select count(1) from horder" + condition;
string sql = "select * from horder" + condition;
string mysort = "order_number";//排序情况
if (sort != null)
{
mysort = sort + " " + order;
}
DbHelper db = new DbHelper();
int count = db.ExecuteScalar(sqlcount);
DbCommand cmd = db.GetStoredProcCommond("spPagingQuery");
db.AddInParameter(cmd, "@SQL", DbType.String, sql);
db.AddInParameter(cmd, "@Page", DbType.Int16, pageNum);
db.AddInParameter(cmd, "@RecordsPerPage", DbType.Int16, pageSize);
db.AddInParameter(cmd, "@ID", DbType.String, "ORDERID");
db.AddInParameter(cmd, "@Sort", DbType.String, "ORDER_NUMBER, LAST_UPDATED_BY DESC");
DataTable dt = db.ExecuteDataTable(cmd);
System.Collections.Hashtable ht = new System.Collections.Hashtable();
ht.Add("total", count);
ht.Add("rows", dt);
string strJson = Newtonsoft.Json.JsonConvert.SerializeObject(ht);//序列化datatable
context.Response.Clear();
context.Response.ContentEncoding = Encoding.UTF8;
context.Response.ContentType = "application/json";
context.Response.Write(strJson);
context.Response.Flush();
context.Response.End();
}
//上传文本资料
public bool TM(string Tel, string Email, string Pname, string Name, string Gender, DateTime Birthday, string City, Int32 UserID)
{
DbHelper db = new DbHelper();
//数据库执行代码 待修改
DbCommand cmd = db.GetStoredProcCommond("sp_Pim");
db.AddInParameter(cmd, "@Tel", DbType.String, Tel);
db.AddInParameter(cmd, "@Name", DbType.String, Name);
db.AddInParameter(cmd, "@Email", DbType.String, Email);
db.AddInParameter(cmd, "@Gender", DbType.String, Gender);
db.AddInParameter(cmd, "@Birthday", DbType.DateTime, Birthday);
db.AddInParameter(cmd, "@City", DbType.String, City);
db.AddInParameter(cmd, "@Pname", DbType.String, Pname);
db.AddInParameter(cmd, "@UserID", DbType.Int32, UserID);
int result = db.ExecuteNonQuery(cmd);
if (result == 1)
{
//上传成功
return(true);
}
else
{
return(false);
}
}
private static string[] GetPrimaryKeyColumnsOfTable(string table)
{
const string strSql =
"select c.[name] from [sys].[tables] as t,[sys].[columns] as c,[sys].[indexes] as i,[sys].[index_columns] as ic where t.[name]=@TheTable and i.[is_primary_key]='1' and t.[object_id]=c.[object_id] and t.[object_id]=i.[object_id] and t.[object_id]=ic.[object_id] and c.[column_id]=ic.[column_id] and i.[index_id]=ic.[index_id]";
IDataReader dataReader = (IDataReader)null;
try
{
DbHelper dbHelper = DbFactory.CreateDatabase();
DbCommand sqlStringCommand = dbHelper.GetSqlStringCommand(strSql);
dbHelper.AddInParameter(sqlStringCommand, "@TheTable", DbType.AnsiString, (object)table);
dataReader = dbHelper.ExecuteReader(sqlStringCommand);
List <string> list = new List <string>(32);
while (dataReader.Read())
{
list.Add(dataReader.GetString(0));
}
return(list.Count == 0 ? (string[])null : list.ToArray());
}
catch (Exception ex)
{
throw ex;
}
finally
{
if (dataReader != null)
{
dataReader.Close();
}
}
}
private static string GetIdentityColumnOfTable(string table)
{
const string strSql =
"select c.[name] from [sys].[tables] as t,[sys].[columns] as c where t.[name]=@TheTable and t.[object_id]=c.[object_id] and c.[is_identity]='1'";
try
{
DbHelper dbHelper = DbFactory.CreateDatabase();
DbCommand sqlStringCommand = dbHelper.GetSqlStringCommand(strSql);
dbHelper.AddInParameter(sqlStringCommand, "@TheTable", DbType.AnsiString, (object)table);
object obj = dbHelper.ExecuteScalar(sqlStringCommand);
if (obj == null || obj is DBNull)
{
return((string)null);
}
else
{
return(obj.ToString());
}
}
catch (Exception ex)
{
throw ex;
}
}
/// <summary>
/// 创建用于删除的Sql命令
/// </summary>
public static DbCommand CreatDeleteCommand <T>(DbHelper db, T entity)
{
var entityType = typeof(T);
var primaryKeyEntityFieldNames = EntityMappingTool.GetPrimaryKeyOfEntityField(entityType);
var primaryKeyDbCloumnNames = EntityMappingTool.GetDbColumnNames(entityType,
primaryKeyEntityFieldNames);
var primaryKeyDbColumnTypes = EntityMappingTool.GetDbColumnTypes(entityType,
primaryKeyEntityFieldNames);
var primaryKeyPropertyInfos = EntityMappingTool.GetEntityPropertyInfos(entityType,
primaryKeyEntityFieldNames);
var sqlBuilder = new StringBuilder();
sqlBuilder.AppendFormat("DELETE FROM [{0}] WHERE ", EntityMappingTool.GetDbTableName(entityType));
for (var i = 0; i < primaryKeyEntityFieldNames.Count; i++)
{
sqlBuilder.Append((i > 0) ? " AND " : "");
sqlBuilder.AppendFormat("([{0}]=@{0})", primaryKeyDbCloumnNames[i]);
}
//参数
var cmd = db.GetSqlStringCommand(sqlBuilder.ToString());
for (var i = 0; i < primaryKeyEntityFieldNames.Count; i++)
{
db.AddInParameter(cmd, "@" + primaryKeyDbCloumnNames[i], primaryKeyDbColumnTypes[i],
primaryKeyPropertyInfos[i].GetValue(entity, null));
}
return(cmd);
}
public HttpResponseMessage Get(string username, string password)
{
Boolean flag = false;
string Msg = "方法异常";
int code = 205;
try
{
string newpassword = System.Web.Security.FormsAuthentication.HashPasswordForStoringInConfigFile(password, "MD5");
DbCommand cmd = db.GetStoredProcCommond("user_login_User");
db.AddInParameter(cmd, "@username", DbType.String, username);
db.AddInParameter(cmd, "@password", DbType.String, newpassword);
DataTable dt = db.ExecuteDataTable(cmd);
string str = "";
Msg = dt.Rows[0]["msg"].ToString();
if (Msg == "登录成功")
{
str = "{\"_User_Name\":\"" + username.ToString()
+ "\",\"_User_ID\":\"" + dt.Rows[0]["userid"].ToString()
+ "\"}";
flag = true;
code = 200;
}
else if (Msg == "用户未注册")
{
flag = true;
code = 209;
}
else if (Msg == "用户名或密码错误")
{
flag = true;
code = 217;
}
String returnString = "{\"Success\":\"" + flag.ToString() + "\",\"Msg\":\"" + Msg.ToString() + "\", \"Data\":[" + str.ToString() + "],\"Code\":" + code + "}";
String decryptStr = aes.Encrypt(returnString);
String ret = "{\"data\":\"" + decryptStr + "\"}";
return(ToJson.toJson(ret.ToString()));
}
catch (Exception ex)
{
logger.WriteLogFile("Get(string username, string password)", ex.Message.ToString());
String returnString = "{\"Success\":\"" + flag.ToString() + "\",\"Msg\":\"" + Msg.ToString() + "\", \"Data\":[" + "" + "],\"Code\":" + code + "}";
String decryptStr = aes.Encrypt(returnString);
String ret = "{\"data\":\"" + decryptStr + "\"}";
return(ToJson.toJson(ret.ToString()));
}
}
/// <summary>
/// 填充Sql参数
/// </summary>
public static void FillSqlParameters <T>(DbHelper db, DbCommand cmd, GenericWhereEntity <T> whereEntity)
{
for (var i = 0; i < whereEntity.WhereParameterNames.Count; i++)
{
db.AddInParameter(cmd, whereEntity.WhereParameterNames[i], whereEntity.WhereParameterTypes[i],
whereEntity.WhereParameterValues[i]);
}
}
/// <summary>
/// 生成用于更新的Sql命令
/// </summary>
public static DbCommand CreateUpdateCommand <T>(DbHelper db, T entity)
{
var entityType = typeof(T);
var primaryKeyEntityFieldNames = EntityMappingTool.GetPrimaryKeyOfEntityField(entityType);
var primaryKeyDbCloumnNames = EntityMappingTool.GetDbColumnNames(entityType,
primaryKeyEntityFieldNames);
var primaryKeyDbColumnTypes = EntityMappingTool.GetDbColumnTypes(entityType,
primaryKeyEntityFieldNames);
var notNullEntityFields = EntityInstanceTool.GetNotNullFields(entity);
var notNullDbCloumnNames = EntityMappingTool.GetDbColumnNames(entityType, notNullEntityFields);
var notNullDbColumnTypes = EntityMappingTool.GetDbColumnTypes(entityType, notNullEntityFields);
var notNullEntityPropertys = EntityInstanceTool.GetNotNullEntityPropertys(entity);
//生成Sql语句
var parameterIndex = new List <string>();
var sqlBuilder = new StringBuilder();
sqlBuilder.Append("UPDATE [").Append(EntityMappingTool.GetDbTableName(entityType)).Append("] SET ");
var firstColumn = true;
for (var i = 0; i < notNullDbCloumnNames.Count; i++)
{
var loopColumn = notNullDbCloumnNames[i];
//当前模式主键不更新
if (primaryKeyDbCloumnNames.Contains(loopColumn))
{
continue;
}
sqlBuilder.Append(firstColumn ? "" : ",");
firstColumn = false;
sqlBuilder.AppendFormat("[{0}]=@{0}", loopColumn);
parameterIndex.Add(loopColumn);
}
//WHERE
sqlBuilder.Append(" WHERE ");
for (var i = 0; i < primaryKeyDbCloumnNames.Count; i++)
{
sqlBuilder.Append((i > 0) ? " AND " : "");
sqlBuilder.AppendFormat("([{0}]=@{0})", primaryKeyDbCloumnNames[i]);
parameterIndex.Add(primaryKeyDbCloumnNames[i]);
}
var cmd = db.GetSqlStringCommand(sqlBuilder.ToString());
for (var i = 0; i < parameterIndex.Count; i++)
{
var pIndex = notNullDbCloumnNames.IndexOf(parameterIndex[i]);
db.AddInParameter(cmd, "@" + notNullDbCloumnNames[pIndex], notNullDbColumnTypes[pIndex],
notNullEntityPropertys[pIndex].GetValue(entity, null));
}
return(cmd);
}
//获取票的信息
public string GetTicketPro(string AttractionsID)
{
DbHelper db = new DbHelper();
DbCommand cmd = db.GetStoredProcCommond("sp_TicketPro");
db.AddInParameter(cmd, "@AttractionsID", DbType.String, AttractionsID);
DataTable dt = db.ExecuteDataTable(cmd);
string result = dt.Rows[0][0].ToString() + "$" + dt.Rows[0][1].ToString();
return(result);
}
//上传图片
public bool IMG(byte[] Head_Portrait, Int32 UserID)
{
DbHelper db = new DbHelper();
DbCommand cmd = db.GetStoredProcCommond("sp_Pim2");
//数据库执行代码 待修改
db.AddInParameter(cmd, "@Head_Portrait", DbType.Binary, Head_Portrait);
db.AddInParameter(cmd, "@UserID", DbType.Int32, UserID);
int result = db.ExecuteNonQuery(cmd);
if (result == 1)
{
//上传成功
return(true);
}
else
{
return(false);
}
}
public String ALogin(string AName, string APassword)
{
DbHelper db = new DbHelper();
DbCommand cmd = db.GetStoredProcCommond("sp_ALogin");
db.AddInParameter(cmd, "@ManagerUser", DbType.String, AName);
db.AddInParameter(cmd, "@ManagerPassword", DbType.String, APassword);
db.AddReturnParameter(cmd, "@retValue", DbType.Int32);
db.ExecuteNonQuery(cmd);
string re = cmd.Parameters["@retValue"].Value.ToString();
if (re == "-1")
{
return("-1");
}
else
{
return(re);
}
//管理员登录成功
}
void Save(HttpContext context)
{
string sProcessID = context.Request["process_id"];
string sPartsNo = context.Request["parts_no"];
string sWI = context.Request["wi"];
string sUser = context.Request["user_name"];
DbHelper db = new DbHelper();
DbCommand cmd = db.GetStoredProcCommond("spWIInsert");
db.AddInParameter(cmd, "@process_id", DbType.String, sProcessID);
db.AddInParameter(cmd, "@parts_no", DbType.String, sPartsNo);
db.AddInParameter(cmd, "@wi", DbType.String, sWI);
db.AddInParameter(cmd, "@user", DbType.String, sUser);
Result rs = new Result();
if (db.ExecuteNonQuery(cmd) == 1)
{
rs.status = 1;
rs.msg = "添加成功！";
}
else
{
rs.status = 0;
rs.msg = "服务器繁忙，请稍后再试！";
}
rs.item = "[]";
string strJson = JsonConvert.SerializeObject(rs);
context.Response.Clear();
context.Response.ContentEncoding = Encoding.UTF8;
context.Response.ContentType = "application/json";
context.Response.Write(strJson);
context.Response.Flush();
context.Response.End();
}
public bool Img(string attractionsID, string img_src)
{
DbHelper db = new DbHelper();
DbCommand cmd = db.GetStoredProcCommond("sp_AttractionsImg");
db.AddInParameter(cmd, "@AttractionsID", DbType.String, attractionsID);
db.AddInParameter(cmd, "@imagePath1", DbType.String, img_src);
db.AddReturnParameter(cmd, "@retValue", DbType.Int32);
db.ExecuteNonQuery(cmd);
string result = cmd.Parameters["@retValue"].Value.ToString();
if (result == "1")
{
//新建景点成功
return(true);
}
else
{
return(false);
}
}
public String Login(string LoginName, string Password)
{
DbHelper db = new DbHelper();
DbCommand cmd = db.GetStoredProcCommond("sp_Login");
db.AddInParameter(cmd, "@LoginName", DbType.String, LoginName);
db.AddInParameter(cmd, "@Password", DbType.String, Password);
db.AddReturnParameter(cmd, "@retValue", DbType.Int32);
db.ExecuteNonQuery(cmd);
string result = cmd.Parameters["@retValue"].Value.ToString();
if (result == "0")
{
return("-1");
}
else
{
return(result);
}
}
public HttpResponseMessage Register(string username, string password, string email, string rancode)
{
Boolean flag = false;
string Msg = "方法异常";
int code = 205;
try
{
string newpassword = System.Web.Security.FormsAuthentication.HashPasswordForStoringInConfigFile(password, "MD5");
DbCommand cmd = db.GetStoredProcCommond("user_register_User");
db.AddInParameter(cmd, "@username", DbType.String, username);
db.AddInParameter(cmd, "@password", DbType.String, newpassword);
db.AddInParameter(cmd, "@email", DbType.String, email);
db.AddInParameter(cmd, "@rancode", DbType.String, rancode);
DataTable dt = db.ExecuteDataTable(cmd);
flag = true;
Msg = dt.Rows[0][0].ToString();
string str = "";
if (dt.Rows[0][0].ToString() == "用户邮箱已存在")
{
code = 204;
}
else if (dt.Rows[0][0].ToString() == "用户名已存在")
{
code = 210;
}
else if (dt.Rows[0][0].ToString() == "用户名、邮箱已存在")
{
code = 215;
}
else if (dt.Rows[0][0].ToString() == "验证码错误")
{
code = 216;
}
else if (dt.Rows[0][0].ToString() == "注册成功")
{
code = 200;
str += "{\"_userid\":\"" + dt.Rows[0]["newid"].ToString()
+ "\"}";
}
String returnString = "{\"Success\":\"" + flag.ToString() + "\",\"Msg\":\"" + Msg.ToString() + "\", \"Data\":[" + str + "],\"Code\":" + code + "}";
String decryptStr = aes.Encrypt(returnString);
String ret = "{\"data\":\"" + decryptStr + "\"}";
return(ToJson.toJson(ret.ToString()));
}
catch (Exception ex)
{
logger.WriteLogFile("Register(string username, string password, string email)", ex.Message.ToString());
String returnString = "{\"Success\":\"" + flag.ToString() + "\",\"Msg\":\"" + Msg.ToString() + "\", \"Data\":[" + "" + "],\"Code\":" + code + "}";
String decryptStr = aes.Encrypt(returnString);
String ret = "{\"data\":\"" + decryptStr + "\"}";
return(ToJson.toJson(ret.ToString()));
}
}
public DataTable Information(Int32 UserID)
{
DbHelper db = new DbHelper();
DbCommand cmd = db.GetStoredProcCommond("sp_Pimxx");
db.AddInParameter(cmd, "@UserID", DbType.Int32, UserID);
try
{
DataTable dt = db.ExecuteDataTable(cmd);
return(dt);
}
catch (Exception ex)
{
Console.WriteLine(ex.ToString());
return(null);
}
}
/// <summary>
/// 生成用于插入的Sql命令
/// </summary>
public static DbCommand CreateInsertCommand <T>(DbHelper db, T entity)
{
var entityType = typeof(T);
var notNullEntityFields = EntityInstanceTool.GetNotNullFields(entity);
var notNullDbCloumnNames = EntityMappingTool.GetDbColumnNames(entityType, notNullEntityFields);
var notNullDbColumnTypes = EntityMappingTool.GetDbColumnTypes(entityType, notNullEntityFields);
var notNullEntityPropertys = EntityInstanceTool.GetNotNullEntityPropertys(entity);
var sqlBuilder = new StringBuilder();
sqlBuilder.Append("INSERT INTO [").Append(EntityMappingTool.GetDbTableName(entity.GetType())).Append("] (");
for (var i = 0; i < notNullDbCloumnNames.Count; i++)
{
if (i > 0)
{
sqlBuilder.Append(", ");
}
sqlBuilder.Append("[").Append(notNullDbCloumnNames[i]).Append("]");
}
sqlBuilder.Append(") VALUES (");
for (var i = 0; i < notNullDbCloumnNames.Count; i++)
{
if (i > 0)
{
sqlBuilder.Append(", ");
}
sqlBuilder.Append("@").Append(notNullDbCloumnNames[i]);
}
sqlBuilder.Append(")");
var cmd = db.GetSqlStringCommand(sqlBuilder.ToString());
for (var i = 0; i < notNullDbCloumnNames.Count; i++)
{
db.AddInParameter(cmd, "@" + notNullDbCloumnNames[i], notNullDbColumnTypes[i],
notNullEntityPropertys[i].GetValue(entity, null));
}
return(cmd);
}
public byte[] Image(Int32 UserID)
{
DbHelper db = new DbHelper();
DbCommand cmd = db.GetStoredProcCommond("sp_Pimimage");
db.AddInParameter(cmd, "@UserID", DbType.Int32, UserID);
try
{
//强制转换成byte[]
byte[] dt = (byte[])db.ExecuteScalar(cmd);
return(dt);
}
catch (Exception ex)
{
Console.WriteLine(ex.ToString());
return(null);
}
}
public DataTable browse(string GetimgID)
{
DbHelper db = new DbHelper();
DbCommand cmd = db.GetStoredProcCommond("sp_Getimg");
db.AddInParameter(cmd, "@AttractionsID", DbType.String, GetimgID);
try
{
DataTable dt = db.ExecuteDataTable(cmd);
return(dt);
}
catch (Exception ex)
{
Console.WriteLine(ex.ToString());
return(null);
}
}
/// <summary>
/// 生成用于更新的Sql命令
/// </summary>
public static DbCommand CreateUpdateCommand <T>(DbHelper db, T entity, GenericWhereEntity <T> whereEntity)
{
var entityType = typeof(T);
var notNullEntityFields = EntityInstanceTool.GetNotNullFields(entity);
var notNullDbCloumnNames = EntityMappingTool.GetDbColumnNames(entityType, notNullEntityFields);
var notNullDbColumnTypes = EntityMappingTool.GetDbColumnTypes(entityType, notNullEntityFields);
var notNullEntityPropertys = EntityInstanceTool.GetNotNullEntityPropertys(entity);
//生成Sql语句
var parameterIndex = new List <string>();
var sqlBuilder = new StringBuilder();
sqlBuilder.AppendFormat("UPDATE {0} SET ", whereEntity.TableName);
var firstColumn = true;
for (var i = 0; i < notNullDbCloumnNames.Count; i++)
{
var loopColumn = notNullDbCloumnNames[i];
sqlBuilder.Append(firstColumn ? "" : ",");
firstColumn = false;
sqlBuilder.AppendFormat("{0}.[{1}]=@{1}", whereEntity.TableName, loopColumn);
parameterIndex.Add(loopColumn);
}
//WHERE
var whereSql = CreateWhereSql(whereEntity);
sqlBuilder.Append(" ").Append(whereSql);
//参数
var cmd = db.GetSqlStringCommand(sqlBuilder.ToString());
for (var i = 0; i < notNullDbCloumnNames.Count; i++)
{
db.AddInParameter(cmd, "@" + notNullDbCloumnNames[i], notNullDbColumnTypes[i],
notNullEntityPropertys[i].GetValue(entity, null));
}
FillSqlParameters(db, cmd, whereEntity);
return(cmd);
}
//新建票种DAL代码
public bool Ticket
(int ticketsID,
string ticket_type,
string tickets_name,
string ticketsPrices,
string tickets_illustrate,
string orderDateD,
string spotID,
string ticket_time)
{
DbHelper db = new DbHelper();
DbCommand cmd = db.GetStoredProcCommond("sp_NewTicket");
db.AddInParameter(cmd, "@TicketsID", DbType.Int32, ticketsID);
db.AddInParameter(cmd, "@TicketsType", DbType.String, ticket_type);
db.AddInParameter(cmd, "@TicketsName", DbType.String, tickets_name);
db.AddInParameter(cmd, "@Price", DbType.String, ticketsPrices);
db.AddInParameter(cmd, "@explain", DbType.String, tickets_illustrate);
db.AddInParameter(cmd, "@OrderDateD", DbType.String, orderDateD);
db.AddInParameter(cmd, "@AttractionsID", DbType.String, spotID);
db.AddInParameter(cmd, "@time", DbType.String, ticket_time);
db.AddReturnParameter(cmd, "@retValue", DbType.Int32);
db.ExecuteNonQuery(cmd);
string result = cmd.Parameters["@retValue"].Value.ToString();
if (result == "1")
{
//新建票种成功
return(true);
}
else
{
return(false);
}
}
public HttpResponseMessage GetHelp(string type)
{
Boolean flag = false;
string Msg = "方法异常";
int code = 205;
try
{
DbCommand cmd = db.GetStoredProcCommond("user_get_GetHelpPage");
db.AddInParameter(cmd, "@type", DbType.String, type);
DataTable dt = db.ExecuteDataTable(cmd);
string str = "";
if (dt.Rows.Count == 0)
{
flag = true;
code = 202;
Msg = "没有查询到数据";
}
else
{
flag = true;
code = 200;
Msg = "获取成功";
str += "{\"_HelpInfo\":\"" + dt.Rows[0]["HelpInfo"].ToString().Trim() + "\"}";
}
String returnString = "{\"Success\":\"" + flag.ToString() + "\",\"Msg\":\"" + Msg.ToString() + "\", \"Data\":[" + str + "],\"Code\":" + code + "}";
String decryptStr = aes.Encrypt(returnString);
String ret = "{\"data\":\"" + decryptStr + "\"}";
return(ToJson.toJson(ret.ToString()));
}
catch (Exception ex)
{
logger.WriteLogFile("GetHelp", ex.Message.ToString());
String returnString = "{\"Success\":\"" + flag.ToString() + "\",\"Msg\":\"" + Msg.ToString() + "\", \"Data\":[],\"Code\":" + code + "}";
String decryptStr = aes.Encrypt(returnString);
String ret = "{\"data\":\"" + decryptStr + "\"}";
return(ToJson.toJson(ret.ToString()));
}
}
public HttpResponseMessage GetRoleByPage(string pagecode)
{
Boolean flag = false;
string Msg = "方法异常";
int code = 205;
try
{
pagecode = pagecode == null ? "" : pagecode;
DbCommand cmd = db.GetStoredProcCommond("user_get_GetRoleByPage");
db.AddInParameter(cmd, "@pagecode", DbType.String, pagecode);
DataTable dt = db.ExecuteDataTable(cmd);
Msg = dt.Rows[0][0].ToString();
if (Msg == "无法找到该链接对应的页面")
{
code = 219;
}
else if (Msg == "无权限")
{
code = 220;
}
else
{
code = 200;
}
String returnString = "{\"Success\":\"" + flag.ToString() + "\",\"Msg\":\"" + Msg.ToString() + "\", \"Data\":[],\"Code\":" + code + "}";
String decryptStr = aes.Encrypt(returnString);
String ret = "{\"data\":\"" + decryptStr + "\"}";
return(ToJson.toJson(ret.ToString()));
}
catch (Exception ex)
{
logger.WriteLogFile("GetRoleByPage", ex.Message.ToString());
String returnString = "{\"Success\":\"" + flag.ToString() + "\",\"Msg\":\"" + Msg.ToString() + "\", \"Data\":[],\"Code\":" + code + "}";
String decryptStr = aes.Encrypt(returnString);
String ret = "{\"data\":\"" + decryptStr + "\"}";
return(ToJson.toJson(ret.ToString()));
}
}
private static SqlServerColumn[] GetColumnsOfTable_2005(string table)
{
const string strSql =
"select c.name as ColumnName, ty.name as ColumnType, c.max_length as ColumnSize\r\n from sys.columns as c\r\n inner join sys.tables as t on t.object_id=c.object_id\r\n inner join sys.types as ty on ty.system_type_id = c.system_type_id\r\nwhere ty.system_type_id=ty.user_type_id and t.name=@TheTable";
IDataReader dataReader = (IDataReader)null;
try
{
DbHelper dbHelper = DbFactory.CreateDatabase();
DbCommand sqlStringCommand = dbHelper.GetSqlStringCommand(strSql);
dbHelper.AddInParameter(sqlStringCommand, "@TheTable", DbType.AnsiString, (object)table);
dataReader = dbHelper.ExecuteReader(sqlStringCommand);
List <SqlServerColumn> list = new List <SqlServerColumn>(32);
while (dataReader.Read())
{
SqlServerColumn sqlServerColumn = new SqlServerColumn()
{
ColumnName = dataReader.GetString(0),
ColumnType = dataReader.GetString(1),
ColumnSize = int.Parse(dataReader.GetValue(2).ToString())
};
list.Add(sqlServerColumn);
}
return(list.Count == 0 ? (SqlServerColumn[])null : list.ToArray());
}
catch (Exception ex)
{
throw ex;
}
finally
{
if (dataReader != null)
{
dataReader.Close();
}
}
}
public HttpResponseMessage GetFindPlay(string type)
{
Boolean flag = false;
string Msg = "方法异常";
int code = 205;
try
{
DbCommand cmd = db.GetStoredProcCommond("user_get_GetFindPlay");
db.AddInParameter(cmd, "@type", DbType.String, type);
DataTable dt = db.ExecuteDataTable(cmd);
int len = dt.Rows.Count;
string str = "";
if (len > 0)
{
for (int i = 0; i < len; i++)
{
string _FMpath = dt.Rows[i]["CoverImg_Path"].ToString().Trim();
if (!string.IsNullOrEmpty(_FMpath))
{
if (_FMpath.Substring(0, 1) == "~")
{
_FMpath = _FMpath.Replace("~", "");
}
_FMpath = ManagerPath + System.Web.HttpUtility.UrlEncode(_FMpath);
}
str += "{\"_titleid\":\"" + dt.Rows[i]["Title_ID"].ToString().Trim()
+ "\",\"_FMpath\":\"" + _FMpath
+ "\"},";
}
str = str.Substring(0, str.Length - 1);
flag = true;
code = 200;
Msg = "获取成功";
}
else
{
flag = true;
code = 202;
Msg = "没有查询到数据";
}
String returnString = "{\"Success\":\"" + flag.ToString() + "\",\"Msg\":\"" + Msg.ToString() + "\", \"Data\":[" + str.ToString() + "],\"Code\":" + code + "}";
String decryptStr = aes.Encrypt(returnString);
String ret = "{\"data\":\"" + decryptStr + "\"}";
return(ToJson.toJson(ret.ToString()));
}
catch (Exception ex)
{
logger.WriteLogFile("GetFindPlay", ex.Message.ToString());
String returnString = "{\"Success\":\"" + flag.ToString() + "\",\"Msg\":\"" + Msg.ToString() + "\", \"Data\":[" + "" + "],\"Code\":" + code + "}";
String decryptStr = aes.Encrypt(returnString);
String ret = "{\"data\":\"" + decryptStr + "\"}";
return(ToJson.toJson(ret.ToString()));
}
}
