public ActionResult GetByNumber(string number)
{
DapperManager dapp = new DapperManager(ConfigurationManager.ConnectionStrings["DefaultConnection"].ConnectionString);
try
{
ClientModel cln;
DapperParameters pars = new DapperParameters();
pars["@phone_number"] = number;
List<ClientModel> result = dapp.ExecuteProcedure<ClientModel>("up_GetClientByPhoneNumber", pars);
if (result.Count > 0)
cln = result[0];
else
cln = new ClientModel() { Name = "NOT FOUND" };
return View(cln);
}
catch (Exception ex)
{
ErrorModel err = new ErrorModel()
{ Error = ex.Message, StackTrace = ex.StackTrace, Controller = "Client", Method = "GetByNumber", Overload = "ActionResult => string", ID = 1, Note = "" };
return View("Error");
}
finally
{
if (dapp != null)
dapp.Dispose();
}
}
static void Main(string[] args)
{
NDapperLog.Receive += msg;
string ConnectionStr = "server=(local);UID=sa;PWD=sa;database=test";
string mysqlhost = "Host = 127.0.0.1; UserName = root; Password = @; Database = KLine; Port = 3316; CharSet = utf8; Allow Zero Datetime = true;";
NDapper dbss = DapperManager.CreateDatabase(ConnectionStr, DBType.SqlServer);
var state = dbss.State();
var list = dbss.Query <KLine>("select id,symbol from pp2009_min10");
NDapper dbss1 = DapperManager.CreateDatabase(ConnectionStr, DBType.SqlServer);
var list1 = dbss1.QueryAsync <KLine>("select id,symbol from pp2009_min11");
NDapper dbss2 = DapperManager.CreateDatabase(ConnectionStr, DBType.SqlServer);
var list2 = dbss2.Query <KLine>("select id,symbol from pp2009_min12");
KLine k = new KLine();
dbss2.Query <KLine>("select UserName,Email from User where UserId=@UserId", new KLine()
{
ID = 1
});
NDapper dbSqlLite = DapperManager.CreateDatabase(@"symbo3.db", DBType.SqlLite);
//ConnectionState DapperState = dbSqlLite.State();
string createtb = "create table hsi1903_min1 (id int, symbol varchar(50))";
int x = dbSqlLite.Execute(createtb);
int xs = dbSqlLite.Execute("insert into hsi1903_min1(id,symbol)values('1','122')");
var listSS = dbSqlLite.Query <KLine>("select id,symbol from hsi1903_min1");
Console.ReadKey();
}
static void Main(string[] args)
{
string ConnectionStr = "server=122.114.170.153,4004;UID=dxwang;PWD=qq520223;database=test";
ConnectionStr = "server=(local);UID=dxwang;PWD=qq520223;database=test";
string host = "Host = 101.132.40.166; UserName = root; Password = @Hy201919; Database = KLine; Port = 3316; CharSet = utf8; Allow Zero Datetime = true;";
NDapper dbss = DapperManager.CreateDatabase(ConnectionStr, DBType.SqlServer);
ConnectionState state = dbss.State();
var list = dbss.Query <KLine>("select id,symbol from pp2009_min10");
NDapper dbss1 = DapperManager.CreateDatabase(ConnectionStr, DBType.SqlServer);
var list1 = dbss1.Query <KLine>("select id,symbol from pp2009_min11");
NDapper dbss2 = DapperManager.CreateDatabase(ConnectionStr, DBType.SqlServer);
var list2 = dbss2.Query <KLine>("select id,symbol from pp2009_min12");
////Database
//DapperBase db = CreateDatabase.CreateDatabase(host, DBType.MySql);
Console.ReadKey();
//SqlLite
NDapper dbSqlLite = DapperManager.CreateDatabase(@"symbo2.db", DBType.SqlLite);
//ConnectionState DapperState = dbSqlLite.State();
string createtb = "create table hsi1903_min1 (id int, symbol varchar(50))";
int x = dbSqlLite.Execute(createtb);
int xs = dbSqlLite.Execute("insert into hsi1903_min1(id,symbol)values('1','122')");
List <KLine> listSS = dbSqlLite.Query <KLine>("select id,symbol from hsi1903_min1").ToList();
}
public ActionResult Login(LoginViewModel model, string returnUrl)
{
IsUsedReturnUrl = false;
if (ModelState.IsValid)
{
IEnumerable <User> result = DapperManager.Query <User>("MVC5TemplateServer",
"SELECT UserID, FamilyName, FirstName FROM [dbo].[User] WHERE UserID = @UserID AND Password = @Password",
new { UserID = model.UserID, Password = model.Password });
if (result.Count() != 0)
{
Session.Add("UserInfo", result.First());
FormsAuthentication.RedirectFromLoginPage(model.UserID, false);
//FormsAuthentication.SetAuthCookie(model.UserID, false);
if (IsUsedReturnUrl &&
Url.IsLocalUrl(returnUrl) &&
returnUrl.Length > 1 &&
returnUrl.StartsWith("/") &&
!returnUrl.StartsWith("//") &&
!returnUrl.StartsWith("/\\"))
{
return(this.Redirect(returnUrl));
}
return(RedirectToAction(string.Empty, "Home"));
}
else
{
ModelState.AddModelError(
"Login_Error",
HttpContext.GetGlobalResourceObject("ResourceError", "Login_LoginError_ErrorMessage").ToString());
}
}
return(View(model));
}
public ActionResult DeleteConfirmed(int id)
{
int result = DapperManager.Execute(
"MVC5TemplateServer",
"DELETE FROM [dbo].[User] WHERE UserID = @UserID", new { UserID = id });
return(RedirectToAction("Index"));
}
public ActionResult Index()
{
IEnumerable <User> result = DapperManager.Query <User>(
"MVC5TemplateServer",
"SELECT * FROM [dbo].[User]");
return(View(result));
}
public ActionResult Edit([Bind(Include = "UserID,Password,FamilyName,FirstName,Birthday,Sex,MobilePhone,PostalCode,Prefectures,City,Address1,Address2,Apartment")] User user)
{
if (ModelState.IsValid)
{
int result = DapperManager.Execute("MVC5TemplateServer", "UPDATE [dbo].[User] SET Password = @Password, FamilyName = @FamilyName, FirstName = @FirstName, Birthday = @Birthday, Sex = @Sex, MobilePhone = @MobilePhone, PostalCode = @PostalCode, Prefectures = @Prefectures, City = @City, Address1 = @Address1, Address2 = @Address2, Apartment = @Apartment WHERE UserID = @UserID", user);
return(RedirectToAction("Index"));
}
return(View(user));
}
/// <summary>
/// 更新美食模块的URL
/// </summary>
public void UpdateMeiShi()
{
HtmlAgilityPack.HtmlDocument doc = null;
IDbConnection conn = null;
try
{
conn = DapperManager.GetConnection();
conn.Open();
var list_source = conn.Query <City>("SELECT ID,Name,URL,MeiShiURL FROM T_City WHERE State = @State", new { State = 1 });
//一个一个访问URL，并获取美食模块的URL
for (int i = 0; i < list_source.Count(); i++)
{
if (list_source.ElementAt(i).MeiShiURL != null)
{
continue;
}
try
{
doc = webClient.Load(list_source.ElementAt(i).URL);
}
catch (System.Net.WebException webEx)
{
listBox1.Invoke(new Action(() =>
{
listBox1.Items.Insert(0, webEx.Message);
}));
continue;
}
var hrefList = doc.DocumentNode.SelectNodes("//div[@class='category-nav-content-wrapper']//a[@href]");
foreach (var item in hrefList)
{
if (item.InnerText.Equals("美食"))
{
var MeiShiURL = item.Attributes["href"].Value;
list_source.ElementAt(i).MeiShiURL = MeiShiURL;
var count = conn.Execute("UPDATE T_City SET MeiShiURL = @MeiShiURL WHERE ID = @ID", list_source.ElementAt(i));
listBox1.Invoke(new Action(() =>
{
listBox1.Items.Insert(0, $"{DateTime.Now } 更新了{list_source.ElementAt(i).Name}的美食地址");
}));
break;
}
}
}
}
catch (Exception ex)
{
MyLog.logger.Error(ex.Message);
}
finally
{
conn.Close();
}
//hrefList = doc.DocumentNode.SelectNodes("//div[@id='app']//a[@href]");
}
static void ListRole()
{
using (var conn = DapperManager.GetConnection())
{
foreach (var item in conn.GetAll <RoleEntity>())
{
Console.WriteLine($"{item.Id,-10}{item.Name,-10}");
}
}
}
public ActionResult Create([Bind(Include = "UserID,Password,FamilyName,FirstName,Birthday,Sex,MobilePhone,PostalCode,Prefectures,City,Address1,Address2,Apartment")] User user)
{
if (ModelState.IsValid)
{
int result = DapperManager.Execute(
"MVC5TemplateServer",
"INSERT [dbo].[User] (UserID, Password, FamilyName, FirstName, Birthday, Sex, MobilePhone, PostalCode, Prefectures, City, Address1, Address2, Apartment) VALUES (@UserID, @Password, @FamilyName, @FirstName, @Birthday, @Sex, @MobilePhone, @PostalCode, @Prefectures, @City, @Address1, @Address2, @Apartment)", user);
return(RedirectToAction("Index"));
}
return(View(user));
}
public ActionResult Edit(int?id)
{
if (id == null)
{
return(new HttpStatusCodeResult(HttpStatusCode.BadRequest));
}
User user = DapperManager.Query <User>("MVC5TemplateServer",
"SELECT * FROM [dbo].[User] WHERE UserID = @UserID",
new { UserID = id }).FirstOrDefault();
if (user == null)
{
return(HttpNotFound());
}
return(View(user));
}
static void CreateRole(string roleName)
{
using (var conn = DapperManager.GetConnection())
{
var maxId = 1;
var role = new RoleEntity()
{
Id = maxId,
Name = roleName
};
var roles = conn.GetAll <RoleEntity>().ToList();
if (roles.Any())
{
maxId = roles.Max(r => r.Id);
role.Id = maxId << 1;
}
conn.Insert(role);
Console.WriteLine($"insert role: {roleName} => succeed!");
ListRole();
}
}
private void 更新城市ToolStripMenuItem_Click(object sender, EventArgs e)
{
listBox1.Items.Insert(0, $"{DateTime.Now.ToShortTimeString()} 请求数据中...");
try
{
LoadCity();
IDbConnection conn = null;
try
{
conn = DapperManager.GetConnection();
conn.Open();
_cancel_token = new CancellationTokenSource();
var list_source = conn.Query <City>("SELECT ID,Name,URL,MeiShiURL FROM T_City WHERE State = @State", new { State = 1 });
var offset = list_source.Count() / 3;
Task task = new Task(() => {
UpdateMeiShi(list_source.Take(offset).ToList(), list_source.Count());
});
task.Start();
Task task2 = new Task(() => {
UpdateMeiShi(list_source.Skip(offset).Take(offset).ToList(), list_source.Count());
});
task2.Start();
Task task3 = new Task(() => {
UpdateMeiShi(list_source.Skip(offset * 2).ToList(), list_source.Count());
});
task3.Start();
}
catch (Exception ex)
{
MyLog.logger.Error(ex.Message);
}
}
catch (Exception ex)
{
MyLog.logger.Error(ex.Message);
}
}
public MiscRepository()
{
dapper = new DapperManager();
}
public SchoolRepo(DapperManager dapper)
{
_dapper = dapper;
}
public GoodWeeRepository()
{
dapper = new DapperManager();
}
// GET: api/User/5
public IEnumerable <User> Get(int id)
{
return(DapperManager.Query <User>("MVC5TemplateServer", "SELECT * FROM [dbo].[User] WHERE UserID = @UserID AND Password = @Password", new { UserID = id }));
}
/// <summary>
/// 更新美食模块的URL
/// </summary>
public void UpdateMeiShi(List <City> list_source, int total)
{
HtmlAgilityPack.HtmlDocument doc = null;
IDbConnection conn = null;
try
{
conn = DapperManager.GetConnection();
//一个一个访问URL，并获取美食模块的URL
for (int i = 0; i < list_source.Count(); i++)
{
if (_cancel_token.IsCancellationRequested)
{
numberOfMeishi = 0;
return;
}
try
{
doc = _webClient.Load(list_source.ElementAt(i).URL);
}
catch (System.Net.WebException ex)
{
listBox1.Invoke(new Action(() =>
{
listBox1.Items.Insert(0, $"{DateTime.Now} 更新{list_source.ElementAt(i).Name}的美食地址失败,请查看错误日志,{numberOfMeishi}/{total}");
}));
MyLog.logger.Error(ex.Message);
continue;
}
finally
{
listBox1.Invoke(new Action(() =>
{
listBox1.Items.Insert(0, $"{DateTime.Now} 更新了{list_source.ElementAt(i).Name}的美食地址,{numberOfMeishi}/{total}");
}));
Interlocked.Increment(ref numberOfMeishi);
}
var hrefList = doc.DocumentNode.SelectNodes("//div[@class='category-nav-content-wrapper']//a[@href]");
foreach (var item in hrefList)
{
//可能会有很多模块，比如美食，外卖，酒店，电影，机票等，这里只取美食模块的数据
if (item.InnerText.Equals("美食"))
{
var MeiShiURL = item.Attributes["href"].Value;
list_source.ElementAt(i).MeiShiURL = MeiShiURL;
var count = conn.Execute("UPDATE T_City SET MeiShiURL = @MeiShiURL WHERE ID = @ID", list_source.ElementAt(i));
break;
}
}
}
}
catch (Exception ex)
{
MyLog.logger.Error(ex.Message);
}
finally
{
conn.Close();
}
}
/// <summary>
/// 获取所有城市列表
/// </summary>
public void LoadCity()
{
HtmlAgilityPack.HtmlDocument doc = null;
try
{
//加载城市列表
doc = _webClient.Load(Global.uri_city);
}
catch (Exception ex)
{
MyLog.logger.Error(ex.Message);
}
//Dom节点获取列表
HtmlNodeCollection hrefList = doc.DocumentNode.SelectNodes("//div[@id='app']//a[@href]");
List <City> list_target = new List <City>();
for (int i = 0; i < hrefList.Count; i++)
{
var url = hrefList[i].Attributes["href"];
if (i == 0)
{
listBox1.Invoke(new Action(() =>
{
listBox1.Items.Insert(0, $"导入{i + 1}/{hrefList.Count}条数据");
}));
}
else
{
listBox1.Items[0] = $"导入{i + 1}/{hrefList.Count}条数据";
}
list_target.Add(new City()
{
URL = url.Value.Insert(0, "http:"),
Name = hrefList[i].InnerText
});
}
int count = 0;
listBox1.Invoke(new Action(() =>
{
listBox1.Items.Insert(0, $"{DateTime.Now.ToShortTimeString()} 导入数据中...");
}));
IDbConnection conn = null;
try
{
conn = DapperManager.GetConnection();
conn.Open();
var list_source = conn.Query <City>("SELECT Name,URL FROM T_City");
var list_except = list_target.Except(list_source);
count = conn.Execute("INSERT INTO T_City(Name,URL) VALUES(@Name,@URL)", list_except);
}
catch (Exception ex)
{
MyLog.logger.Error(ex.Message);
}
finally
{
conn.Close();
}
listBox1.Invoke(new Action(() =>
{
listBox1.Items.Insert(0, $"{DateTime.Now.ToShortTimeString()} 解析完毕，导入{count}条数据...");
}));
}
public GraphRepository()
{
dapper = new DapperManager();
}
// GET: api/User
public IEnumerable <User> Get()
{
return(DapperManager.Query <User>("MVC5TemplateServer", "SELECT * FROM [dbo].[User]"));
}
public SunGrowRepository()
{
dapper = new DapperManager();
}
public GrowWattRepository()
{
dapper = new DapperManager();
}
public VendorRepository()
{
dapper = new DapperManager();
}
