/// <summary>
/// 获取标准字段SQL字符串
/// </summary>
/// <param name="tp"></param>
/// <param name="multiTable"></param>
/// <returns></returns>
public string ToSqlString(DatabaseTypes tp, bool multiTable = false)
{
string name = "";
switch (tp)
{
case DatabaseTypes.Microsoft_Office_Access:
case DatabaseTypes.Microsoft_Office_Access_v12:
case DatabaseTypes.Microsoft_SQL_Server:
case DatabaseTypes.SQLite:
case DatabaseTypes.SQLite_3:
name = $"[{_name}]";
break;
case DatabaseTypes.MySQL:
name = $"`{_name}`";
break;
case DatabaseTypes.PostgreSQL:
name = $"\"{_name}\"";
break;
default:
throw new Exception($"尚未支持 {tp.ToString()} 类型数据库");
}
return($"{_object.ToSqlString(tp, multiTable)} AS {name}");
}
/// <summary>
/// 获取标准SQL字符串
/// </summary>
/// <param name="tp"></param>
/// <param name="multiTable"></param>
/// <returns></returns>
public string ToSqlString(DatabaseTypes tp, bool multiTable = false)
{
string res = "";
for (int i = 0; i < _value.Length; i++)
{
if (res != "")
{
res += ",";
}
//res += $"'{_value[i]}'";
switch (tp)
{
case DatabaseTypes.MySQL:
res += $"'{_value[i].Replace("'", "\'")}'";
break;
case DatabaseTypes.Microsoft_Office_Access:
case DatabaseTypes.Microsoft_Office_Access_v12:
case DatabaseTypes.Microsoft_SQL_Server:
case DatabaseTypes.SQLite:
case DatabaseTypes.SQLite_3:
case DatabaseTypes.PostgreSQL:
res += $"'{_value[i].Replace("'", "''")}'";
break;
default:
throw new Exception($"尚未支持数据库 {tp.ToString()} 中的字符串转义。");
}
}
return($"({res})");
}
/// <summary>
/// 获取对象标准的SQL表示形式
/// </summary>
/// <param name="tp"></param>
/// <param name="multiTable"></param>
/// <returns></returns>
public string ToSqlString(DatabaseTypes tp, bool multiTable = false)
{
string res = "";
string stp = GetTypeString(tp);
switch (tp)
{
case DatabaseTypes.Microsoft_Office_Access:
case DatabaseTypes.Microsoft_Office_Access_v12:
case DatabaseTypes.Microsoft_SQL_Server:
case DatabaseTypes.SQLite:
case DatabaseTypes.SQLite_3:
res = $"[{this.Name}] {stp}";
break;
case DatabaseTypes.MySQL:
res = $"`{this.Name}` {stp}";
break;
case DatabaseTypes.PostgreSQL:
res = $"\"{this.Name}\" {stp}";
break;
default: throw new Exception($"尚未支持\"{tp.ToString()}\"数据库字段定义");
}
return(res);
}
/// <summary>
/// Put parameter if not exist and property AddMissingParameter is true
/// </summary>
/// <param name="param">Param name</param>
/// <param name="value">Param value</param>
/// <returns></returns>
private bool PutParameter(string param, string value)
{
if (!_addMissingParameter)
{
return(false);
}
if (!param.Equals(_parameterNames.UserName, StringComparison.OrdinalIgnoreCase) &&
!param.Equals(_parameterNames.UserPassword, StringComparison.OrdinalIgnoreCase) &&
!param.Equals(_parameterNames.Provider, StringComparison.OrdinalIgnoreCase) &&
!param.Equals(_parameterNames.Server, StringComparison.OrdinalIgnoreCase) &&
!param.Equals(_parameterNames.Database, StringComparison.OrdinalIgnoreCase)
)
{
throw new ArgumentException("Param name '"
+ param + "' can not exist in connection type '"
+ _databaseType.ToString() + "'!");
}
if (!_connectionString.EndsWith(Constants.ParameterDivider, StringComparison.Ordinal))
{
_connectionString += Constants.ParameterDivider;
}
string prm = param + Constants.ParameterValueDivider + value;
_connectionString += prm;
return(true);
}
public IDbConnection GetConnection(DatabaseTypes type)
{
//If you have a polyglot of database sources then you can return the right connection according to the database you want to use
//and what connection is stored in the application configuration
string stringConnection = _connection.GetSection("Data").GetSection(type.ToString()).Value;
//Need to provide a factory abstraction here to get the relevent datasource connection
return(new SqlConnection(stringConnection));
}
public static Database Generate(DatabaseTypes type, string connectionString)
{
switch (type)
{
case DatabaseTypes.MySql:
return(new Database(new MySql.Data.MySqlClient.MySqlConnection(connectionString)));
case DatabaseTypes.SqlServer:
return(new Database(new System.Data.SqlClient.SqlConnection(connectionString)));
default:
throw new NotSupportedException(type.ToString());
}
}
/// <summary>
/// 执行SQL语句，返回影响的记录数
/// </summary>
/// <param name="database">数据库类型：Oracle或Sql Server</param>
/// <param name="connStr">连接字符串</param>
/// <param name="sqlString">执行的查询语句</param>
/// <returns>返回影响的记录行数</returns>
public int ExecuteSql(DatabaseTypes database, string connStr, string sqlString)
{
if (string.IsNullOrWhiteSpace(sqlString))
{
return(0);
}
dynamic connection, command;
sqlString = sqlString.Trim(' ', ';'); //去除SQL语句前后的空格与分号
if (database == DatabaseTypes.Oracle)
{
connection = new OracleConnection(connStr);
command = new OracleCommand(sqlString, connection);
}
else if (database == DatabaseTypes.SqlServer)
{
connection = new SqlConnection(connStr);
command = new SqlCommand(sqlString, connection);
}
else if (database == DatabaseTypes.MySql)
{
connection = new MySqlConnection(connStr);
command = new MySqlCommand(sqlString, connection);
}
else
{
return(0);
}
int result = 0;
try {
connection.Open();
result = command.ExecuteNonQuery();
} catch (Exception e) {
result = 0;
FileClient.WriteExceptionInfo(e, string.Format("{0} SQL语句执行出错", database.ToString()), string.Format("SQL语句：{0}", sqlString));
throw; //假如不需要抛出异常，注释此行
}
finally
{
//不要忘记释放资源
connection.Close();
connection.Dispose();
command.Dispose();
}
return(result);
}
/// <summary>
/// 执行存储过程，返回影响的行数
/// </summary>
/// <param name="database">数据库类型</param>
/// <param name="connStr">数据库连接字符串</param>
/// <param name="procedureName">存储过程名</param>
/// <param name="parameters">输入参数</param>
/// <returns>返回影响的行数（？）</returns>
public int RunProcedure(DatabaseTypes database, string connStr, string procedureName, IDataParameter[] parameters)
{
//if (database != DatabaseTypes.Oracle && database != DatabaseTypes.SqlServer)
// return 0;
dynamic connection, command;
if (database == DatabaseTypes.Oracle)
{
connection = new OracleConnection(connStr);
command = this.BuildOracleCommand(connection, procedureName, parameters);
}
else if (database == DatabaseTypes.SqlServer)
{
connection = new SqlConnection(connStr);
command = this.BuildSqlServerCommand(connection, procedureName, parameters);
}
else if (database == DatabaseTypes.MySql)
{
connection = new MySqlConnection(connStr);
command = this.BuildMySqlCommand(connection, procedureName, parameters);
}
else
{
return(0);
}
int result = 0;
try
{
connection.Open();
result = command.ExecuteNonQuery();
}
catch (Exception e)
{
result = 0;
FileClient.WriteExceptionInfo(e, string.Format("{0} 存储过程执行失败", database.ToString()));
throw; //假如不需要抛出异常，将此行注释
}
finally
{
//不要忘记释放资源
connection.Close();
connection.Dispose();
command.Dispose();
}
return(result);
}
/// <summary>
/// 获取标准SQL字符串
/// </summary>
/// <param name="tp"></param>
/// <param name="multiTable"></param>
/// <returns></returns>
public string ToSqlString(DatabaseTypes tp, bool multiTable = false)
{
string res = "INSERT INTO ";
res += _table.ToSqlString(tp);
string cols = "";
string vals = "";
foreach (var key in _row.Keys)
{
if (cols != "")
{
cols += ",";
}
if (vals != "")
{
vals += ",";
}
using (SqlUnits.TableField field = new SqlUnits.TableField(_table, key)) {
cols += field.ToSqlString(tp);
}
switch (tp)
{
case DatabaseTypes.MySQL:
vals += $"'{_row[key].Replace("'", "\'")}'";
break;
//return $"'{_value.Replace("'", "\'")}'";
case DatabaseTypes.Microsoft_Office_Access:
case DatabaseTypes.Microsoft_Office_Access_v12:
case DatabaseTypes.Microsoft_SQL_Server:
case DatabaseTypes.SQLite:
case DatabaseTypes.SQLite_3:
case DatabaseTypes.PostgreSQL:
vals += $"'{_row[key].Replace("'", "''")}'";
break;
//return $"'{_value.Replace("'", "''")}'";
default:
throw new Exception($"尚未支持数据库 {tp.ToString()} 中的字符串转义。");
}
}
res += $"({cols}) VALUES ({vals})";
return(res);
}
public static IdpeKeyTypes GetSreType(this DatabaseTypes databaseType)
{
switch (databaseType)
{
case DatabaseTypes.SqlServer:
return(IdpeKeyTypes.ConnectionStringSqlServer);
case DatabaseTypes.Oracle:
return(IdpeKeyTypes.ConnectionStringOracle);
case DatabaseTypes.DB2iSeries:
return(IdpeKeyTypes.ConnectionStringDB2iSeries);
case DatabaseTypes.SqlCe:
return(IdpeKeyTypes.ConnectionStringSqlCe);
default:
throw new Exception(databaseType.ToString() + " is not sretype key!");
}
}
public SqlBuilderBase CreateSqlBuilder()
{
if (_providerTypes == null)
{
_providerTypes = Assembly.GetExecutingAssembly().GetTypes().Where(x => x.BaseType == typeof(SqlBuilderBase)).ToList();
}
var typeName = _databaseType.ToString() + ".SqlBuilder";
var types = _providerTypes.Where(x => x.FullName.EndsWith(typeName));
if (types.Count() > 1)
{
throw new NotSupportedException("找到了多个包含" + typeName + "的提供者类");
}
var type = types.FirstOrDefault();
if (type == null)
{
throw new NotSupportedException("未找到提供者类：" + typeName);
}
return((SqlBuilderBase)ObjectCache <SqlBuilderBase> .GetObjectFromCallContext(type));
}
public static ProviderBase CreateProvider(DatabaseTypes databaseType)
{
if (_type == null)
{
if (_providerTypes == null)
{
_providerTypes = Assembly.GetExecutingAssembly().GetTypes().Where(x => x.BaseType == typeof(ProviderBase)).ToDictionary(x => x.Name);
}
var typeName = string.Format("Provider.{0}.{0}Provider", databaseType.ToString());
var types = _providerTypes.Values.Where(x => x.FullName.EndsWith(typeName));
if (types.Count() > 1)
{
throw new NotSupportedException("找到了多个包含" + typeName + "的提供者类");
}
_type = types.FirstOrDefault();
if (_type == null)
{
throw new NotSupportedException("未找到提供者类：" + typeName);
}
}
return (ProviderBase)ObjectCache<ProviderBase>.GetObjectFromCallContext(_type);
}
public static ProviderBase CreateProvider(DatabaseTypes databaseType)
{
if (_type == null)
{
if (_providerTypes == null)
{
_providerTypes = Assembly.GetExecutingAssembly().GetTypes().Where(x => x.BaseType == typeof(ProviderBase)).ToDictionary(x => x.Name);
}
var typeName = string.Format("Provider.{0}.{0}Provider", databaseType.ToString());
var types = _providerTypes.Values.Where(x => x.FullName.EndsWith(typeName));
if (types.Count() > 1)
{
throw new NotSupportedException("找到了多个包含" + typeName + "的提供者类");
}
_type = types.FirstOrDefault();
if (_type == null)
{
throw new NotSupportedException("未找到提供者类：" + typeName);
}
}
return((ProviderBase)ObjectCache <ProviderBase> .GetObjectFromCallContext(_type));
}
/// <summary>
/// 获取标准SQL字符串
/// </summary>
/// <param name="tp"></param>
/// <param name="multiTable"></param>
/// <returns></returns>
public string ToSqlString(DatabaseTypes tp, bool multiTable = false)
{
switch (tp)
{
case DatabaseTypes.Microsoft_Office_Access:
case DatabaseTypes.Microsoft_Office_Access_v12:
return("RND(ID)");
case DatabaseTypes.Microsoft_SQL_Server:
return("NEWID()");
case DatabaseTypes.SQLite:
case DatabaseTypes.SQLite_3:
case DatabaseTypes.PostgreSQL:
return("RANDOM()");
case DatabaseTypes.MySQL:
return("RAND()");
default:
throw new Exception($"尚未支持 {tp.ToString()} 类型数据库");
}
}
/// <summary>
/// 获取标准SQL字符串
/// </summary>
/// <param name="tp"></param>
/// <param name="multiTable"></param>
/// <returns></returns>
public string ToSqlString(DatabaseTypes tp, bool multiTable = false)
{
if (_value == null)
{
return("NULL");
}
switch (tp)
{
case DatabaseTypes.MySQL:
return($"'{_value.Replace("'", "\'")}'");
case DatabaseTypes.Microsoft_Office_Access:
case DatabaseTypes.Microsoft_Office_Access_v12:
case DatabaseTypes.Microsoft_SQL_Server:
case DatabaseTypes.SQLite:
case DatabaseTypes.SQLite_3:
case DatabaseTypes.PostgreSQL:
return($"'{_value.Replace("'", "''")}'");
default:
throw new Exception($"尚未支持数据库 {tp.ToString()} 中的字符串转义。");
}
}
internal string GetConnectionStringSqlClient(DatabaseTypes databaseType)
{
string portString;
string serviceNameString;
switch (databaseType)
{
//case DatabaseTypes.SQLServer2000:
case DatabaseTypes.SQLServer2005:
portString = Port == GetDefaultPort(databaseType) ? "" : string.Format(",{0}", Port);
if (UseIntegratedSecurity)
{
return(string.Format("Server={0}{2};Database={1};Trusted_Connection=True", ServerName, DatabaseName, portString));
}
return(string.Format("Server={0}{4};Database={1};User ID={2};Password={3};Trusted_Connection=False", ServerName, DatabaseName, UserName, Password, portString));
case DatabaseTypes.SQLServerExpress:
EnsureSqlExpressServiceIsRunning();
if (UseFileName)
{
if (!SqlExpressDbIsAlreadyAttached)
{
return(string.Format(@"Server=.\SQLExpress;AttachDbFilename='{2}';Database='{1}';Trusted_Connection=Yes;User Instance=True", ServerName, DatabaseName.Replace("_Data", ""), FileName));
}
// Don't include the filename
return(string.Format(@"Server=.\SQLExpress;Database='{1}';Trusted_Connection=Yes;User Instance=True", ServerName, DatabaseName.Replace("_Data", ""), FileName));
}
if (UseIntegratedSecurity)
{
return(string.Format(@"Data Source=.\SQLEXPRESS;Initial Catalog={1};Integrated Security=True;Pooling=False", ServerName, DatabaseName.Replace("_Data", ""), FileName));
}
return(string.Format(@"Server=.\SQLExpress;Database='{1}';User ID={3};Password={4};Trusted_Connection=False;", ServerName, DatabaseName.Replace("_Data", ""), FileName, UserName, Password));
case DatabaseTypes.MySQL:
return(string.Format("Provider=MySQL;direct=true;host={0};port={1};user={2};password={3};database={4}", ServerName, Port, UserName, Password, DatabaseName));
case DatabaseTypes.Oracle:
if (Direct)
{
serviceNameString = ServiceName == GetDefaultServiceName(databaseType) ? "" : string.Format("sid={0};", ServiceName);
return(string.Format("Provider=Oracle;Direct=true;data source={0};port={1};{2}user={3};password={4};", ServerName, Port, serviceNameString, UserName, Password));
}
else
{
return(string.Format("Provider=Oracle;data source={0};user={1};password={2};", ServerName, UserName, Password));
}
case DatabaseTypes.PostgreSQL:
//return string.Format("Provider=PostgreSQL;host={0};port={1};user={2};password={3};initial schema={4};database={5};", ServerName, Port, UserName, Password, SchemaName, DatabaseName);
return(string.Format("Provider=PostgreSQL;host={0};port={1};user={2};password={3};database={4};", ServerName, Port, UserName, Password, DatabaseName));
case DatabaseTypes.Firebird:
return(string.Format("Server={0};Port={1};User={2};Password={3};Database={4};", ServerName, Port, UserName, Password, FileName));
case DatabaseTypes.SQLite:
return(string.Format("'Data Source=\"{0}\";Version=3;New=True'", FileName));
default:
throw new NotImplementedException("Not coded yet: " + databaseType.ToString());
}
}
/// <summary>
/// 获取对应数据库标准的类型表示方式
/// </summary>
/// <param name="tp"></param>
/// <returns></returns>
public string GetTypeString(DatabaseTypes tp)
{
string res = "";
string stp = this.Type.ToLower();
switch (tp)
{
case DatabaseTypes.Microsoft_Office_Access:
case DatabaseTypes.Microsoft_Office_Access_v12:
case DatabaseTypes.Microsoft_SQL_Server:
switch (stp)
{
case "string":
case "varchar":
case "nvarchar":
//字符串类型
res = $"VARCHAR({this.Size})";
break;
case "double":
case "float":
case "decimal":
case "numeric":
//带经度的数字
res = $"numeric({this.Size},{this.Float})";
break;
case "long":
case "bigint":
//长整型
res = $"bigint";
break;
case "integer":
case "int":
//整型
res = $"int";
break;
case "text":
//可扩展字符串
res = $"text";
break;
default:
throw new Exception($"未知数据类型:{this.Type}");
}
break;
case DatabaseTypes.MySQL:
switch (stp)
{
case "string":
case "varchar":
case "nvarchar":
//字符串类型
res = $"VARCHAR({this.Size})";
break;
case "double":
case "float":
case "decimal":
case "numeric":
//带经度的数字
res = $"DECIMAL({this.Size},{this.Float})";
break;
case "long":
case "bigint":
//长整型
res = $"bigint";
break;
case "integer":
case "int":
//整型
res = $"int";
break;
case "text":
//可扩展字符串
res = $"text";
break;
default:
throw new Exception($"未知数据类型:{this.Type}");
}
break;
case DatabaseTypes.SQLite:
case DatabaseTypes.SQLite_3:
switch (stp)
{
case "string":
case "varchar":
case "nvarchar":
case "text":
//字符串类型
res = $"TEXT";
break;
case "double":
case "float":
case "decimal":
case "numeric":
//带经度的数字
res = $"REAL";
break;
case "long":
case "bigint":
case "integer":
case "int":
//整型
res = $"INTEGER";
break;
default:
throw new Exception($"未知数据类型:{this.Type}");
}
break;
case DatabaseTypes.PostgreSQL:
switch (stp)
{
case "string":
case "varchar":
case "nvarchar":
//字符串类型
res = $"varchar({this.Size})";
break;
case "double":
case "float":
case "decimal":
case "numeric":
//带经度的数字
res = $"numeric({this.Size},{this.Float})";
break;
case "long":
case "bigint":
//长整型
res = $"bigint";
break;
case "integer":
case "int":
//整型
res = $"integer";
break;
case "text":
//可扩展字符串
res = $"text";
break;
default:
throw new Exception($"未知数据类型:{this.Type}");
}
break;
default: throw new Exception($"尚未支持\"{tp.ToString()}\"数据库字段定义");
}
return(res);
}
public static int GetDefaultPort(DatabaseTypes databaseType)
{
switch (databaseType)
{
case DatabaseTypes.MySQL:
return 3306;
case DatabaseTypes.Oracle:
return 1521;
//case DatabaseTypes.SQLServer2000:
case DatabaseTypes.SQLServer2005:
case DatabaseTypes.SQLServerExpress:
return 1433;
case DatabaseTypes.PostgreSQL:
return 5432;
case DatabaseTypes.Firebird:
return 3050;
case DatabaseTypes.SQLCE:
return 0;
case DatabaseTypes.SQLite:
return 0;
default:
throw new NotImplementedException("Database type not handled yet: " + databaseType.ToString());
}
}
void form_SelectedDatabaseTypeChanged(object sender, EventArgs e)
{
DatabaseTypes dbType = form.SelectedDatabaseType;
switch (dbType)
{
//case DatabaseTypes.SQLServer2000:
case DatabaseTypes.SQLServer2005:
case DatabaseTypes.MySQL:
case DatabaseTypes.Oracle:
case DatabaseTypes.PostgreSQL:
form.SelectDatabase();
break;
case DatabaseTypes.SQLCE:
case DatabaseTypes.SQLServerExpress:
case DatabaseTypes.SQLite:
form.SelectDatabaseFile();
break;
default:
throw new NotImplementedException("Database type not handled yet: " + dbType.ToString());
}
}
internal string GetConnectionStringSqlClient(DatabaseTypes databaseType)
{
string portString;
string serviceNameString;
switch (databaseType)
{
//case DatabaseTypes.SQLServer2000:
case DatabaseTypes.SQLServer2005:
portString = Port == GetDefaultPort(databaseType) ? "" : string.Format(",{0}", Port);
if (UseIntegratedSecurity) { return string.Format("Server={0}{2};Database={1};Trusted_Connection=True", ServerName, DatabaseName, portString); }
return string.Format("Server={0}{4};Database={1};User ID={2};Password={3};Trusted_Connection=False", ServerName, DatabaseName, UserName, Password, portString);
case DatabaseTypes.SQLServerExpress:
EnsureSqlExpressServiceIsRunning();
if (UseFileName)
{
if (!SqlExpressDbIsAlreadyAttached)
{
return string.Format(@"Server=.\SQLExpress;AttachDbFilename='{2}';Database='{1}';Trusted_Connection=Yes;User Instance=True", ServerName, DatabaseName.Replace("_Data", ""), FileName);
}
// Don't include the filename
return string.Format(@"Server=.\SQLExpress;Database='{1}';Trusted_Connection=Yes;User Instance=True", ServerName, DatabaseName.Replace("_Data", ""), FileName);
}
if (UseIntegratedSecurity)
{
return string.Format(@"Data Source=.\SQLEXPRESS;Initial Catalog={1};Integrated Security=True;Pooling=False", ServerName, DatabaseName.Replace("_Data", ""), FileName);
}
return string.Format(@"Server=.\SQLExpress;Database='{1}';User ID={3};Password={4};Trusted_Connection=False;", ServerName, DatabaseName.Replace("_Data", ""), FileName, UserName, Password);
case DatabaseTypes.MySQL:
return string.Format("Provider=MySQL;direct=true;host={0};port={1};user={2};password={3};database={4}", ServerName, Port, UserName, Password, DatabaseName);
case DatabaseTypes.Oracle:
if (Direct)
{
serviceNameString = ServiceName == GetDefaultServiceName(databaseType) ? "" : string.Format("sid={0};", ServiceName);
return string.Format("Provider=Oracle;Direct=true;data source={0};port={1};{2}user={3};password={4};", ServerName, Port, serviceNameString, UserName, Password);
}
else
return string.Format("Provider=Oracle;data source={0};user={1};password={2};", ServerName, UserName, Password);
case DatabaseTypes.PostgreSQL:
//return string.Format("Provider=PostgreSQL;host={0};port={1};user={2};password={3};initial schema={4};database={5};", ServerName, Port, UserName, Password, SchemaName, DatabaseName);
return string.Format("Provider=PostgreSQL;host={0};port={1};user={2};password={3};database={4};", ServerName, Port, UserName, Password, DatabaseName);
case DatabaseTypes.Firebird:
return string.Format("Server={0};Port={1};User={2};Password={3};Database={4};", ServerName, Port, UserName, Password, FileName);
case DatabaseTypes.SQLite:
return string.Format("'Data Source=\"{0}\";Version=3;New=True'", FileName);
default:
throw new NotImplementedException("Not coded yet: " + databaseType.ToString());
}
}
public IEnumerable <string> GetServerNames(DatabaseTypes databaseType)
{
if (databaseType == DatabaseTypes.SQLServer2005)
{
return(SqlServer2005Helper.GetSqlServer2005Instances());
}
if (databaseType == DatabaseTypes.MySQL)
{
return(MySQLHelper.GetMySQLInstances());
}
if (databaseType == DatabaseTypes.Oracle)
{
return(OracleHelper.GetOracleInstances());
}
if (databaseType == DatabaseTypes.PostgreSQL)
{
return(PostgreSQLHelper.GetPostgreSQLInstances());
}
if (databaseType == DatabaseTypes.SQLServerExpress)
{
return(SqlServerExpressHelper.GetSqlServerExpressInstances());
}
if (databaseType == DatabaseTypes.SQLite)
{
return(SQLiteHelper.GetSQLiteInstances());
}
throw new NotImplementedException("This database type not handled yet in GetServerNames(): " + databaseType.ToString());
//return new List<string>();
}
public IEnumerable<string> GetServerNames(DatabaseTypes databaseType)
{
if (databaseType == DatabaseTypes.SQLServer2005)
return SqlServer2005Helper.GetSqlServer2005Instances();
if (databaseType == DatabaseTypes.MySQL)
return MySQLHelper.GetMySQLInstances();
if (databaseType == DatabaseTypes.Oracle)
return OracleHelper.GetOracleInstances();
if (databaseType == DatabaseTypes.PostgreSQL)
return PostgreSQLHelper.GetPostgreSQLInstances();
if (databaseType == DatabaseTypes.SQLServerExpress)
return SqlServerExpressHelper.GetSqlServerExpressInstances();
if (databaseType == DatabaseTypes.SQLite)
return SQLiteHelper.GetSQLiteInstances();
throw new NotImplementedException("This database type not handled yet in GetServerNames(): " + databaseType.ToString());
//return new List<string>();
}
/// <summary>
/// 执行多条SQL语句，实现数据事务
/// </summary>
/// <param name="database">数据库类型：Oracle或Sql Server</param>
/// <param name="connStr">连接字符串</param>
/// <param name="sqlStrings">存储SQL语句的字符串数组</param>
/// <param name="level">事务隔离（锁定）级别</param>
/// <returns>假如所有语句执行成功，返回true，否则返回false</returns>
public bool ExecuteSqlTrans(DatabaseTypes database, string connStr, IEnumerable <string> sqlStrings, IsolationLevel level)
{
//假如数组为空，返回false
if (sqlStrings == null || sqlStrings.Count() == 0)
{
return(false);
}
//假如数据库类型不正确，返回false
dynamic connection, command, transaction;
if (database == DatabaseTypes.Oracle)
{
connection = new OracleConnection(connStr);
command = new OracleCommand(string.Empty, connection);
}
else if (database == DatabaseTypes.SqlServer)
{
connection = new SqlConnection(connStr);
command = new SqlCommand(string.Empty, connection);
}
else if (database == DatabaseTypes.MySql)
{
connection = new MySqlConnection(connStr);
command = new MySqlCommand(string.Empty, connection);
}
else
{
return(false);
}
bool result = false;
try { connection.Open(); }
catch (Exception e)
{
result = false;
FileClient.WriteExceptionInfo(e, string.Format("{0} 数据库连接打开失败", database.ToString()));
connection.Close();
connection.Dispose();
command.Dispose();
throw;
}
string sql = string.Empty;
transaction = connection.BeginTransaction(level); //connection必须先Open
try
{
command.Transaction = transaction;
foreach (string sqlString in sqlStrings)
{
//假如为空白字符串（移除空格与分号后），跳到下一次循环
//需去除字符串前后的空格和分号，否则Oracle中报错（ORA-00911: 无效字符）
sql = sqlString == null ? null : sqlString.Trim(' ', ';');
if (string.IsNullOrWhiteSpace(sql))
{
continue;
}
command.CommandText = sql;
command.ExecuteNonQuery();
}
transaction.Commit();
result = true;
}
catch (Exception e)
{
result = false;
transaction.Rollback();
FileClient.WriteExceptionInfo(e, string.Format("{0} SQL语句事务执行失败", database.ToString()), string.Format("执行失败的SQL语句：{0}", sql));
throw; //假如不需要抛出异常，注释此行
}
finally
{
//不要忘记释放资源
connection.Close();
connection.Dispose();
command.Dispose();
transaction.Dispose();
}
return(result);
}
/// <summary>
/// 执行一条或多条SQL语句，返回查询结果集
/// </summary>
/// <param name="database">数据库类型，Oracle或SqlServer</param>
/// <param name="connStr">数据库连接字符串</param>
/// <param name="sqlStrings">待执行的查询语句数组</param>
/// <returns></returns>
public DataSet MultiQuery(DatabaseTypes database, string connStr, IEnumerable <string> sqlStrings)
{
//假如字符数组为空，返回空
if (sqlStrings == null || sqlStrings.Count() == 0)
{
return(null);
}
//动态类，根据数据库类型初始化；假如数据库类型不正确，返回空
dynamic connection, adapter;
if (database == DatabaseTypes.Oracle)
{
connection = new OracleConnection(connStr);
adapter = new OracleDataAdapter(string.Empty, connection);
}
else if (database == DatabaseTypes.SqlServer)
{
connection = new SqlConnection(connStr);
adapter = new SqlDataAdapter(string.Empty, connection);
}
else if (database == DatabaseTypes.MySql)
{
connection = new MySqlConnection(connStr);
adapter = new MySqlDataAdapter(string.Empty, connection);
}
else
{
return(null);
}
DataSet dataSet = new DataSet();
string _string = string.Empty;
try
{
//dataSet.EnforceConstraints = false; //禁用约束检查
connection.Open();
int i = 1;
foreach (var sqlString in sqlStrings)
{
_string = sqlString; //记录每个循环中的sql语句
//跳过空白字符串
if (string.IsNullOrWhiteSpace(sqlString))
{
continue;
}
adapter.SelectCommand.CommandText = sqlString.Trim(' ', ';'); //去除字符串前后的空格与分号，否则Oracle中报错（ORA-00911: 无效字符）
adapter.Fill(dataSet, string.Format("Table{0}", i++)); //执行SQL语句并填充以表名指定的DataTable，假如不存在将创建（为避免DataTable被覆盖，表名不要重复）
}
}
//假如出现异常，写入日志，重新抛出异常
catch (Exception e)
{
dataSet = null;
FileClient.WriteExceptionInfo(e, string.Format("{0} SQL语句执行出错", database.ToString()), string.Format("SQL语句：{0}", _string));
throw; //假如不需要抛出异常，注释此行
}
finally
{
//不要忘记释放资源
connection.Close();
connection.Dispose();
adapter.Dispose();
}
return(dataSet);
}
private ConnectionStringHelper LoadGenericDatabaseData(XmlNode node, DatabaseTypes databaseType)
{
NodeProcessor proc = new NodeProcessor(node);
var connectionHelper = new ConnectionStringHelper();
if (string.IsNullOrEmpty(connectionHelper.ServerName))
{
switch (databaseType)
{
case DatabaseTypes.SQLCE:
//case DatabaseTypes.SQLServer2000:
case DatabaseTypes.SQLServer2005:
connectionHelper.ServerName = Environment.MachineName;
break;
case DatabaseTypes.SQLServerExpress:
connectionHelper.ServerName = Environment.MachineName + "\\SQLEXPRESS";
break;
case DatabaseTypes.MySQL:
connectionHelper.ServerName = "localhost";
break;
case DatabaseTypes.Oracle:
connectionHelper.ServerName = "localhost";
break;
case DatabaseTypes.PostgreSQL:
connectionHelper.ServerName = "localhost";
break;
case DatabaseTypes.Firebird:
connectionHelper.ServerName = "localhost";
break;
case DatabaseTypes.SQLite:
connectionHelper.ServerName = Environment.MachineName;
break;
default:
throw new NotImplementedException("Database type not handled yet: " + databaseType.ToString());
}
}
connectionHelper.CurrentDbType = databaseType;
if (proc.Exists("ServerName"))
connectionHelper.ServerName = proc.GetString("ServerName");
if (proc.Exists("DatabaseName"))
{
connectionHelper.DatabaseName = proc.GetString("DatabaseName");
connectionHelper.UseFileName = false;
}
if (proc.Exists("FileName"))
{
connectionHelper.FileName = proc.GetString("FileName");
connectionHelper.UseFileName = true;
}
if (proc.Exists("UseIntegratedSecurity"))
connectionHelper.UseIntegratedSecurity = proc.GetBool("UseIntegratedSecurity");
if (proc.Exists("UserName"))
connectionHelper.UserName = proc.GetString("UserName");
if (proc.Exists("Password"))
{
string password = "";
try
{
password = proc.GetString("Password").Decrypt();
}
catch
{
// Do nothing
password = "";
}
connectionHelper.Password = password;
}
if (proc.Exists("Port"))
connectionHelper.Port = proc.GetInt("Port");
if (proc.Exists("ServiceName"))
connectionHelper.ServiceName = proc.GetString("ServiceName");
if (proc.Exists("SchemaName"))
connectionHelper.ServiceName = proc.GetString("SchemaName");
if (proc.Exists("UseDirectConnection"))
connectionHelper.UseDirectConnection = proc.GetBool("UseDirectConnection");
else
connectionHelper.UseDirectConnection = false;
return connectionHelper;
}
/// <summary>
/// Take specific data layer and create or not singleton instance
/// </summary>
/// <param name="dbType">Data base type</param>
/// <param name="singleton">If parameter equal true - get singleton instance. If false - every call to the method generates a new instance.</param>
/// <returns>Instance of DataLayer</returns>
public static IDataLayer GetInstance(DatabaseTypes dbType, bool singleton)
{
IDataLayer dal = null;
switch (dbType)
{
#if (ALL || MSSQL)
case DatabaseTypes.MSSql:
if (singleton)
{
GenericSingleton <DataLayerMSSql> gsMsSql = new GenericSingleton <DataLayerMSSql>();
dal = gsMsSql.Instance;
_isNewInstance = gsMsSql.IsNewInstance;
}
else
{
dal = new DataLayerMSSql();
}
break;
#endif
#if (ALL || POSTGREE)
case DatabaseTypes.PostGre:
if (singleton)
{
GenericSingleton <DataLayerPGSql> gsPG = new GenericSingleton <DataLayerPGSql>();
dal = gsPG.Instance;
_isNewInstance = gsPG.IsNewInstance;
}
else
{
dal = new DataLayerPGSql();
}
break;
#endif
//case DataBaseType.dbtAccess:
// break;
#if (ALL || FIREBIRD)
case DatabaseTypes.Firebird:
if (singleton)
{
GenericSingleton <DataLayerFirebird> gsFB = new GenericSingleton <DataLayerFirebird>();
dal = gsFB.Instance;
_isNewInstance = gsFB.IsNewInstance;
}
else
{
dal = new DataLayerFirebird();
}
break;
#endif
#if (ALL || MYSQL)
case DatabaseTypes.MySql:
if (singleton)
{
GenericSingleton <DataLayerMySql> gsMySQL = new GenericSingleton <DataLayerMySql>();
dal = gsMySQL.Instance;
_isNewInstance = gsMySQL.IsNewInstance;
}
else
{
dal = new DataLayerMySql();
}
break;
#endif
#if (ALL || SQLITE)
case DatabaseTypes.SQLite:
if (singleton)
{
GenericSingleton <DataLayerSQLite> gsSQLite = new GenericSingleton <DataLayerSQLite>();
dal = gsSQLite.Instance;
_isNewInstance = gsSQLite.IsNewInstance;
}
else
{
dal = new DataLayerSQLite();
}
break;
#endif
#if (ALL || ORACLE)
case DatabaseTypes.Oracle:
if (singleton)
{
GenericSingleton <DataLayerOracle> gsOracle = new GenericSingleton <DataLayerOracle>();
dal = gsOracle.Instance;
_isNewInstance = gsOracle.IsNewInstance;
}
else
{
dal = new DataLayerOracle();
}
break;
#endif
//#if (ALL || SQLSERVERCE)
// case DatabaseTypes.SqlServerCE:
// if (singleton)
// {
// GenericSingleton<DataLayerSqlServerCe> gsSqlCe = new GenericSingleton<DataLayerSqlServerCe>();
// dal = gsSqlCe.Instance;
// _isNewInstance = gsSqlCe.IsNewInstance;
// }
// else
// dal = new DataLayerSqlServerCe();
// break;
//#endif
#if (ALL || OLEDB)
case DatabaseTypes.OleDB:
if (singleton)
{
GenericSingleton <DataLayerOleDB> gsOleDb = new GenericSingleton <DataLayerOleDB>();
dal = gsOleDb.Instance;
_isNewInstance = gsOleDb.IsNewInstance;
}
else
{
dal = new DataLayerOleDB();
}
break;
#endif
//case DataBaseType.dbtNone:
// break;
default:
throw new ArgumentException("void GetInstance. Connection type '"
+ dbType.ToString() + "' not exists!");
}
return(dal);
}
public static int GetDefaultPort(DatabaseTypes databaseType)
{
switch (databaseType)
{
case DatabaseTypes.MySQL:
return(3306);
case DatabaseTypes.Oracle:
return(1521);
//case DatabaseTypes.SQLServer2000:
case DatabaseTypes.SQLServer2005:
case DatabaseTypes.SQLServerExpress:
return(1433);
case DatabaseTypes.PostgreSQL:
return(5432);
case DatabaseTypes.Firebird:
return(3050);
case DatabaseTypes.SQLCE:
return(0);
case DatabaseTypes.SQLite:
return(0);
default:
throw new NotImplementedException("Database type not handled yet: " + databaseType.ToString());
}
}
private ConnectionStringHelper LoadGenericDatabaseData(XmlNode node, DatabaseTypes databaseType)
{
NodeProcessor proc = new NodeProcessor(node);
var connectionHelper = new ConnectionStringHelper();
if (string.IsNullOrEmpty(connectionHelper.ServerName))
{
switch (databaseType)
{
case DatabaseTypes.SQLCE:
//case DatabaseTypes.SQLServer2000:
case DatabaseTypes.SQLServer2005:
connectionHelper.ServerName = Environment.MachineName;
break;
case DatabaseTypes.SQLServerExpress:
connectionHelper.ServerName = Environment.MachineName + "\\SQLEXPRESS";
break;
case DatabaseTypes.MySQL:
connectionHelper.ServerName = "localhost";
break;
case DatabaseTypes.Oracle:
connectionHelper.ServerName = "localhost";
break;
case DatabaseTypes.PostgreSQL:
connectionHelper.ServerName = "localhost";
break;
case DatabaseTypes.Firebird:
connectionHelper.ServerName = "localhost";
break;
case DatabaseTypes.SQLite:
connectionHelper.ServerName = Environment.MachineName;
break;
default:
throw new NotImplementedException("Database type not handled yet: " + databaseType.ToString());
}
}
connectionHelper.CurrentDbType = databaseType;
if (proc.Exists("ServerName"))
{
connectionHelper.ServerName = proc.GetString("ServerName");
}
if (proc.Exists("DatabaseName"))
{
connectionHelper.DatabaseName = proc.GetString("DatabaseName");
connectionHelper.UseFileName = false;
}
if (proc.Exists("FileName"))
{
connectionHelper.FileName = proc.GetString("FileName");
connectionHelper.UseFileName = true;
}
if (proc.Exists("UseIntegratedSecurity"))
{
connectionHelper.UseIntegratedSecurity = proc.GetBool("UseIntegratedSecurity");
}
if (proc.Exists("UserName"))
{
connectionHelper.UserName = proc.GetString("UserName");
}
if (proc.Exists("Password"))
{
string password = "";
try
{
password = proc.GetString("Password").Decrypt();
}
catch
{
// Do nothing
password = "";
}
connectionHelper.Password = password;
}
if (proc.Exists("Port"))
{
connectionHelper.Port = proc.GetInt("Port");
}
if (proc.Exists("ServiceName"))
{
connectionHelper.ServiceName = proc.GetString("ServiceName");
}
if (proc.Exists("SchemaName"))
{
connectionHelper.ServiceName = proc.GetString("SchemaName");
}
if (proc.Exists("UseDirectConnection"))
{
connectionHelper.UseDirectConnection = proc.GetBool("UseDirectConnection");
}
else
{
connectionHelper.UseDirectConnection = false;
}
return(connectionHelper);
}
/// <summary>
/// 获取标准SQL字符串
/// </summary>
/// <param name="tp"></param>
/// <param name="multiTable"></param>
/// <returns></returns>
public string ToSqlString(DatabaseTypes tp, bool multiTable = false)
{
string res = "UPDATE ";
res += _table.ToSqlString(tp);
res += " SET ";
string cols = "";
string keyCol = "";
if (!Equals(_keyField, null))
{
keyCol = _keyField.ToString();
}
foreach (var key in _row.Keys)
{
if (key != keyCol)
{
if (cols != "")
{
cols += ",";
}
using (SqlUnits.TableField field = new SqlUnits.TableField(_table, key)) {
cols += field.ToSqlString(tp);
}
//cols += $" = '{_row[key]}'";
switch (tp)
{
case DatabaseTypes.MySQL:
cols += $" = '{_row[key].Replace("'", "\'")}'";
break;
//return $"'{_value.Replace("'", "\'")}'";
case DatabaseTypes.Microsoft_Office_Access:
case DatabaseTypes.Microsoft_Office_Access_v12:
case DatabaseTypes.Microsoft_SQL_Server:
case DatabaseTypes.SQLite:
case DatabaseTypes.SQLite_3:
case DatabaseTypes.PostgreSQL:
cols += $" = '{_row[key].Replace("'", "''")}'";
break;
//return $"'{_value.Replace("'", "''")}'";
default:
throw new Exception($"尚未支持数据库 {tp.ToString()} 中的字符串转义。");
}
}
}
res += cols;
if (!keyCol.IsNone())
{
res += " WHERE ";
using (SqlUnits.TableField field = new SqlUnits.TableField(_table, keyCol)) {
res += field.ToSqlString(tp);
switch (tp)
{
case DatabaseTypes.MySQL:
res += $" = '{_row[keyCol].Replace("'", "\'")}'";
break;
//return $"'{_value.Replace("'", "\'")}'";
case DatabaseTypes.Microsoft_Office_Access:
case DatabaseTypes.Microsoft_Office_Access_v12:
case DatabaseTypes.Microsoft_SQL_Server:
case DatabaseTypes.SQLite:
case DatabaseTypes.SQLite_3:
case DatabaseTypes.PostgreSQL:
res += $" = '{_row[keyCol].Replace("'", "''")}'";
break;
//return $"'{_value.Replace("'", "''")}'";
default:
throw new Exception($"尚未支持数据库 {tp.ToString()} 中的字符串转义。");
}
//res += $" = '{_row[keyCol]}'";
}
}
else
{
if (!Equals(_where, null))
{
res += " WHERE " + _where.ToSqlString(tp);
}
}
return(res);
}
public string GetNHConnectionStringSqlClient()
{
string portString;
string serviceNameString;
switch (CurrentDbType)
{
//case DatabaseTypes.SQLServer2000:
case DatabaseTypes.SQLServer2005:
portString = Port == GetDefaultPort(CurrentDbType) ? "" : string.Format(",{0}", Port);
if (UseIntegratedSecurity)
{
return(string.Format("Server={0}{2};Database={1};Trusted_Connection=True", ServerName, DatabaseName, portString));
}
return(string.Format("Server={0}{4};Database={1};User ID={2};Password={3};Trusted_Connection=False", ServerName, DatabaseName, UserName, Password, portString));
case DatabaseTypes.SQLServerExpress:
EnsureSqlExpressServiceIsRunning();
if (UseFileName)
{
if (!SqlExpressDbIsAlreadyAttached)
{
return(string.Format(@"Server={0};AttachDbFilename='{2}';Database='{1}';Trusted_Connection=Yes;User Instance=True", ServerName, DatabaseName.Replace("_Data", ""), FileName));
}
// Don't include the filename
return(string.Format(@"Server={0};Database='{1}';Trusted_Connection=Yes;User Instance=True", ServerName, DatabaseName.Replace("_Data", ""), FileName));
}
if (UseIntegratedSecurity)
{
return(string.Format(@"Data Source={0};Initial Catalog={1};Integrated Security=SSPI;Pooling=False", ServerName, DatabaseName.Replace("_Data", ""), FileName));
}
return(string.Format(@"Server={0};Database='{1}';User ID={3};Password={4};Trusted_Connection=False;", ServerName, DatabaseName.Replace("_Data", ""), FileName, UserName, Password));
case DatabaseTypes.SQLCE:
return(string.Format("Data Source={0};", System.IO.Path.GetFileName(FileName)));
case DatabaseTypes.MySQL:
portString = Port == GetDefaultPort(CurrentDbType) ? "" : string.Format("Port={0};", Port);
return(string.Format("Database={1};Data Source={0};{4}User Id={2};Password={3};", ServerName, DatabaseName, UserName, Password, portString));
case DatabaseTypes.Oracle:
portString = Port == GetDefaultPort(CurrentDbType) ? "" : string.Format("Port={0};", Port);
return(string.Format("Data Source={0};{4}User ID={2};Password={3};", ServerName, DatabaseName, UserName, Password, portString));
case DatabaseTypes.PostgreSQL:
portString = Port == GetDefaultPort(CurrentDbType) ? "" : string.Format("Port={0};", Port);
//return string.Format("Server={0};initial catalog={1};User ID={2};Password={3};Database={4};", ServerName, SchemaName, UserName, Password, DatabaseName);
return(string.Format("Server={0};User ID={1};Password={2};Database={3};", ServerName, UserName, Password, DatabaseName));
case DatabaseTypes.Firebird:
portString = Port == GetDefaultPort(CurrentDbType) ? "" : string.Format("Port={0};", Port);
return(string.Format("Server={0};Database={1};User={2};Password={3};{4}", ServerName, FileName, UserName, Password, portString));
case DatabaseTypes.SQLite:
return(string.Format("'Data Source=\"{0}\";Version=3;New=True'", FileName));
default:
throw new NotImplementedException("Not coded yet: " + CurrentDbType.ToString());
}
}