/// <summary>
/// Initializes a new instance of the <see cref="T:frmLogin"/> class.
/// </summary>
/// <param name="DatabaseType">Type of the database.</param>
public frmLogin(DatabaseTypes DatabaseType)
{
InitializeComponent();
oDatabaseType = DatabaseType;
//Only display check box if type is Microsoft Sql Server
if (oDatabaseType == DatabaseTypes.SqlServer)
{
optWindowsAuth.Visible = true;
txtPort.Text = "";
}
else
{
optWindowsAuth.Visible = false;
if (txtPort.Text == "")
txtPort.Text = "3306";
}
if (optWindowsAuth.Checked == true)
{
txtUsername.Enabled = false;
txtPassword.Enabled = false;
}
else
{
txtUsername.Enabled = true;
txtPassword.Enabled = true;
}
}
/// <summary>
/// Constructor...
/// </summary>
public DatabaseConfig(string serverIp, string schema, string username, string password, DatabaseTypes dbType=DatabaseTypes.MySQL)
{
_serverIp = serverIp;
_schema = schema;
_username = username;
_password = password;
DatabaseType = dbType;
}
public ConnectionStringHelper(string serverName, string databaseName, string userName, string password, bool useIntegratedSecurity, bool useFilename, string filename, DatabaseTypes currentDbType)
{
ServerName = serverName;
DatabaseName = databaseName;
UserName = userName;
Password = password;
UseIntegratedSecurity = useIntegratedSecurity;
UseFileName = useFilename;
FileName = filename;
CurrentDbType = currentDbType;
}
//, Model.View.ViewPrefixes)
public View(DatabaseTypes dalAssemblyName, ConnectionStringHelper connectionString)
: base(dalAssemblyName, connectionString)
{
IView dal = DALFactory.DataAccess.CreateView(dalAssemblyName, ConnectionString);
Model.View[] views = dal.GetViews();
//InitiateAlias(views);
InitialCreateFilters(views);
_scriptObjects = new List<Model.View>(views);
}
/// <summary>
/// Initializes a new instance of the <see cref="T:SqlLogin"/> class.
/// </summary>
/// <param name="DatabaseType">Type of the database.</param>
/// <param name="Filename">The filename.</param>
public MySqlLogin(DatabaseTypes DatabaseType, string Filename)
{
oDatabaseType = DatabaseType;
sUserfile = Filename;
sUsername = "";
sHostname = "";
sDatabase = "";
sPassword = "";
sPort = "3306"; // TCP 3306 is MySQL Server Default Port
}
/// <summary>
/// Initializes a new instance of the <see cref="T:SqlLogin"/> class.
/// </summary>
public MySqlLogin()
{
oDatabaseType = DatabaseTypes.MySql;
sUsername = "";
sHostname = "";
sDatabase = "";
sPassword = "";
sPort = "3306"; // TCP 3306 is MySQL Server Default Port
sUserfile = "";
}
public void AddDatabase(string name, DatabaseTypes databaseType, ConnectionStringHelper connectionString, string[] tablePrefixes, string[] viewPrefixes, string[] storedProcedurePredixes)
{
Model.Table.TablePrefixes = new List<string>(tablePrefixes);
Model.View.ViewPrefixes = new List<string>(viewPrefixes);
Model.StoredProcedure.StoredProcedurePrefixes = new List<string>(storedProcedurePredixes);
Table bllTable = new Table(databaseType, connectionString);
View bllView = new View(databaseType, connectionString);
StoredProcedure bllStoredProcedure = new StoredProcedure(databaseType, connectionString);
Model.Database database = new Model.Database(name, connectionString, databaseType, bllTable.Tables, bllView.Views, bllStoredProcedure.StoredProcedures);
AddDatabase(database);
}
public static IDAL.IHelper CreateHelper(DatabaseTypes dalAssemblyName)
{
switch (dalAssemblyName)
{
case DatabaseTypes.SQLServer2000:
return new SQLServerDAL_2005.Helper();
case DatabaseTypes.SQLServer2005:
return new SQLServerDAL_2005.Helper();
case DatabaseTypes.SQLServerExpress:
return new SQLServerDAL_Express.Helper();
default:
throw new NotImplementedException("Not handled yet: " + dalAssemblyName);
}
}
public static IDAL.IView CreateView(DatabaseTypes dalAssemblyName, BLL.ConnectionStringHelper connectionString)
{
switch (dalAssemblyName)
{
case DatabaseTypes.SQLServer2000:
return new SQLServerDAL_2005.View(connectionString);
case DatabaseTypes.SQLServer2005:
return new SQLServerDAL_2005.View(connectionString);
case DatabaseTypes.SQLServerExpress:
return new SQLServerDAL_Express.View(connectionString);
default:
throw new NotImplementedException("Not handled yet: " + dalAssemblyName);
}
}
public static void InitializeConnections(DatabaseTypes db)
{
if (db == DatabaseTypes.Sql)
{
// TODO - Set up the SQL Connector properly.
SqlConnector sql = new SqlConnector();
Connection = sql;
}
else if (db == DatabaseTypes.TextFile)
{
// TODO - Set up the Text connector properly.
TextConnector text = new TextConnector();
Connection = text;
}
}
public DbConnection CreateConnection(string nameOrConnectionString, DatabaseTypes dbTypes)
{
var _dbConnection = default(DbConnection);
if (InvalidUserOrPassConns.ContainsKey(nameOrConnectionString))
{
throw (Exception)InvalidUserOrPassConns[nameOrConnectionString];
}
try
{
var profile = new Profile
{
ConnectionString = nameOrConnectionString,
DatabaseType = dbTypes
};
//Creates DbConnection using the dbcontext type name
_dbConnection = FrameworkAlcoaVault.GetConnectionByProfile(profile);
//Validates and authorizes machines for specified database instances
FrameworkAlcoaInstance.ValidateInstanceAuthorization(_dbConnection.DataSource);
//Test connection before use
_dbConnection.Open();
_dbConnection.Close();
}
catch (OracleException ex)
{
//Only adds to invalid dictionary if error is for Invalid User or Password
if (ex.ErrorCode == 1017)
{
InvalidUserOrPassConns[nameOrConnectionString] = ex;
}
if (_dbConnection.State != ConnectionState.Closed)
{
_dbConnection.Close();
}
_dbConnection.Dispose();
_dbConnection = null;
throw ex;
}
return(_dbConnection);
}
/// <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 virtual void Init()
{
_databaseFilePath = Path.GetTempFileName();
_sessionFactory = DataUtility.CreateSessionFactory(_databaseFilePath, DatabaseTypes.ToArray());
_session = _sessionFactory.OpenSession();
_clock = new StubClock();
var builder = new ContainerBuilder();
//builder.RegisterModule(new ImplicitCollectionSupportModule());
builder.RegisterInstance(new StubLocator(_session)).As <ISessionLocator>();
builder.RegisterInstance(_clock).As <IClock>();
builder.RegisterGeneric(typeof(Repository <>)).As(typeof(IRepository <>));
Register(builder);
_container = builder.Build();
}
public UTDatabaseInfo(UTDatabaseInfo src)
{
this.dbType = src.dbType;
this.code = src.code;
this.connectionChecked = src.connectionChecked;
this.databaseCatalog = src.databaseCatalog;
this.dbEnv = src.dbEnv;
this.envDesc = src.envDesc;
this.exceptionMessage = src.exceptionMessage;
this.databaseInstanceName = src.databaseInstanceName;
this.trustedConnection = src.trustedConnection;
this.user = src.user;
this.userPassword = src.userPassword;
this.dotNetProviderName = src.dotNetProviderName;
this.DataSourceDescription = src.DataSourceDescription;
}
/// <summary>
/// Khởi tạo tầng nghiệp vụ
/// (Hàm này phải được gọi trước khi sử dụng các chức năng khác trong lớp)
/// </summary>
/// <param name="dbType"></param>
/// <param name="connectionString"></param>
public static void Init(DatabaseTypes dbType, string connectionString)
{
switch (dbType)
{
case DatabaseTypes.SQLServer:
EmployeeDB = new LiteCommerce.DataLayers.SQLServer.EmployeeDAL(connectionString);
break;
case DatabaseTypes.FakeDB:
break;
default:
throw new Exception("Database Type is not supported");
}
}
/// <summary>
/// Get instance of <see cref="T"/> using connection string
/// </summary>
/// <param name="dbType">The type of the database (SQLite, SQLServer, ...)</param>
/// <param name="connectionString">The connection string</param>
/// <returns>Instance of <see cref="T"/></returns>
public T Get <T>(DatabaseTypes dbType, string connectionString)
{
RepositoryCreator repoCreator;
if (!_repositoryCreators.TryGetValue(typeof(T), out repoCreator))
{
throw new Exception("Interface not registered.");
}
if (repoCreator != null && repoCreator.CreatorByConnectionString != null)
{
return((T)repoCreator.CreatorByConnectionString(dbType, connectionString));
}
return(default(T));
}
public static void InitializeConnections(DatabaseTypes db)
{
if (db == DatabaseTypes.Sql)
{
// Set up sql connecter properly
var sql = new SqlConnector();
Connections = sql;
}
if (db == DatabaseTypes.TextFile)
{
// Create the Text Connections
var text = new TextConnector();
Connections = text;
}
}
private static RequestDelegate GetHandler<T, TE>(
Dictionary<string,List<AuthorizeAttribute>> restrictions,
Dictionary<Type, EntityKeyDescribtion> entityKeys,
DatabaseTypes dbType,
string connString,
InteractingType interactingType,
bool allowAnonimus,
string authentificationPath,
string accessDeniedPath,
Dictionary<string,RequestParamName> _requestParams,
JsonSerializerOptions jsonSerializerOptions = null,
Func<T> customDbContextFactory = null ) where T : DbContext, IDisposable
where TE : class
{
return async (context) =>
{
bool authResult = Authorization<TE>(context, HttpMethod.Get, restrictions, allowAnonimus,authentificationPath,accessDeniedPath);
if (!authResult)
{
return;
}
var e = entityKeys;
var QueryParams = RequestParams.RetriveQueryParam(context.Request.Query, _requestParams);
IEnumerable<TE> queryResult;
using (T dbcontext = CreateContext<T>(connString, dbType, customDbContextFactory))
{
queryResult = GetDBQueryResult<T, TE>(dbcontext, QueryParams);
}
if (interactingType == InteractingType.JSON)
{
byte[] jsonUtf8Bytes;
jsonUtf8Bytes = JsonSerializer.SerializeToUtf8Bytes(queryResult, jsonSerializerOptions);
await context.Response.WriteAsync(System.Text.Encoding.UTF8.GetString(jsonUtf8Bytes));
}
else if (interactingType == InteractingType.XML)
{
XmlRootAttribute a = new XmlRootAttribute("result");
// XmlSerializer does not support IEnumerable<T>
XmlSerializer serializer = new XmlSerializer(typeof(List<TE>),a);
StringWriter textWriter = new StringWriter();
serializer.Serialize(textWriter,queryResult.ToList());
await context.Response.WriteAsync(textWriter.ToString());
await textWriter.DisposeAsync();
}
};
}
public Database GetSchema(string connectionString, DatabaseTypes type)
{
Database database = new Database();
database.ConnString = connectionString;
database.Type = type;
//获取数据库结构
DbHelper dbHelper = DbHelper.Create("SqlServer");
dbHelper.ConnectionString = database.ConnString;
string sqlTable = GetSql(database.Type, SchemaHelper.SchemaType.Table);
DataSet ds = dbHelper.ExecuteDataset(CommandType.Text, sqlTable, null);
List <Table> tables = GetSQLTableList(ds);
//todo
//var db = DataPoco.GetPoco(connectionString, type);
//var dd = db.Query<FieldInfo>(sqlTable);
//foreach (var item in dd)
//{
// var SDSDS = item.DefaultValue;
//}
foreach (Table table in tables)
{
database.AddTable(table);
}
ds = dbHelper.ExecuteDataset(CommandType.Text,
GetSql(database.Type, SchemaHelper.SchemaType.View), null);
List <Table> views = GetSQLTableList(ds);
foreach (Table view in views)
{
database.AddView(view);
}
ds = dbHelper.ExecuteDataset(CommandType.Text, SqlForGetStoreProcedures, null);
database.StoreProcedures = GetSQLStoreProcedureList(ds);
return(database);
}
public static IDAL.IHelper CreateHelper(DatabaseTypes dalAssemblyName)
{
switch (dalAssemblyName)
{
case DatabaseTypes.SQLServer2000:
return(new SQLServerDAL_2005.Helper());
case DatabaseTypes.SQLServer2005:
return(new SQLServerDAL_2005.Helper());
case DatabaseTypes.SQLServerExpress:
return(new SQLServerDAL_Express.Helper());
default:
throw new NotImplementedException("Not handled yet: " + dalAssemblyName);
}
}
public static IDAL.ITable CreateTable(DatabaseTypes dalAssemblyName, BLL.ConnectionStringHelper connectionString)
{
switch (dalAssemblyName)
{
case DatabaseTypes.SQLServer2000:
return(new SQLServerDAL_2005.Table(connectionString));
case DatabaseTypes.SQLServer2005:
return(new SQLServerDAL_2005.Table(connectionString));
case DatabaseTypes.SQLServerExpress:
return(new SQLServerDAL_Express.Table(connectionString));
default:
throw new NotImplementedException("Not handled yet: " + dalAssemblyName);
}
}
public Table(DatabaseTypes dalAssemblyName, ConnectionStringHelper connectionString)
: base(dalAssemblyName, connectionString)//, Model.Table.TablePrefixes)
{
ITable dal = DALFactory.DataAccess.CreateTable(dalAssemblyName, ConnectionString);
Model.Table[] tables = dal.GetTables();
//InitiateAlias(tables);
InitialUpdateIndexes(tables);
InitialUpdateKeys(tables);
InitialCreateFilters(tables);
InitialUpdateRelationships(tables);
// GFH: I don't think that we should automatically add MapColumns, because we don't know what makes sense in the user's
// schema. Users get very confused as to why Mapped columns suddenly appear - causes more confusion than anything else.
//InitialUpdateMapColumns(tables);
_scriptObjects = new List <Model.Table>(tables);
}
//, Model.Table.TablePrefixes)
public Table(DatabaseTypes dalAssemblyName, ConnectionStringHelper connectionString)
: base(dalAssemblyName, connectionString)
{
ITable dal = DALFactory.DataAccess.CreateTable(dalAssemblyName, ConnectionString);
Model.Table[] tables = dal.GetTables();
//InitiateAlias(tables);
InitialUpdateIndexes(tables);
InitialUpdateKeys(tables);
InitialCreateFilters(tables);
InitialUpdateRelationships(tables);
// GFH: I don't think that we should automatically add MapColumns, because we don't know what makes sense in the user's
// schema. Users get very confused as to why Mapped columns suddenly appear - causes more confusion than anything else.
//InitialUpdateMapColumns(tables);
_scriptObjects = new List<Model.Table>(tables);
}
/// <summary>
/// Khởi tạo tính năng tác nghiệp (Hàm này phải được gọi nếu muốn sử dụng các tính năng của lớp)
/// </summary>
/// <param name="dbType"></param>
/// <param name="connectionString"></param>
public static void Init(DatabaseTypes dbType, string connectionString)
{
switch (dbType)
{
case DatabaseTypes.SQLServer:
CountryDB = new DataLayers.SQLServer.CountryDAL(connectionString);
CityDB = new DataLayers.SQLServer.CityDAL(connectionString);
SupplierDB = new DataLayers.SQLServer.SupplierDAL(connectionString);
CategoryDB = new DataLayers.SQLServer.CategoryDAL(connectionString);
CustomerDB = new DataLayers.SQLServer.CustomerDAL(connectionString);
EmployeeDB = new DataLayers.SQLServer.EmployeeDAL(connectionString);
ShipperDB = new DataLayers.SQLServer.ShipperDAL(connectionString);
break;
default:
throw new Exception("Database Type is not Supported");
}
}
public string GetDefaultServiceName(DatabaseTypes databaseType)
{
return("");
//switch (databaseType)
//{
// case DatabaseTypes.MySQL:
// return "";
// case DatabaseTypes.Oracle:
// return 1521;
// case DatabaseTypes.SQLServer2000:
// case DatabaseTypes.SQLServer2005:
// return 1433;
// //case DatabaseTypes.PostgreSQL:
// // return 5432;
//}
}
void ExecuteQuery(DataSource dataSource, IdpeKey connectionStringKey, string updateQuery)
{
//IdpeKey connectionStringKey = dataSource.Keys.GetKey(connectionStringKeyName);
//if (connectionStringKey == null)
// throw new KeyNotFoundException(string.Format("The connection string '{0}' was not defined!", connectionStringKeyName));
DatabaseTypes databaseType = connectionStringKey.GetDatabaseType();
string actualConnectionString = connectionStringKey.Value;
IDal myDal = new DataAccessLayer(databaseType).Instance;
IDbConnection conn = myDal.CreateConnection(actualConnectionString);
conn.Open();
IDbTransaction transaction = myDal.CreateTransaction(conn);
IDbCommand commandUpdate = myDal.CreateCommand();
commandUpdate.Connection = conn;
commandUpdate.Transaction = transaction;
commandUpdate.CommandText = new CommandParser(dataSource).Parse(updateQuery);
try
{
commandUpdate.ExecuteNonQuery();
transaction.Commit();
}
catch (Exception ex)
{
transaction.Rollback();
ExtensionMethods.TraceError(ex.ToString());
throw new Exception(ex.Message, ex);
}
finally
{
if (conn.State == ConnectionState.Open)
{
conn.Close();
}
conn.Dispose();
commandUpdate.Dispose();
}
}
/// <summary>
/// Connection strings and database setup for the application
/// </summary>
/// <param name="services"></param>
/// <param name="config"></param>
public static void AddCustomDataStore(this IServiceCollection services, IConfiguration config)
{
string userConnectionString = config.GetConnectionString("UserDbConnectionString");
string webServerConnectionString = config.GetConnectionString("WebServerDbConnectionString");
DatabaseTypes dbType = (DatabaseTypes)config.GetValue <int>("Options:DatabaseType");
if (dbType == DatabaseTypes.MSSQLSERVER)
{
using (var client = new UserContext(new DbContextOptionsBuilder <UserContext>().UseSqlServer(userConnectionString, a => a.MigrationsAssembly("VueServer")).Options))
{
client.Database.EnsureCreated();
}
using (var client = new WSContext(new DbContextOptionsBuilder <WSContext>().UseSqlServer(webServerConnectionString, a => a.MigrationsAssembly("VueServer")).Options))
{
client.Database.Migrate();
}
services.AddEntityFrameworkSqlServer().AddDbContext <UserContext>
(options => options.UseSqlServer(userConnectionString, a => a.MigrationsAssembly("VueServer")), ServiceLifetime.Scoped);
services.AddEntityFrameworkSqlServer().AddDbContext <WSContext>
(options => options.UseSqlServer(webServerConnectionString, a => a.MigrationsAssembly("VueServer")), ServiceLifetime.Scoped);
}
else if (dbType == DatabaseTypes.SQLITE)
{
using (var client = new UserContext(new DbContextOptionsBuilder <UserContext>().UseSqlite(userConnectionString, a => a.MigrationsAssembly("VueServer")).Options))
{
client.Database.EnsureCreated();
}
using (var client = new WSContext(new DbContextOptionsBuilder <WSContext>().UseSqlite(webServerConnectionString, a => a.MigrationsAssembly("VueServer")).Options))
{
client.Database.Migrate();
}
services.AddEntityFrameworkSqlite().AddDbContext <UserContext>
(options => options.UseSqlite(userConnectionString, a => a.MigrationsAssembly("VueServer")), ServiceLifetime.Scoped);
services.AddEntityFrameworkSqlite().AddDbContext <WSContext>
(options => options.UseSqlite(webServerConnectionString, a => a.MigrationsAssembly("VueServer")), ServiceLifetime.Scoped);
}
}
private StringBuilder WriteOutputToDatabase(List <Row> row)
{
DatabaseTypes databaseType = ConnectionStringKey.GetDatabaseType();
switch (databaseType)
{
case DatabaseTypes.SqlServer:
return(WriteOutputToDatabaseSql(row));
case DatabaseTypes.SqlCe:
return(WriteOutputToDatabaseSqlCe(row));
case DatabaseTypes.Oracle:
return(WriteOutputToDatabaseOracle(row));
default:
throw new Exception(string.Format("The '{0}' type database output writer has not been implemented yet!", databaseType));
}
}
//, Model.StoredProcedure.StoredProcedurePrefixes)
public StoredProcedure(DatabaseTypes dalAssemblyName, ConnectionStringHelper connectionString)
: base(dalAssemblyName, connectionString)
{
IStoredProcedure dal = DALFactory.DataAccess.CreateStoredProcedure(DalAssemblyName, ConnectionString);
Model.StoredProcedure[] storedProcedures = dal.GetStoredProcedures();
//InitiateAlias(storedProcedures);
//this.ErrorMessages.AddRange(dal.
InitialCreateFilters(storedProcedures);
foreach (Model.StoredProcedure sp in storedProcedures)
{
foreach (string error in sp.Errors)
{
ErrorMessages.Add(error);
}
}
_scriptObjects = new List<Model.StoredProcedure>(storedProcedures);
}
public StoredProcedure(DatabaseTypes dalAssemblyName, ConnectionStringHelper connectionString)
: base(dalAssemblyName, connectionString)//, Model.StoredProcedure.StoredProcedurePrefixes)
{
IStoredProcedure dal = DALFactory.DataAccess.CreateStoredProcedure(DalAssemblyName, ConnectionString);
Model.StoredProcedure[] storedProcedures = dal.GetStoredProcedures();
//InitiateAlias(storedProcedures);
//this.ErrorMessages.AddRange(dal.
InitialCreateFilters(storedProcedures);
foreach (Model.StoredProcedure sp in storedProcedures)
{
foreach (string error in sp.Errors)
{
ErrorMessages.Add(error);
}
}
_scriptObjects = new List <Model.StoredProcedure>(storedProcedures);
}
/// <summary>
/// Convert string to DatabaseTypes
/// </summary>
/// <param name="type">String type</param>
/// <returns>DatabaseTypes</returns>
private static DatabaseTypes ConvertDBType(string type)
{
type = type.Trim();
DatabaseTypes dbType = DatabaseTypes.None;
Object o = Enum.Parse(typeof(DatabaseTypes), type, true);
if (o != null)
{
dbType = (DatabaseTypes)o;
}
else
{
throw new ArgumentException("Undefined type of database " + type + ".");
}
return(dbType);
}
/// <summary>
/// 取得表结构的SQL语句
/// </summary>
private string GetSql(DatabaseTypes type, SchemaHelper.SchemaType schemaType)
{
StringBuilder strSql = new StringBuilder();
strSql.Append("SELECT d.name N'TableName',a.colorder N'FieldNumber',a.name N'FieldName', ");
strSql.Append("(case when COLUMNPROPERTY( a.id,a.name,'IsIdentity')=1 then '1'else '0' end) N'IsId',");
strSql.Append("(case when (SELECT count(*) ");
strSql.Append(" FROM sysobjects WHERE (name in (SELECT name FROM sysindexes ");
strSql.Append(" WHERE (id = a.id) AND (indid in (SELECT indid FROM sysindexkeys");
strSql.Append(" WHERE (id = a.id) AND (colid in (SELECT colid FROM syscolumns");
strSql.Append(" WHERE (id = a.id) AND (name = a.name))))))) AND (xtype = 'PK'))>0 ");
strSql.Append(" then '1' else '0' end) N'IsKey', b.name N'FieldType',a.length N'FieldSize', ");
strSql.Append(" COLUMNPROPERTY(a.id,a.name,'PRECISION') as N'FieldLength', ");
strSql.Append(" isnull(COLUMNPROPERTY(a.id,a.name,'Scale'),0) as N'DecimalDigits', ");
strSql.Append(" (case when a.isnullable=1 then '1'else '0' end) N'AllowNull', isnull(e.text,'') N'DefaultValue', ");
strSql.Append(" isnull(g.[value],'') AS N'FieldDescn' ");
strSql.Append(" FROM syscolumns a left join systypes b on a.xtype=b.xusertype inner join sysobjects d on a.id=d.id");
switch (schemaType)
{
case SchemaHelper.SchemaType.View:
strSql.Append(" and d.xtype='V'");
break;
case SchemaHelper.SchemaType.Table:
default:
strSql.Append(" and d.xtype='U'");
break;
}
strSql.Append("and d.name<>'dtproperties' left join syscomments e on a.cdefault=e.id");
if (type == DatabaseTypes.Sql2005)
{
strSql.Append(" left join sys.extended_properties g on a.id=g.major_id AND a.colid = g.minor_id order by object_name(a.id),a.colorder");
}
else
{
strSql.Append(" left join sysproperties g on a.id=g.id AND a.colid = g.smallid order by object_name(a.id),a.colorder ");
}
return(strSql.ToString());
}
public static void Init(DatabaseTypes dbType, string connectionString, AccountTypes accountType)
{
switch (dbType)
{
case DatabaseTypes.SQLServer:
if (accountType == AccountTypes.Employee)
{
AccountDB = new DataLayers.SQLServer.EmployeeAccountDAL(connectionString);
}
else
{
AccountDB = new DataLayers.SQLServer.CustomerAccountDAL(connectionString);
}
break;
default:
throw new Exception("Database Type is not Supported");
}
}
/// <summary>
/// Gets the database schema and creats an object model.
/// </summary>
/// <param name="name"></param>
/// <param name="databaseType"></param>
/// <param name="connectionString"></param>
/// <param name="tablePrefixes"></param>
/// <param name="viewPrefixes"></param>
/// <param name="storedProcedurePredixes"></param>
/// <param name="fetchTables"></param>
/// <param name="fetchViews"></param>
/// <param name="fetchStroredProcedures"></param>
/// <returns></returns>
public static Database LoadNewDatabase(
string name,
DatabaseTypes databaseType,
BLL.ConnectionStringHelper connectionString,
List <string> tablePrefixes,
List <string> viewPrefixes,
List <string> storedProcedurePredixes,
bool fetchTables,
bool fetchViews,
bool fetchStroredProcedures)
{
Utility.ResetAllConnections();
Table.TablePrefixes = tablePrefixes ?? new List <string>();
View.ViewPrefixes = viewPrefixes ?? new List <string>();
StoredProcedure.StoredProcedurePrefixes = storedProcedurePredixes ?? new List <string>();
BLL.Table bllTable;
BLL.View bllView;
BLL.StoredProcedure bllStoredProcedure;
Table[] tables = null;
View[] views = null;
StoredProcedure[] storedProcedures = null;
if (fetchTables)
{
bllTable = new BLL.Table(databaseType, connectionString);
tables = bllTable.Tables;
}
if (fetchViews)
{
bllView = new BLL.View(databaseType, connectionString);
views = bllView.Views;
}
if (fetchStroredProcedures)
{
bllStoredProcedure = new BLL.StoredProcedure(databaseType, connectionString);
storedProcedures = bllStoredProcedure.StoredProcedures;
}
return(new Database(name, connectionString, databaseType, tables, views, storedProcedures));
}
public IDatabaseLoader DeserialiseConnectionInformation(XmlNode node, string databaseName)
{
if (node == null)
{
throw new ArgumentNullException("node");
}
NodeProcessor proc = new NodeProcessor(node);
DatabaseTypes connectorType = proc.Attributes.GetEnum <DatabaseTypes>("DatabaseConnector");
switch (connectorType)
{
//case DatabaseTypes.SQLServer2000:
case DatabaseTypes.SQLServer2005:
return(LoadSqlServer2005Connector(node));
case DatabaseTypes.SQLServerExpress:
return(LoadSqlServerExpressConnector(node, databaseName));
case DatabaseTypes.SQLCE:
return(LoadSqlCEConnector(node));
case DatabaseTypes.MySQL:
return(LoadMySQLConnector(node));
case DatabaseTypes.Oracle:
return(LoadOracleConnector(node));
case DatabaseTypes.PostgreSQL:
return(LoadPostgreSQLConnector(node));
case DatabaseTypes.Firebird:
return(LoadFirebirdConnector(node));
case DatabaseTypes.SQLite:
return(LoadSQLiteConnector(node));
default:
throw new NotImplementedException(string.Format("Have not implemented {0} in DeserialiseConnectionInformation().", connectorType));
}
}
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!");
}
}
private void PopulateDatabaseTypes()
{
comboBoxDatabaseTypes.Items.Clear();
Array enumValues = Enum.GetValues(typeof(DatabaseTypes));
DatabaseTypes lastDbUsed = SettingsEngine.LastDatabaseTypeUsed;
int sqlIndex = -1;
if (lastDbUsed == DatabaseTypes.Unknown)
{
lastDbUsed = DatabaseTypes.SQLServer2005;
}
foreach (DatabaseTypes item in enumValues)
{
if (item == DatabaseTypes.Unknown)
{
continue;
}
//if (item == DatabaseTypes.SQLite)
// // TODO: Remove this when SQLite dev is finished
// continue;
var description = Slyce.Common.Utility.GetDescription(item);
comboBoxDatabaseTypes.Items.Add(new Slyce.Common.ComboBoxItemEx <DatabaseTypes>(item, f => description));
if (item == DatabaseTypes.SQLServer2005)
{
sqlIndex = comboBoxDatabaseTypes.Items.Count - 1;
}
if (item == lastDbUsed)
{
comboBoxDatabaseTypes.SelectedIndex = comboBoxDatabaseTypes.Items.Count - 1;
}
}
if (comboBoxDatabaseTypes.SelectedIndex < 0)
{
comboBoxDatabaseTypes.SelectedIndex = sqlIndex;
}
//comboBoxDatabaseTypes.Sorted = true;
}
public virtual void Init()
{
_databaseFilePath = Path.GetTempFileName();
_sessionFactory = DataUtility.CreateSessionFactory(_databaseFilePath, DatabaseTypes.ToArray());
_session = _sessionFactory.OpenSession();
_transaction = _session.BeginTransaction(IsolationLevel.ReadCommitted);
_clock = new StubClock();
var builder = new ContainerBuilder();
//builder.RegisterModule(new ImplicitCollectionSupportModule());
builder.RegisterInstance(new StubLocator(_session)).As <ISessionLocator>();
builder.RegisterInstance(_clock).As <IClock>();
builder.RegisterGeneric(typeof(Repository <>)).As(typeof(IRepository <>));
builder.RegisterInstance(new ShellSettings {
Name = ShellSettings.DefaultName, DataProvider = "SqlCe"
});
Register(builder);
_container = builder.Build();
}
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>
/// Initializes a new instance of the DataLayerBase class.
/// </summary>
/// <param name="databaseType">Data base type and create this class</param>
/// <param name="dbConnection">Connection specific SQL server type</param>
/// <param name="parameterPrefix">Paramether prefix specific SQL server</param>
/// <param name="parameterNames">Parameter names</param>
protected DataLayerBase(DatabaseTypes databaseType, IDbConnection dbConnection, string parameterPrefix, ConnectionParameterNames parameterNames)
{
if (databaseType == DatabaseTypes.None)
{
throw new ArgumentException("constructor DataLayerBase(). DatabaseType can not be 'None'!");
}
_dataBaseType = databaseType;
if (dbConnection == null)
{
throw new ArgumentNullException("dbConnection", "Can not be Null!");
}
_connection = dbConnection;
_propertyBindingFlag = BindingFlags.IgnoreCase | BindingFlags.Public | BindingFlags.Instance;
// Create ConnectionString
_connectionString = new DBConnectionString(databaseType, parameterNames);
_connectionString.Changed += new EventHandler(ConnectionString_Changed);
_lastError = string.Empty;
// This a default initialization
//_reRaiseException = false;
_keepConnection = true;
_inNotReconnected = true;
// Set specific Prameter prefix
_paramPrefix = parameterPrefix;
// Create command
CreateCommand();
// Set command type
_command.CommandType = CommandType.Text;
_defaultCommandType = CommandType.Text;
}
/// <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()} 类型数据库");
}
}
public static string[] GetDatabases(string serverName, string userName, string password, DatabaseTypes dbType, bool trustedConnection)
{
// SQL Server, Sybase: SELECT * FROM master..sysdatabases
// MySQL: show databases;
// Oracle: SELECT tablespace_name FROM dba_tablespaces
List<string> arrDatabases = new List<string>();
SqlConnection conn = null;
System.Data.SqlClient.SqlDataReader dr = null;
if (trustedConnection)
{
conn = new SqlConnection(string.Format("Server={0};Database=master;Trusted_Connection=True", serverName));
}
else
{
conn = new SqlConnection(string.Format("Server={0};Database=master;User ID={1};Password={2};Trusted_Connection=False", serverName, userName, password));
}
try
{
conn.Open();
SqlCommand cmd = new SqlCommand("SELECT * FROM master..sysdatabases", conn);
dr = cmd.ExecuteReader();
while (dr.Read())
{
arrDatabases.Add(dr.GetString(0));
}
}
finally
{
if (dr != null) { dr.Close(); }
if (conn != null) { conn.Close(); }
}
return (string[])arrDatabases.ToArray();
}
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;
}
public string GetConnectionStringSqlClient(DatabaseTypes databaseType)
{
switch (databaseType)
{
case DatabaseTypes.SQLServer2000:
case DatabaseTypes.SQLServer2005:
if (UseIntegratedSecurity) { return string.Format("Server={0};Database={1};Trusted_Connection=True", ServerName, DatabaseName); }
return string.Format("Server={0};Database={1};User ID={2};Password={3};Trusted_Connection=False", ServerName, DatabaseName, UserName, Password);
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);
default:
throw new NotImplementedException("Not coded yet.");
}
}
//, List<string> scriptObjectPrefixes)
protected ScriptBLL(DatabaseTypes dalAssemblyName, ConnectionStringHelper connectionString)
{
DalAssemblyName = dalAssemblyName;
ConnectionString = connectionString;
}
public DatabaseUpdater(EntitySetting pSettings, DatabaseTypes pType)
{
this._setting = pSettings;
this._type = pType;
}
/// <summary>
/// Initializes a new instance of the <see cref="T:SqlLogin"/> class.
/// </summary>
/// <param name="DatabaseType">Type of the database.</param>
/// <param name="Hostname">The hostname.</param>
/// <param name="Username">The username.</param>
/// <param name="Password">The password.</param>
/// <param name="Database">The database.</param>
/// <param name="Port">The port.</param>
public MySqlLogin(DatabaseTypes DatabaseType, string Hostname, string Username, string Password, string Database, string Port)
{
oDatabaseType = DatabaseType;
sHostname = Hostname;
sUsername = Username;
sPassword = Password;
sDatabase = Database;
if (int.Parse(Port) > 0)
sPort = Port;
else
sPort = "3306"; // TCP 3306 is MySQL Server Default Port
sUserfile = "";
BuildConnectionString();
}
private void FillFromConnectionString(string connectionString, DatabaseTypes databaseType)
{
var values = connectionString.Trim(' ', '"', ';').Split(';');
foreach (var nameValuePair in values)
{
string nameValuePairValue = nameValuePair.Trim();
if (string.IsNullOrEmpty(nameValuePairValue))
continue;
var data = nameValuePairValue.Split('=');
string name = data[0], value = data[1];
switch (name.ToLower())
{
case "server":
ServerName = value;
break;
case "data source":
if (databaseType == DatabaseTypes.SQLCE)
{
FileName = value;
UseFileName = true;
if (string.IsNullOrEmpty(value) || value.LastIndexOf(".") < 0)
DatabaseName = "";
else
DatabaseName = value.Substring(0, value.LastIndexOf(".") - 1);
}
else if (databaseType == DatabaseTypes.SQLServer2005)
{
UseFileName = false;
ServerName = value;
}
else if (databaseType == DatabaseTypes.MySQL)
{
UseFileName = false;
ServerName = value;
}
else if (databaseType == DatabaseTypes.Oracle)
{
UseFileName = false;
ServerName = value;
}
else if (databaseType == DatabaseTypes.SQLite)
{
FileName = value;
}
break;
case "initial catalog":
DatabaseName = value;
break;
case "database":
DatabaseName = value;
break;
case "integrated security":
UseIntegratedSecurity = value.Equals("SSPI", StringComparison.OrdinalIgnoreCase);
break;
case "trusted_connection":
UseIntegratedSecurity = ParseBoolean(value);
break;
case "user id":
UserName = value;
break;
case "password":
Password = value;
break;
case "user instance":
UseFileName = ParseBoolean(value);
break;
}
}
}
public DBHelper(DatabaseTypes databaseType, string connectionString)
{
DatabaseType = databaseType;
this._connectionString = connectionString;
}
public DbHelper(DatabaseTypes databaseType, string connectionString)
{
DatabaseType = databaseType;
ConnectionString = connectionString;
}
public Project Connect(DatabaseTypes type,string userName,string password,string database,string serverName,bool winAuth)
{
Project project = null;
try
{
project = new Project();
project.DatabaseType = type;
project.UserName = userName;
project.Password = password;
project.ServerName = serverName;
project.IsWindowsAuthentication = winAuth;
SetDatabaseReader(project);
SetConnectionString(project, database);
//System.Data.Common.DbConnection conn = null;
//if (databaseSchemaReader is SQLServerDatabaseExtractor)
//{
// conn = new System.Data.SqlClient.SqlConnection(connectionString);
//}
//conn.Open();
}
catch (Exception ex)
{
throw ex;
}
return project;
}
public H31DBSQL(DatabaseTypes type, string connectionString)
{
this.databaseType = type;
this.connectionString = connectionString;
}
public void LoadNewDatabase(
int index,
string name,
DatabaseTypes databaseType,
ConnectionStringHelper connectionString,
List<string> tablePrefixes,
List<string> viewPrefixes,
List<string> storedProcedurePredixes,
bool fetchTables,
bool fetchViews,
bool fetchStroredProcedures)
{
Model.Database database = Model.Database.LoadNewDatabase(name, databaseType, connectionString, tablePrefixes, viewPrefixes, storedProcedurePredixes, fetchTables, fetchViews, fetchStroredProcedures);
UpdateDatabase(database, index);
}
public string GetConnectionStringOleDb(DatabaseTypes databaseType)
{
switch (databaseType)
{
case DatabaseTypes.SQLServer2000:
if (UseIntegratedSecurity) { return string.Format("Provider=sqloledb;Data Source={0};Initial Catalog={1};Integrated Security=SSPI;", ServerName, DatabaseName); }
return string.Format("Provider=sqloledb;Data Source={0};Initial Catalog={1};User Id={2};Password={3};", ServerName, DatabaseName, UserName, Password);
case DatabaseTypes.SQLServer2005:
if (UseIntegratedSecurity) { return string.Format("Provider=SQLNCLI;Server={0};Database={1};Trusted_Connection=yes;", ServerName, DatabaseName); }
return string.Format("Provider=SQLNCLI;Server={0};Database={1};UID={2};PWD={3};", ServerName, DatabaseName, UserName, Password);
case DatabaseTypes.SQLServerExpress:
if (FileName.Length == 0)
{
throw new MissingFieldException("SQLServerExpress connectionstring requires FileName to be set.");
}
// Make sure the SQLEXPRESS Windows Service is running on the local machine
EnsureSqlExpressServiceIsRunning();
return string.Format(@"Provider=SQLNCLI;Server=.\SQLExpress;AttachDbFilename='{2}';Database={1};Trusted_Connection=Yes;", ServerName, DatabaseName.Replace("_Data", ""), FileName);
default:
throw new NotImplementedException("Not coded yet.");
}
}
public ProviderBase()
{
_databaseType = ConfigManager.DataBaseType;
}
/// <summary>
/// Creates a new instance of the manager given a connection string
/// </summary>
/// <param name="dbType">The type of the database (SQLite, SQLServer, ...)</param>
/// <param name="connectionString">The connection string</param>
public MetadataCacheManagerSQL(DatabaseTypes dbType, string connectionString)
{
_db = new DbOperations(connectionString, dbType);
}
public BuilderFactory(DatabaseTypes databaseType)
{
_databaseType = databaseType;
}
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 Helper(DatabaseTypes dalAssemblyName)
{
_dalAssemblyName = dalAssemblyName;
}
