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