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