/// <summary> /// 链接数据库并查询 /// </summary> /// <param name="dbLink"></param> /// <param name="dbType"></param> /// <param name="tableName"></param> /// <returns></returns> public JsonResult LinkServer(string dbLink, DbType dbType, string tableName) { ResultInfo result = new ResultInfo(); SqlSugarClient db = GetSugarClient(dbLink, dbType); try { List <DbTableInfo> list = db.DbMaintenance.GetTableInfoList(false); if (!string.IsNullOrEmpty(tableName)) //模糊查询 { List <DbTableInfo> tableList = list.Where <DbTableInfo>(t => t.Name.Contains(tableName) || t.Description.Contains(tableName)).ToList(); result.info = tableList; } else { result.info = list; } //获取客户端的Cookie对象 HttpCookie c*k = Request.Cookies["MyData"]; if (c*k != null) { //修改Cookie的两种方法 c*k.Values["dbLink"] = Base64Helper.Encode(dbLink); c*k.Values["dbType"] = dbType.ToString(); Response.AppendCookie(c*k); } else { HttpCookie cookie = new HttpCookie("MyData"); //初使化并设置Cookie的名称 DateTime dt = DateTime.Now; TimeSpan ts = new TimeSpan(2, 0, 0, 0, 0); //过期时间为1分钟 cookie.Expires = dt.Add(ts); //设置过期时间 cookie.Values.Add("dbLink", Base64Helper.Encode(dbLink)); cookie.Values.Add("dbType", dbType.ToString()); Response.AppendCookie(cookie); } db.Dispose(); db.Close(); result.res = true; result.msg = "链接成功!"; } catch (Exception ex) { db.Dispose(); db.Close(); result.msg = ex.Message; } return(Json(result, JsonRequestBehavior.AllowGet)); }
public void FreeMe() { try { db.Ado.RollbackTran(); } catch { } try { db.Close(); } catch { } }
/// <summary> /// 创建新表,返回影响的记录数 /// 本方法只用于创建包含联合主键的表 /// </summary> /// <typeparam name="TableClass">自定义表格类</typeparam> /// <param name="sugarClient">SqlSugarClient</param> /// <param name="tableName">表名,为空值则为默认值</param> /// <returns>影响的记录数</returns> public static int CreateTable <TableClass>(SqlSugarClient sugarClient, string tableName = null) { if (sugarClient == null) { throw new NullReferenceException("null SqlSugarClient"); } using IDbCommand cmd = sugarClient.Ado.Connection.CreateCommand(); //检查表名 if (string.IsNullOrEmpty(tableName)) { tableName = SugarTableUtils.GetTableName <TableClass>(); } //写入创建新表指令 cmd.CommandText = $"CREATE TABLE {tableName} ("; PropertyInfo[] properties = typeof(TableClass).GetProperties(); int i = 0; List <string> primaryKeys = new(); bool haveIdentity = false; foreach (var colInfo in properties) { i++; //写入字段信息 cmd.CommandText += $"{SugarColUtils.GetColName(colInfo)} " + $"{SugarColUtils.GetColType(colInfo)} " + $"{SugarColUtils.ColIsNullable(colInfo)} " + $"{SugarColUtils.ColIsIdentity(colInfo)}"; if (i != properties.Length) { cmd.CommandText += ","; } if (SugarColUtils.ColIsPrimaryKey(colInfo) && string.IsNullOrEmpty(SugarColUtils.ColIsIdentity(colInfo)) ) { primaryKeys.Add(SugarColUtils.GetColName(colInfo)); } if (!string.IsNullOrEmpty(SugarColUtils.ColIsIdentity(colInfo))) { haveIdentity = true; } } if (primaryKeys.Count != 0 && !haveIdentity) //当有多主键时 { cmd.CommandText += $",PRIMARY KEY({string.Join(",", primaryKeys)})"; } cmd.CommandText += ")"; //检查数据库链接 sugarClient.Ado.CheckConnection(); int ret = cmd.ExecuteNonQuery(); if (!sugarClient.CurrentConnectionConfig.IsAutoCloseConnection) { sugarClient.Close(); } return(ret); }
/// <summary> /// 断开数据库 /// </summary> public static void DiscontectedDataBase() { if (_db != null) { _db.Close(); } }
public NoteDatabase() { // Check database existence if (File.Exists(DatabaseFile)) { File.Delete(DatabaseFile); } // Connect to a new created Sqlite instance // CAUTION: CANNOT HANDLE IN-MEMORY INSTANCE! database = new SqlSugarClient(new ConnectionConfig() { ConnectionString = "Data Source=" + DatabaseFile, DbType = DbType.Sqlite, IsAutoCloseConnection = true, InitKeyType = InitKeyType.Attribute }); // Create tables of Note and KnowledgeBase database.CodeFirst.InitTables(typeof(Note)); database.CodeFirst.InitTables(typeof(KnowledgeBase)); // Test table creation try { var all = database.Queryable <Note>().ToList(); var kb = database.Queryable <KnowledgeBase>().InSingle("Math"); } catch (Exception InitTableFailure) { Console.WriteLine(InitTableFailure.Message); database.Close(); } }
public void BindDgv() { SqlSugarClient db = null; try { db = SqlLiteHelper.GetInstance(); if (!File.Exists(Device.DB_FilePath)) { MessageBox.Show("文件下载中,请稍后再试!"); return; } var sourceList = db.Queryable <SYS_USERROOM, SYS_MODELS>((u, m) => new object[] { JoinType.Left, u.GUID == m.GUID }) .Select((u, m) => new { u.GUID, u.NAME, u.CREATETIME, m.MODELID, m.FEATURE, u.STARTTIME, u.ENDTIME, u.AREACODE, u.AREANAME, u.USERTYPE, }); if (!string.IsNullOrWhiteSpace(txtName.Text)) { sourceList = sourceList.Where(u => u.NAME.Contains(txtName.Text)); } TotalCount = sourceList.Count(); if (PageIndex <= 1) { PageIndex = 1; } var maxPageIndex = Math.Ceiling((decimal)TotalCount / (decimal)PageSize); if (PageIndex >= maxPageIndex) { PageIndex = maxPageIndex.ToInt32(); } var skip = (PageIndex - 1) * PageSize; sourceList = sourceList.Take(PageSize).Skip(skip) .OrderBy(u => u.CREATETIME, OrderByType.Desc); dataGridView1.AutoGenerateColumns = false; dataGridView1.DataSource = sourceList.ToList(); lblTotalCount.Text = $"第{PageIndex}页,每页{PageSize}条,总数:" + TotalCount; } catch (Exception ex) { MessageBox.Show("文件下载中,请稍后再试!"); } db.Close(); db.Dispose(); System.Data.SQLite.SQLiteConnection.ClearAllPools(); }
/// <summary> /// 连接状态 /// </summary> public static bool OnlineState() { try { sqlSugarClient.Open(); sqlSugarClient.Close(); return(true); } catch (Exception ex) { _logger.Error("连接数据库失败", ex); return(false); } }
/// <summary> /// 连接菜单 /// </summary> /// <param name="sender"></param> /// <param name="e"></param> private void 增加新连接ToolStripMenuItem_Click(object sender, EventArgs e) { // 打开连接对话框 读取配置文件 测试连通性 选择数据库类型 5种类型 输入数据库连接参数 FrmConnection fc = new FrmConnection(); if (DialogResult.Yes != fc.ShowDialog()) { return; } // 获取填写的配置信息 dbparam = fc.selectDbParm; if (dbClient != null) { dbClient.Close(); } // 根据填写的配置 获取数据客户端 dbClient = GetDBInstance(dbparam); if (dbClient == null) { Error("找不到连接对象,请检查填写的配置信息"); return; } lblDbName.ForeColor = Color.Black; lblDbName.BackColor = label7.BackColor; lblDbType.ForeColor = Color.Black; lblDbType.BackColor = label7.BackColor; // 显示数据库类型 lblDbType.Text = dbparam.DbType; // 显示数据库名称 lblDbName.Text = dbparam.DbName; StringBuilder sb = new StringBuilder(); sb.Append("Connection String :"); sb.Append(dbClient.Context.Ado.Connection.ConnectionString); Info(sb.ToString()); // 获取数据库列表 GetTableInfo(); }
/// <summary> /// 执行sql语句 /// </summary> /// <param name="sugarClient">sugarClient</param> /// <param name="command">sql指令</param> /// <returns>影响的记录数</returns> public static int ExecuteSql(SqlSugarClient sugarClient, string command) { if (sugarClient == null) { throw new NullReferenceException("null SqlSugarClient"); } //使用Ado获取控制台执行指令 using IDbCommand cmd = sugarClient.Ado.Connection.CreateCommand(); cmd.CommandText = command; //检查数据库链接 sugarClient.Ado.CheckConnection(); int ret = cmd.ExecuteNonQuery(); if (!sugarClient.CurrentConnectionConfig.IsAutoCloseConnection) { sugarClient.Close(); } return(ret); }
/** * 關閉連線 */ public bool Disconnect() { bool ret = false; try { // 關閉連線 sql.Close(); ret = true; SaveLog($"[Info] Disconnect Data Base Success"); } catch (Exception ex) { SaveLog($"[Error] DataBaseConnect::Disconnect, Disconnect Data Base Fail, Catch Msg: {ex.Message}"); } return(ret); }
public void Select() { SqlSugarClient db = new SqlSugarClient(new ConnectionConfig() { ConnectionString = Config.ConnectionString, DbType = DbType.Sqlite, IsAutoCloseConnection = false }); db.IgnoreColumns.Add("TestId", "Student"); db.Queryable <Student>().Select(it => new ViewModelStudent2 { Name = it.Name, Student = it }).ToList(); base.Execute("sqlsuagr", () => { var test = db.Queryable <Student>().Select(it => new ViewModelStudent2 { Name = it.Name, Student = it }).ToList(); }); db.Close(); }
/** * 關閉連線 */ public bool Disconnect() { bool bReturn = false; try { // 關閉連線 sql.Close(); bReturn = true; log.SaveLog("[Info] Disconnect SQL Success"); } catch (Exception ex) { log.SaveLog("[Error] DataBaseConnect::Disconnect SQL Disconnect Fail, Error Msg:" + ex.Message); } return bReturn; }
/// <summary> /// 删除表 /// </summary> /// <typeparam name="TableClass">自定义表格类</typeparam> /// <param name="sugarClient">SqlSugarClient</param> /// <param name="tableName">表名,为空值则为默认值</param> /// <returns>影响的记录数</returns> public static int DeletTable <TableClass>(SqlSugarClient sugarClient, string tableName = null) { if (sugarClient == null) { throw new NullReferenceException("null SqlSugarClient"); } using IDbCommand cmd = sugarClient.Ado.Connection.CreateCommand(); //检查表名 if (string.IsNullOrEmpty(tableName)) { tableName = SugarTableUtils.GetTableName <TableClass>(); } cmd.CommandText = $"DROP TABLE {tableName}"; //检查数据库链接 sugarClient.Ado.CheckConnection(); int ret = cmd.ExecuteNonQuery(); if (sugarClient.CurrentConnectionConfig.IsAutoCloseConnection) { sugarClient.Close(); } return(ret); }
public void Dispose() { db.Close(); }
/// <summary> /// 处理 /// </summary> public void Dispose() { RollbackTran(); DbContext.Close(); }
//IServiceProvider This method gets called by the runtime. Use this method to add services to the container. public void ConfigureServices(IServiceCollection services) { WCSApiAccessor.Host = Configuration["Host:WCS"]; MESApiAccessor.Host = Configuration["Host:MES"]; WMSBaseApiAccessor.WMSProxy = Configuration["Host:WMSPROXY"]; services.AddMvc(option => { option.Filters.Add <BaseExceptionAttribute>(); //option.Filters.Add<FilterXSSAttribute>(); option.Conventions.Add(new ApplicationDescription("title", Configuration["sys:title"])); option.Conventions.Add(new ApplicationDescription("company", Configuration["sys:company"])); option.Conventions.Add(new ApplicationDescription("customer", Configuration["sys:customer"])); }).SetCompatibilityVersion(CompatibilityVersion.Latest); services.AddSwaggerGen(c => { c.SwaggerDoc("v1", new Info { Title = "API", Version = "v1" }); }); //services.Configure<CookiePolicyOptions>(options => //{ // // This lambda determines whether user consent for non-essential cookies is needed for a given request. // options.CheckConsentNeeded = context => true; // options.MinimumSameSitePolicy = SameSiteMode.None; //}); services.AddTimedJob(); services.AddOptions(); services.AddXsrf(); services.AddXss(); services.AddAuthentication(c => { c.DefaultAuthenticateScheme = CookieAuthenticationDefaults.AuthenticationScheme; c.DefaultChallengeScheme = CookieAuthenticationDefaults.AuthenticationScheme; }).AddCookie(cfg => { cfg.LoginPath = "/Login/Index"; cfg.LogoutPath = "/Login/Logout"; //cfg.ExpireTimeSpan = TimeSpan.FromDays(1); //cfg.Cookie.Expiration = TimeSpan.FromDays(1); cfg.Cookie.Name = CookieAuthenticationDefaults.AuthenticationScheme; cfg.Cookie.Path = "/"; cfg.Cookie.HttpOnly = true; //cfg.SlidingExpiration = true; }); var sqlSugarConfig = SqlSugarConfig.GetConnectionString(Configuration); services.AddSqlSugarClient <SqlSugarClient>(config => { config.ConnectionString = sqlSugarConfig.Item2; config.DbType = sqlSugarConfig.Item1; config.IsAutoCloseConnection = true; config.InitKeyType = InitKeyType.Attribute; //config.IsShardSameThread = false; }); services.AddJson(o => { o.JsonType = JsonType.Jil; }); services.AddSoapCore(); services.AddDIProperty(); services.AddHttpContextAccessor(); services.AddHtmlEncoder(); Encoding.RegisterProvider(CodePagesEncodingProvider.Instance); services.AddBr(); //br压缩 services.AddResponseCompression(); //添加压缩 services.AddResponseCaching(); //响应式缓存 services.AddMemoryCache(); services.AddMediatR(typeof(Startup).GetTypeInfo().Assembly); //@1 DependencyInjection 注册 services.AddNlog(); //添加Nlog RegisterBase(services); ServiceExtension.RegisterAssembly(services, "Services"); ServiceExtension.RegisterAssembly(services, "Repository"); var bulid = services.BuildServiceProvider(); ServiceResolve.SetServiceResolve(bulid); SqlSugarClient db = new SqlSugarClient(new ConnectionConfig() { DbType = sqlSugarConfig.Item1, ConnectionString = sqlSugarConfig.Item2, InitKeyType = InitKeyType.Attribute, IsAutoCloseConnection = true, }); //OutputDataBase(db); InitDataBase(db); InitSystemData(db); db.Close(); }