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())); } }