/// <summary> /// 读取系统权限列表 /// </summary> /// <param name="number">当前页码</param> /// <param name="pgsize">当前页的数据条数</param> /// <returns></returns> public static string GetPermission(int number, int pgsize, string where) { string result = ""; //实例化数据库连接对象 SqlConnection connect = new SqlConnection(connetStr); //打开数据连接 connect.Open(); if (connect.State != System.Data.ConnectionState.Open) { Console.WriteLine("打开数据库失败"); } else { //声明SQL语句 string cmdtext = $"SELECT * FROM dbo.ZJHL_Permission {where}"; //实例化SQL命令执行对象,输入SQL语句(cmdtext)和数据库连接对象(connect) SqlCommand cmd = new SqlCommand(cmdtext, connect); //数据适配器 SqlDataAdapter da = new SqlDataAdapter(cmd); //数据集 DataSet ds = new DataSet(); //通过数据适配器填充数据集 da.Fill(ds); //返回JSON字符串 result = JsonUnit.DataTableToJson(ds.Tables[0]); } return(result); }
/// <summary> /// 获取客户分页信息 /// </summary> /// <param name="number">当前页码</param> /// <param name="pgsize">当前页的数据条数</param> /// <returns></returns> public static string GetAdvertisement(int number, int pgsize) { string result = ""; //实例化数据库连接对象 SqlConnection connect = new SqlConnection(connetStr); //打开数据连接 connect.Open(); if (connect.State != System.Data.ConnectionState.Open) { Console.WriteLine("打开数据库失败"); } else { //声明SQL语句 string cmdtext = " SELECT TOP " + pgsize + " * FROM ZJHL_Adviertisements WHERE ID NOT IN ( SELECT TOP (" + pgsize + " * (" + number + "-1)) ID FROM ZJHL_Adviertisements )"; //实例化SQL命令执行对象,输入SQL语句(cmdtext)和数据库连接对象(connect) SqlCommand cmd = new SqlCommand(cmdtext, connect); //执行SQL命令并返回SQL数据只读器(reader) SqlDataReader reader = cmd.ExecuteReader(); DataTable dt = new DataTable(); DataRow dr; dt.Columns.Add("id"); dt.Columns.Add("Name"); dt.Columns.Add("Title"); dt.Columns.Add("GG_Image"); dt.Columns.Add("GG_Content"); dt.Columns.Add("CreateDate"); dt.Columns.Add("CreateId"); while (reader.Read()) { //读取数据 var id = reader.GetInt64(0); var Name = reader.IsDBNull(1) ? "暂时没有填写名字" : reader.GetString(1); var Title = reader.IsDBNull(2) ? "暂时没有填写标题" : reader.GetString(2); var GG_Image = reader.IsDBNull(3) ? "暂时没有填写照片信息" : reader.GetString(3); var GG_Content = reader.IsDBNull(4) ? "暂时没有填写广告内容" : reader.GetString(4); var CreateDate = reader.IsDBNull(5) ? DateTime.Now : reader.GetDateTime(5); var CreateId = reader.IsDBNull(6) ? -1 : reader.GetInt64(6); //为数据表添加列 dr = dt.NewRow(); //为数据行添加值 dr["id"] = id; dr["Name"] = Name; dr["Title"] = Title; dr["GG_Image"] = GG_Image; dr["GG_Content"] = GG_Content; dr["CreateDate"] = CreateDate; dr["CreateId"] = CreateId; //添加行到表格 dt.Rows.Add(dr); } result = JsonUnit.DataTableToJson(dt); } return(result); }
/// <summary> /// 获取客户分页信息 /// </summary> /// <param name="number">当前页码</param> /// <param name="pgsize">当前页的数据条数</param> /// <returns></returns> public static string GetCustomer(int number, int pgsize) { string result = ""; //实例化数据库连接对象 SqlConnection connect = new SqlConnection(connetStr); //打开数据连接 connect.Open(); if (connect.State != System.Data.ConnectionState.Open) { Console.WriteLine("打开数据库失败"); } else { //声明SQL语句 string cmdtext = " SELECT TOP " + pgsize + " * FROM ZJHL_Customers WHERE ID NOT IN ( SELECT TOP (" + pgsize + " * (" + number + "-1)) ID FROM ZJHL_Customers )"; //实例化SQL命令执行对象,输入SQL语句(cmdtext)和数据库连接对象(connect) SqlCommand cmd = new SqlCommand(cmdtext, connect); //执行SQL命令并返回SQL数据只读器(reader) SqlDataReader reader = cmd.ExecuteReader(); DataTable dt = new DataTable(); DataRow dr; dt.Columns.Add("id"); dt.Columns.Add("Name"); dt.Columns.Add("UserName"); dt.Columns.Add("Phone"); dt.Columns.Add("Sex"); dt.Columns.Add("Address"); dt.Columns.Add("Area"); dt.Columns.Add("Remark"); dt.Columns.Add("Company"); dt.Columns.Add("QQ"); dt.Columns.Add("MailBox"); dt.Columns.Add("Contact"); dt.Columns.Add("Password"); dt.Columns.Add("CompanyLogo"); dt.Columns.Add("CompanyLogoMini"); while (reader.Read()) { //读取数据 var id = reader.GetInt64(0); var Name = reader.GetString(1); var UserName = reader.GetString(2); var Phone = reader.GetString(3); var Sex = reader.GetBoolean(4); var Address = reader.GetString(5); var Area = reader.GetString(6); var Remark = reader.GetString(7); var Company = reader.GetString(8); var QQ = reader.GetString(9); var MailBox = reader.GetString(10); var Contact = reader.GetString(11); var Password = reader.GetString(12); var CompanyLogo = reader.GetString(13); var CompanyLogoMini = reader.GetString(14); //为数据表添加列 dr = dt.NewRow(); //为数据行添加值 dr["id"] = id; dr["Name"] = Name; dr["UserName"] = UserName; dr["Phone"] = Phone; dr["Sex"] = Sex; dr["Address"] = Address; dr["Area"] = Area; dr["Remark"] = Remark; dr["Company"] = Company; dr["QQ"] = QQ; dr["MailBox"] = MailBox; dr["Contact"] = Contact; dr["Password"] = Password; dr["CompanyLogo"] = CompanyLogo; dr["CompanyLogoMini"] = CompanyLogoMini; //添加行到表格 dt.Rows.Add(dr); } result = JsonUnit.DataTableToJson(dt); } return("{data:" + result + "}"); }
public static string DoSearch(string selectType, string selectContent) { string result = ""; //声明搜索语句 var sql = string.Format(@" SELECT * FROM [ZJHL].[dbo].[ZJHL_Customers] WHERE Name LIKE '{0}%' OR Address LIKE '{1}%' OR Company LIKE '{2}%' OR Phone LIKE '{3}%' OR QQ LIKE '{4}%'", selectContent, selectContent, selectContent, selectContent, selectContent); //实例化数据库连接对象 SqlConnection connect = new SqlConnection(connetStr); //打开数据连接 connect.Open(); if (connect.State != System.Data.ConnectionState.Open) { Console.WriteLine("打开数据库失败"); } else { //实例化SQL命令执行对象,输入SQL语句(cmdtext)和数据库连接对象(connect) SqlCommand cmd = new SqlCommand(sql, connect); //执行SQL命令并返回SQL数据只读器(reader) SqlDataReader reader = cmd.ExecuteReader(); DataTable dt = new DataTable(); DataRow dr; dt.Columns.Add("id"); dt.Columns.Add("UserName"); dt.Columns.Add("Name"); dt.Columns.Add("Phone"); dt.Columns.Add("Sex"); dt.Columns.Add("Address"); dt.Columns.Add("Area"); dt.Columns.Add("Remark"); dt.Columns.Add("Company"); dt.Columns.Add("QQ"); dt.Columns.Add("MailBox"); dt.Columns.Add("Contact"); dt.Columns.Add("Password"); dt.Columns.Add("CompanyLogo"); dt.Columns.Add("CompanyLogoMini"); while (reader.Read()) { //读取数据 var id = reader.GetInt64(0); var UserName = reader.IsDBNull(1) ? "" : reader.GetString(1); var Name = reader.GetString(2); var Phone = reader.GetString(3); var Sex = reader.GetBoolean(4); var Address = reader.GetString(5); var Area = reader.GetString(6); var Remark = reader.GetString(7); var Company = reader.GetString(8); var QQ = reader.GetString(9); var MailBox = reader.GetString(10); var Contact = reader.GetString(11); var Password = reader.GetString(12); var CompanyLogo = reader.GetString(13); var CompanyLogoMini = reader.GetString(14); //为数据表添加列 dr = dt.NewRow(); //为数据行添加值 dr["id"] = id; dr["UserName"] = UserName; dr["Name"] = Name; dr["Phone"] = Phone; dr["Sex"] = Sex; dr["Address"] = Address; dr["Area"] = Area; dr["Remark"] = Remark; dr["Company"] = Company; dr["QQ"] = QQ; dr["MailBox"] = MailBox; dr["Contact"] = Contact; dr["Password"] = Password; dr["CompanyLogo"] = CompanyLogo; dr["CompanyLogoMini"] = CompanyLogoMini; //添加行到表格 dt.Rows.Add(dr); } result = JsonUnit.DataTableToJson(dt); } return(result); }
/// <summary> /// 用户登陆 /// </summary> /// <param name="username">用户名</param> /// <param name="password">密码</param> /// <returns></returns> public static string Login(string username, string password) { string result = ""; //实例化数据库连接对象 SqlConnection connect = new SqlConnection(connetStr); //打开数据连接 connect.Open(); if (connect.State != System.Data.ConnectionState.Open) { Console.WriteLine("打开数据库失败"); } else { //声明SQL语句 string cmdtext = "SELECT * FROM ZJHL_Users WHERE Name = '" + username + "' AND Password = '******'"; //实例化SQL命令执行对象,输入SQL语句(cmdtext)和数据库连接对象(connect) SqlCommand cmd = new SqlCommand(cmdtext, connect); //执行SQL命令并返回SQL数据只读器(reader) SqlDataReader reader = cmd.ExecuteReader(); //声明并实例化数据表对象 DataTable dt = new DataTable(); //声明数据行对象 DataRow dr; //为数据表添加列 dt.Columns.Add("Id"); dt.Columns.Add("Name"); dt.Columns.Add("NikeName"); dt.Columns.Add("Password"); dt.Columns.Add("Phone"); dt.Columns.Add("Email"); dt.Columns.Add("QQ"); dt.Columns.Add("WeiXin"); dt.Columns.Add("CreateDate"); dt.Columns.Add("UsetType"); dt.Columns.Add("Age"); dt.Columns.Add("Sex"); //循环读取数据 while (reader.Read()) { //读取数据 var id = reader.GetInt64(0); var Name = reader.GetString(1); var Password = reader.GetString(2); var Phone = reader.GetString(3); var Email = reader.GetString(4); var QQ = reader.GetString(5); var WeiXin = reader.GetString(6); var CreateDate = reader.GetDateTime(7); var UsetType = reader.IsDBNull(8) ? 0 : reader.GetInt32(8); var NikeName = reader.GetString(9); var Age = reader.GetInt32(10); var Sex = reader.GetBoolean(11); //为数据表添加行 dr = dt.NewRow(); //为数据行添加值 dr["id"] = id; dr["Name"] = Name; dr["Password"] = Password; dr["Phone"] = Phone; dr["Email"] = Email; dr["QQ"] = QQ; dr["WeiXin"] = WeiXin; dr["CreateDate"] = CreateDate; dr["UsetType"] = UsetType; dr["NikeName"] = NikeName; dr["Age"] = Age; dr["Sex"] = Sex; //添加行到表格 dt.Rows.Add(dr); } //将数据表转换成JSON字符串 result = JsonUnit.DataTableToJson(dt); } //返回JSON字符串 return("{data:" + result + "}"); }