/// <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>
/// <param name="customerJson">广告信息的JSON字符串</param>
/// <returns></returns>
public static bool AddAdviertisement(string json)
{
//插入数据的结果
bool result = false;
//将JSON字符串转换成键值对(字典)
var obj = JsonUnit.toDictionary(json);
//声明插入客户信息的语句
var sql = @"
INSERT INTO dbo.ZJHL_Adviertisements( Name,Title,GG_Image,GG_Content,CreateDate,CreateId)" +
string.Format("VALUES ( '{0}','{1}','{2}','{3}','{4}','{5}')",
obj["Name"], obj["Title"], obj["GG_Image"], obj["GG_Content"], obj["CreateDate"], obj["CreateId"]);
//实例化数据库连接对象
SqlConnection connect = new SqlConnection(connetStr);
//打开数据连接
connect.Open();
if (connect.State != System.Data.ConnectionState.Open)
{
Console.WriteLine("打开数据库失败");
}
else
{
//实例化SQL命令执行对象，输入SQL语句(sql)和数据库连接对象(connect)
SqlCommand cmd = new SqlCommand(sql, connect);
//执行语句并返回执行结果的第一行第一列
result = cmd.ExecuteNonQuery() > 0;
}
return(result);
}
/// 插入客户信息
/// </summary>
/// <param name="customerJson">客户信息的JSON字符串</param>
/// <returns></returns>
public static bool AddCustomer(string customerJson)
{
//插入数据的结果
bool result = false;
//将JSON字符串转换成键值对(字典)
var obj = JsonUnit.toDictionary(customerJson);
//声明插入客户信息的语句
var sql = @"
INSERT INTO dbo.ZJHL_Customers(
UserName,Name ,Phone ,Sex ,Address ,Area ,Remark ,Company ,
QQ ,MailBox ,Contact ,Password ,CompanyLogo ,CompanyLogoMini
)" +
string.Format("VALUES ( '{0}','{1}','{2}','{3}','{4}','{5}','{6}','{7}','{8}','{9}','{10}','{11}','{12}','{13}')",
obj["UserName"], obj["Name"], obj["Phone"], obj["Sex"], obj["Address"], obj["Area"], obj["Remark"], obj["Company"],
obj["QQ"], obj["MailBox"], obj["Contact"], obj["Password"], obj["CompanyLogo"], obj["CompanyLogoMini"]);
//实例化数据库连接对象
SqlConnection connect = new SqlConnection(connetStr);
//打开数据连接
connect.Open();
if (connect.State != System.Data.ConnectionState.Open)
{
Console.WriteLine("打开数据库失败");
}
else
{
//实例化SQL命令执行对象，输入SQL语句(sql)和数据库连接对象(connect)
SqlCommand cmd = new SqlCommand(sql, connect);
//执行语句并返回执行结果的第一行第一列
result = cmd.ExecuteNonQuery() > 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>
/// <param name="customerJson">用户信息的JSON字符串</param>
/// <returns></returns>
public static bool AddUser(string userJson)
{
//插入数据的结果
bool result = false;
//将JSON字符串转换成键值对(字典)
var obj = JsonUnit.toDictionary(userJson);
//声明插入客户信息的语句
var sql = @"
INSERT INTO dbo.ZJHL_Users(
Name ,
Password,
Phone ,
MailBox ,
QQ ,
WeiXin ,
CreateDate,
Sex,
NikeName,
Remark,
Age
)" +
string.Format("VALUES ( '{0}','{1}',{2},'{3}','{4}','{5}','{6}',{7},'{8}','{9}',{10})",
obj["Name"],
obj["Password"],
obj["Phone"],
obj["MailBox"],
obj["QQ"],
obj["WeiXin"],
DateTime.Now,
obj["Sex"],
obj["NikeName"],
obj["Remark"],
obj["Age"]);
//实例化数据库连接对象
SqlConnection connect = new SqlConnection(connetStr);
//打开数据连接
connect.Open();
if (connect.State != System.Data.ConnectionState.Open)
{
Console.WriteLine("打开数据库失败");
}
else
{
//实例化SQL命令执行对象，输入SQL语句(sql)和数据库连接对象(connect)
SqlCommand cmd = new SqlCommand(sql, connect);
//执行语句并返回执行结果的第一行第一列
result = cmd.ExecuteNonQuery() > 0;
}
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 + "}");
}
