public Helper(IConfiguration connection)
{
_connection = connection;
// Setup dapper
SimpleCRUD.SetDialect(SimpleCRUD.Dialect.PostgreSQL);
}
public virtual int RemoveAsTransactional(int id, IDbConnection conn, IDbTransaction transaction)
{
SimpleCRUD.SetDialect(SimpleCRUD.Dialect.PostgreSQL);
int recordCount = conn.Delete <T>(id, transaction);
return(recordCount);
}
protected override void ApplicationStartup(TinyIoCContainer container, IPipelines pipelines)
{
base.ApplicationStartup(container, pipelines);
SimpleCRUD.SetDialect(SimpleCRUD.Dialect.MySQL);
pipelines.BeforeRequest.AddItemToEndOfPipeline(context =>
{
Database db = new Database();
context.Items.Add("db", db);
db.OpenDatabase();
return(context.Response);
});
pipelines.AfterRequest.AddItemToEndOfPipeline(context =>
{
context.GetDb()?.CloseDatabase();
});
pipelines.OnError += (ctx, e) =>
{
App.log.Error($"{e.Message}\r\n{e.StackTrace}");
return(null);
};
}
static void Main(string[] args)
{
PIPoint pIPoint = new PIPoint();
PropertyInfo[] propertys = pIPoint.GetType().GetProperties();
string jsonFile = "D:/vs2019proj/PIData/PIDataSave/PIDataSave/tagName.json";
using (System.IO.StreamReader file = System.IO.File.OpenText(jsonFile))
{
using (JsonTextReader reader = new JsonTextReader(file))
{
JArray jArray = (JArray)JToken.ReadFrom(reader);
foreach (JObject o in jArray)
{
IEnumerable <JProperty> properties = o.Properties();
foreach (JProperty item in properties)
{
string key = item.Name;
JToken val = item.Value;
JObject obj = JObject.Parse(val.ToString());
foreach (PropertyInfo property in propertys)
{
if (property.Name != "id" && property.Name != "time" && obj.ContainsKey(property.Name))
{
string a = obj[property.Name].ToString();
string value = WebService.PIRead(
$"select value from piarchive..piavg where tag = '{a}' and time BETWEEN '*-1h' AND '*'",
key);
JArray arr = (JArray)JsonConvert.DeserializeObject(value);
foreach (JObject jo in arr)
{
string joValue = jo.Value <string>("value");
property.SetValue(pIPoint, double.Parse(joValue), null);
}
}
}
}
}
}
}
pIPoint.time = DateTime.Now.ToLocalTime().ToString();
//Program.WebService.PIRead("", "");
string dbPath = "Data Source =" + Environment.CurrentDirectory + "/test.db";
//conn.Close();
SimpleCRUD.SetDialect(SimpleCRUD.Dialect.SQLite);
using (IDbConnection db = new SQLiteConnection(dbPath))
{
db.Open();
IDbTransaction tran = db.BeginTransaction();
db.Insert(pIPoint, transaction: tran);
tran.Commit();
}
}
/// <summary>
/// 获取数据库中的表
/// </summary>
/// <param name="connectionstring"></param>
/// <param name="databaseName"></param>
/// <returns></returns>
public List <Table> GetTableList(string connectionstring, string databaseName)
{
SimpleCRUD.SetDialect(SimpleCRUD.Dialect.Oracle);
using (IDbConnection conn = SqlConnectionHelper.GetOpenConnection(connectionstring))
{
List <Table> tables = new List <Table>();
string sqlCmd = "SELECT table_name,cluster_name FROM user_tables";
var dr = conn.ExecuteReader(sqlCmd);
while (dr.Read())
{
string id = dr.GetString(0);
string displayName = dr.GetString(0);
string name = dr.GetString(0);
string comment = dr.IsDBNull(1) ? string.Empty : dr.GetString(1);
Table table = new Table(id, displayName, name, comment)
{
OriginalName = name
};
tables.Add(table);
}
dr.Close();
return(tables);
}
}
/// <summary>
/// GetKeys
/// </summary>
/// <param name="connectionString"></param>
/// <param name="sqlCmd"></param>
/// <returns></returns>
private Columns GetKeys(string connectionString, string sqlCmd)
{
Columns columns = new Columns(5);
SimpleCRUD.SetDialect(SimpleCRUD.Dialect.Oracle);
using (var conn = SqlConnectionHelper.GetOpenConnection(connectionString))
{
var dr = conn.ExecuteReader(sqlCmd);
while (dr.Read())
{
string id = dr.IsDBNull(3) ? string.Empty : dr.GetValue(3).ToString();
string displayName = dr.IsDBNull(3) ? string.Empty : dr.GetValue(3).ToString();
string name = dr.IsDBNull(3) ? string.Empty : dr.GetValue(3).ToString();
string dataType = "";
string defaultValue = "";
string isNullable = "Y";
string length = "";
string identity = string.Empty;
string comment = "主键";
Column column = new Column(id, displayName, name, dataType, comment);
column.Length = DataTypeConvertHelper.ToInt(length);
column.IsAutoIncremented = false;
column.IsNullable = isNullable.Equals("Y");
column.DefaultValue = defaultValue.ToEmpty();
column.DataType = dataType;
column.OriginalName = name;
columns.Add(id, column);
}
dr.Close();
return(columns);
}
}
/// <summary>
/// GetViews
/// </summary>
/// <param name="connectionString"></param>
/// <param name="databaseName"></param>
/// <returns></returns>
public List <View> GetViews(string connectionString, string databaseName)
{
SimpleCRUD.SetDialect(SimpleCRUD.Dialect.Oracle);
using (IDbConnection conn = SqlConnectionHelper.GetOpenConnection(connectionString))
{
List <View> views = new List <View>();
string sqlCmd = $"select view_name,editioning_view from user_views";
var dr = conn.ExecuteReader(sqlCmd);
while (dr.Read())
{
string id = dr.GetString(0);
string displayName = dr.GetString(0);
string name = dr.GetString(0);
string comment = string.Empty;
View view = new View(id, displayName, name, comment)
{
OriginalName = name
};
views.Add(view);
}
dr.Close();
return(views);
}
}
public virtual int RemoveListAsTransactional(Object whereConditions, IDbConnection conn, IDbTransaction transaction)
{
SimpleCRUD.SetDialect(SimpleCRUD.Dialect.PostgreSQL);
int recordCount = conn.DeleteList <T>(whereConditions, transaction);
return(recordCount);
}
/// <summary>
/// Throws exception if opening database fails, e.g. due to wrong password.
/// </summary>
/// <returns>Version string as found in database (after it has been created if that is the case).</returns>
public static string InitDB()
{
Console.WriteLine("Initializing database.");
SimpleCRUD.SetDialect(SimpleCRUD.Dialect.SQLite);
string version = null;
if (System.IO.File.Exists(DBFILENAME))
{
Console.WriteLine("Found database file.");
using (var conn = Connection())
{
conn.Open();
// If wrong password, exception will be thrown.
version = QueryMisc("Version", conn);
}
}
else
{
Console.WriteLine("Database file not found. Creating new one.");
SQLiteConnection.CreateFile(DBFILENAME);
CreateDBTables();
version = QueryMisc("Version");
}
return(version);
}
//dialect test
public void ChangeDialect()
{
SimpleCRUD.SetDialect(SimpleCRUD.Dialect.SQLServer);
SimpleCRUD.GetDialect().IsEqualTo(SimpleCRUD.Dialect.SQLServer.ToString());
SimpleCRUD.SetDialect(SimpleCRUD.Dialect.PostgreSQL);
SimpleCRUD.GetDialect().IsEqualTo(SimpleCRUD.Dialect.PostgreSQL.ToString());
}
public PgConnectionFactory() {
Dapper.DefaultTypeMap.MatchNamesWithUnderscores = true;
SimpleCRUD.SetDialect(SimpleCRUD.Dialect.PostgreSQL);
var resolver = new CustomResolver();
SimpleCRUD.SetTableNameResolver(resolver);
SimpleCRUD.SetColumnNameResolver(resolver);
}
//获取打开连接
public static IDbConnection GetOpenConnection()
{
IDbConnection connection;
if (_dbtype == SimpleCRUD.Dialect.SQLite)
{
connection = new SQLiteConnection(ConfigHelper.GetSectionValue("ConnectionStrings:SQLiteConnectionString"));
SimpleCRUD.SetDialect(SimpleCRUD.Dialect.SQLite);
}
else if (_dbtype == SimpleCRUD.Dialect.MySQL)
{
connection = new MySqlConnection(ConfigHelper.GetSectionValue("ConnectionStrings:MySQLConnectionString"));
SimpleCRUD.SetDialect(SimpleCRUD.Dialect.MySQL);
}
else
{
connection =
new SqlConnection(ConfigHelper.GetSectionValue("ConnectionStrings:SQLServerConnectionString"));
SimpleCRUD.SetDialect(SimpleCRUD.Dialect.SQLServer);
}
if (connection.State != ConnectionState.Open)
{
connection.Open();
}
return(connection);
}
/// <summary>
/// Establece el dialecto o tipo de base de datos segun lo establecido en el web config
/// </summary>
private void SelectDialect()
{
try
{
string dialect = "Dialect".ReadKey("0");
switch (dialect)
{
case "0":
SimpleCRUD.SetDialect(SimpleCRUD.Dialect.SQLServer);
break;
case "1":
SimpleCRUD.SetDialect(SimpleCRUD.Dialect.PostgreSQL);
break;
case "3":
SimpleCRUD.SetDialect(SimpleCRUD.Dialect.MySQL);
break;
default:
goto case "0";
}
}
catch (Exception)
{
throw;
}
}
public RepoMapper(string dbfile)
{
connectionString = string.Format("Data Source={0}", dbfile);
CreateDatabaseIfNotExists(dbfile);
SimpleCRUD.SetDialect(SimpleCRUD.Dialect.SQLite);
}
/// <summary>
/// <para>Get open connection</para>
/// <para>if you want to link mysql db,please add SslMode=None; in you link string.</para>
/// </summary>
/// <param name="connectionString">DIV you connection string</param>
/// <returns>IDbConection object</returns>
public static IDbConnection GetOpenConnection(string connectionString = null)
{
var dbtype = SimpleCRUD.GetDialect();
if (!string.IsNullOrEmpty(connectionString))
{
ConnectionString = connectionString;
}
IDbConnection connection;
//if (dbtype == SimpleCRUD.Dialect.PostgreSQL)
//{
// connection = new NpgsqlConnection(ConnectionString);
// SimpleCRUD.SetDialect(SimpleCRUD.Dialect.PostgreSQL);
//}
//else
if (dbtype == SimpleCRUD.Dialect.MySQL)
{
connection = new MySqlConnection(ConnectionString);
SimpleCRUD.SetDialect(SimpleCRUD.Dialect.MySQL);
}
else if (dbtype == SimpleCRUD.Dialect.SQLite)
{
connection = new SQLiteConnection(ConnectionString);
SimpleCRUD.SetDialect(SimpleCRUD.Dialect.SQLite);
}
else
{
connection = new SqlConnection(ConnectionString);
SimpleCRUD.SetDialect(SimpleCRUD.Dialect.SQLServer);
}
connection.Open();
return(connection);
}
public Startup(IConfiguration configuration)
{
IdentityModelEventSource.ShowPII = true;
Configuration = configuration;
SimpleCRUD.SetDialect(SimpleCRUD.Dialect.PostgreSQL);
}
/// <summary>
/// 根据Provider类型，创建数据库连接
/// </summary>
/// <returns></returns>
private static IDbConnection CreateConnectionByProvider(string connectionStr)
{
DefaultTypeMap.MatchNamesWithUnderscores = true;
var conf = new ConfigurationBuilder().AddJsonFile("appsettings.json").Build();
string providerName = conf["ConnectionString:" + connectionStr + ":ProviderName"];
IDbConnection conn = null;
switch (providerName)
{
case "SqlServer":
conn = new SqlConnection(conf["ConnectionString:" + connectionStr + ":Value"]);
break;
case "System.Data.MySqlClient":
case "MySql.Data.MySqlClient":
//MySql
//conn = new MySqlConnection(connStringSetting.ConnectionString);
SimpleCRUD.SetDialect(SimpleCRUD.Dialect.MySQL);
break;
//case "System.Data.OracleClient":
//case "Oracle.DataAccess.Client":
// //Oracle
// conn = new OracleConnection(connStringSetting.ConnectionString);
// break;
case "System.Data.Odbc":
//ODBC Excel Driver
break;
}
return(conn);
}
/// <summary>
/// 获取数据库连接
/// </summary>
/// <param name="dbType">数据库类型</param>
/// <param name="conStr">数据库连接字符串</param>
/// <returns>数据库连接</returns>
public static IDbConnection CreateConnection(DatabaseType dbType, string strConn)
{
IDbConnection connection = null;
if (strConn.IsNullOrWhiteSpace())
{
throw new ArgumentNullException("获取数据库连接居然不传数据库类型，你想上天吗？");
}
switch (dbType)
{
case DatabaseType.SqlServer:
connection = new SqlConnection(strConn);
break;
case DatabaseType.MySQL:
connection = new MySqlConnection(strConn);
SimpleCRUD.SetDialect(SimpleCRUD.Dialect.MySQL);
break;
case DatabaseType.PostgreSQL:
connection = new NpgsqlConnection(strConn);
SimpleCRUD.SetDialect(SimpleCRUD.Dialect.PostgreSQL);
break;
default:
throw new ArgumentNullException($"这是我的错，还不支持的{dbType.ToString()}数据库类型");
}
if (connection.State == ConnectionState.Closed)
{
connection.Open();
}
return(connection);
}
private IDbConnection GetOpenConnection()
{
IDbConnection connection;
if (_dbtype == SimpleCRUD.Dialect.PostgreSQL)
{
connection = new NpgsqlConnection(String.Format("Server={0};Port={1};User Id={2};Password={3};Database={4};", "localhost", "5432", "postgres", "postgrespass", "testdb"));
SimpleCRUD.SetDialect(SimpleCRUD.Dialect.PostgreSQL);
}
else if (_dbtype == SimpleCRUD.Dialect.SQLite)
{
connection = new SQLiteConnection("Data Source=MyDatabase.sqlite;Version=3;");
SimpleCRUD.SetDialect(SimpleCRUD.Dialect.SQLite);
}
else if (_dbtype == SimpleCRUD.Dialect.MySQL)
{
connection = new MySqlConnection(String.Format("Server={0};Port={1};User Id={2};Password={3};Database={4};", "localhost", "3306", "admin", "admin", "testdb"));
SimpleCRUD.SetDialect(SimpleCRUD.Dialect.MySQL);
}
else
{
connection = new SqlConnection(@"Data Source = (LocalDB)\v11.0;Initial Catalog=DapperSimpleCrudTestDb;Integrated Security=True;MultipleActiveResultSets=true;");
SimpleCRUD.SetDialect(SimpleCRUD.Dialect.SQLServer);
}
connection.Open();
return(connection);
}
static RepositoryFactory()
{
var mappings = TypeColumnMappings.Create();
foreach (var mapping in mappings)
{
Dapper.SqlMapper.SetTypeMap(
mapping.Type,
new CustomPropertyTypeMap(
mapping.Type,
(type, columnName) =>
{
if (mapping.FieldNameByColumnName.ContainsKey(columnName))
{
string fieldName = mapping.FieldNameByColumnName[columnName];
var properties = type.GetProperties();
return(properties.FirstOrDefault(item => item.Name == fieldName));
}
return(null);
}));
}
var resolver = new CustomSimpleCrudResolver(mappings);
SimpleCRUD.SetTableNameResolver(resolver);
SimpleCRUD.SetColumnNameResolver(resolver);
SimpleCRUD.SetDialect(SimpleCRUD.Dialect.MySQL);
}
public void SetDialect(Dialect dialect)
{
SimpleCRUD.Dialect DapperDialect;
switch (dialect)
{
case Dialect.MySQL:
DapperDialect = SimpleCRUD.Dialect.MySQL;
break;
case Dialect.PostgreSQL:
DapperDialect = SimpleCRUD.Dialect.PostgreSQL;
break;
case Dialect.SQLServer:
DapperDialect = SimpleCRUD.Dialect.SQLServer;
break;
case Dialect.SQLite:
DapperDialect = SimpleCRUD.Dialect.SQLite;
break;
default:
DapperDialect = SimpleCRUD.Dialect.SQLServer;
break;
}
SimpleCRUD.SetDialect(DapperDialect);
}
/// <summary>
/// 获取数据库连接
/// </summary>
/// <param name="dbType">数据库类型</param>
/// <param name="connectionStr">数据库连接字符串</param>
/// <returns></returns>
public static IDbConnection CreateConnection(DatabaseType dbType, string connectionStr)
{
IDbConnection dbConnection = null;
if (connectionStr.IsNullOrWhiteSpace())
{
throw new ArgumentNullException("未指定连接字符串");
}
switch (dbType)
{
case DatabaseType.MySQL:
//使用的Dapper.SimpleCRUD, 默认数据库格式是sqlserver
SimpleCRUD.SetDialect(SimpleCRUD.Dialect.MySQL);
dbConnection = new MySqlConnection(connectionStr);
break;
default:
throw new ArgumentException($"不支持的数据库类型:{dbType.ToString()}");
}
if (dbConnection.State == ConnectionState.Closed)
{
dbConnection.Open();
}
return(dbConnection);
}
/// <summary>
/// 查询配置文件信息
/// </summary>
/// <returns></returns>
public List <DataBaseLinkConfigModel> GetConfigModels()
{
SimpleCRUD.SetDialect(SimpleCRUD.Dialect.SQLite);
var conn = SqlConnectionHelper.GetOpenConnection(ConnectionString);
var result = conn.GetList <DataBaseLinkConfigModel>();
return(result.ToList());
}
protected DbConnection ObterConexao(string strConexao)
{
string strConnectionString = ConfigurationManager.ConnectionStrings[strConexao].ConnectionString;
SimpleCRUD.SetDialect(SimpleCRUD.Dialect.PostgreSQL);
return(new NpgsqlConnection(strConnectionString));
//return new SqlConnection(strConnectionString);
}
/// <summary>
/// 更新配置文件
/// </summary>
/// <param name="newModel"></param>
/// <returns></returns>
public bool UpdateConfigModel(DataBaseLinkConfigModel newModel)
{
SimpleCRUD.SetDialect(SimpleCRUD.Dialect.SQLite);
var conn = SqlConnectionHelper.GetOpenConnection(ConnectionString);
var result = conn.Update(newModel);
return(result > 0);
}
/// <summary>
/// 查询指定条件配置信息
/// </summary>
/// <param name="linkName"></param>
/// <returns></returns>
public DataBaseLinkConfigModel GetConfigModelByLinkName(string linkName)
{
SimpleCRUD.SetDialect(SimpleCRUD.Dialect.SQLite);
var conn = SqlConnectionHelper.GetOpenConnection(ConnectionString);
var result = conn.GetList <DataBaseLinkConfigModel>($" where LinkName='{linkName}' ");
return(result.FirstOrDefault());
}
public static void AddUnitOfWork(this IServiceCollection services, Func <DbConnection> connFactoryFunc, SimpleCRUD.Dialect dialect)
{
SimpleCRUD.SetDialect(dialect);
services.AddScoped <Func <DbConnection> >(resolver => () => new TracingDbConnection(connFactoryFunc(), resolver.GetService <ILoggerFactory>()));
services.AddTransient <IConnectionFactory, ConnectionFactory>();
services.AddScoped <IUnitOfWork, UnitOfWork>();
services.AddTransient(typeof(IRepository <>), typeof(Repository <>));
services.AddTransient(typeof(IRepository <,>), typeof(Repository <,>));
}
public async Task <Product> GetAsync(int productId)
{
using (var connection = new MySqlConnection(_appSettings.Value.DBConnectionString))
{
SimpleCRUD.SetDialect(SimpleCRUD.Dialect.MySQL);
return(await connection.GetAsync <Product>(productId));
}
}
public async Task <int?> CreateAsync(string name, decimal price)
{
using (var connection = new MySqlConnection(_appSettings.Value.DBConnectionString))
{
SimpleCRUD.SetDialect(SimpleCRUD.Dialect.MySQL);
return(await connection.InsertAsync(new Product { Name = name, Price = price }));
}
}
public async Task <IEnumerable <Product> > ListAsync()
{
using (var connection = new MySqlConnection(_appSettings.Value.DBConnectionString))
{
SimpleCRUD.SetDialect(SimpleCRUD.Dialect.MySQL);
return(await connection.GetListAsync <Product>());
}
}