/// <summary>
/// Writes the database to the location defined in the configuration file if it doesn't exist.
/// </summary>
public static void CheckForDatabase()
{
// A LocalSqlServer connection string get's added by default. Don't look at that one.
foreach (ConnectionStringSettings connection in ConfigurationManager.ConnectionStrings.OfType<ConnectionStringSettings>().Where(c => c.Name != "LocalSqlServer"))
{
try
{
EntityConnectionStringBuilder builder = new EntityConnectionStringBuilder(connection.ConnectionString);
SqlCeConnectionStringBuilder sqlCEBuilder = new SqlCeConnectionStringBuilder(builder.ProviderConnectionString);
if (!File.Exists(sqlCEBuilder.DataSource))
{
FileInfo info = new FileInfo(sqlCEBuilder.DataSource);
if (!Directory.Exists(info.Directory.FullName))
{
Directory.CreateDirectory(info.Directory.FullName);
}
File.WriteAllBytes(info.FullName, EncounterTracker.Properties.Resources.EncounterTracker);
}
break;
}
catch
{
}
}
}
public bool TryConnect(string connectionString, out string errorMessage)
{
try
{
var csb = new SqlCeConnectionStringBuilder(connectionString);
var replaceDataDirectory = ReplaceDataDirectory(csb.DataSource);
if (!System.IO.File.Exists(replaceDataDirectory))
{
var directoryName = Path.GetDirectoryName(replaceDataDirectory);
if (directoryName != null && !Directory.Exists(directoryName))
{
Directory.CreateDirectory(directoryName);
}
new SqlCeEngine(connectionString).CreateDatabase();
}
errorMessage = "";
using (var connection = new SqlCeConnection(csb.ConnectionString))
{
connection.Open();
new SqlCeCommand("select 1", connection).ExecuteScalar();
}
return(true);
}
catch (Exception e)
{
errorMessage = e.Message;
return(false);
}
}
private void GenerateDataContext()
{
var code = new StringBuilder();
code.AppendLine("\nnamespace " + Database.DefaultNamespace);
code.AppendLine("{");
code.AppendLine("\tusing System.Data.Linq;");
code.AppendLine();
var connStr = new SqlCeConnectionStringBuilder(Database.ConnectionString);
var dataSource = new FileInfo(connStr.DataSource);
var className = dataSource.Name.Trim(' ').Replace(dataSource.Extension, string.Empty);
GenerateXmlDoc(code, 1, "Represents the entity data context");
code.AppendLine("\tpublic partial class EntityDataContext : DataContext");
code.AppendLine("\t{");
GenerateXmlDoc(code, 2, "Global Connection String");
code.AppendLine("\t\tpublic static string ConnectionString = \"Data Source=isostore:/" + dataSource.Name + "\";");
code.AppendLine();
GenerateXmlDoc(code, 2, "Creates an instance of the " + className + " data context");
code.AppendLine("\t\tpublic EntityDataContext() : this(ConnectionString)");
code.AppendLine("\t\t{");
code.AppendLine("\t\t}");
code.AppendLine();
GenerateXmlDoc(code, 2, "Creates an instance of the entity data context",
new KeyValuePair <string, string>("connectionString", "connection string to be used for this instance"));
code.AppendLine("\t\tpublic EntityDataContext(string connectionString) : base(connectionString)");
code.AppendLine("\t\t{");
code.AppendLine("\t\t\tif (!DatabaseExists())");
code.AppendLine("\t\t\t\tCreateDatabase();");
code.AppendLine("\t\t}");
//GenerateXmlDoc(code, 2, "Factory method that creates an instance of the entity data context");
//code.AppendLine("\t\tpublic static EntityDataContext Create()");
//code.AppendLine("\t\t{");
//code.AppendLine("\t\t\treturn Create(ConnectionString);");
//code.AppendLine("\t\t}");
//GenerateXmlDoc(code, 2, "Factory method that creates an instance of the entity data context",
// new KeyValuePair<string, string>("connectionString", "connection string to be used for this instance"));
//code.AppendLine("\t\tpublic static EntityDataContext Create(string connectionString)");
//code.AppendLine("\t\t{");
//code.AppendLine("\t\t\treturn new EntityDataContext(connectionString);");
//code.AppendLine("\t\t}");
foreach (var table in Database.Tables)
{
code.AppendLine();
GenerateXmlDoc(code, 2, "Represents the " + table.DisplayName + " table");
code.AppendLine("\t\tpublic Table<" + table.ClassName + "> " + table.ClassName + ";");
}
code.AppendLine("\t}");
code.AppendLine("}");
AppendCode(className + "DataContext", code);
}
public static IEnumerable <string> GetFileDatabaseTables(string databaseFile, string password = null)
{
if (string.IsNullOrEmpty(databaseFile) || !File.Exists(databaseFile))
{
throw new Exception("Database not exists");
}
IList <string> tableNames = new List <string>();
SqlCeConnectionStringBuilder connectionString = new SqlCeConnectionStringBuilder();
connectionString.DataSource = databaseFile;
if (!string.IsNullOrEmpty(password))
{
connectionString.Password = password;
}
using (SqlCeConnection sqlCon = new SqlCeConnection(connectionString.ToString()))
{
string sqlStmt = "SELECT table_name FROM INFORMATION_SCHEMA.TABLES";
using (SqlCeCommand cmd = new SqlCeCommand(commandText: sqlStmt, connection: sqlCon))
{
sqlCon.Open();
var reader = cmd.ExecuteReader();
while (reader.Read())
{
tableNames.Add((string)reader["table_name"]);
}
sqlCon.Close();
}
}
return(tableNames);
}
public override DbConnectionStringBuilder GetConnectionStringBuilder(string connectionString = null)
{
var sqlCeConnectionStringBuilder = new SqlCeConnectionStringBuilder(connectionString);
sqlCeConnectionStringBuilder.BrowsableConnectionString = false;
return(sqlCeConnectionStringBuilder);
}
public List<Models.LogLiveEvent> Query(DiscoDataContext Database)
{
List<Models.LogLiveEvent> results = new List<LogLiveEvent>();
// Validate Options
this.Validate();
var relevantLogFiles = RelevantLogFiles(Database);
relevantLogFiles.Reverse();
foreach (var logFile in relevantLogFiles)
{
SqlCeConnectionStringBuilder sqlCeCSB = new SqlCeConnectionStringBuilder();
sqlCeCSB.DataSource = logFile.Item1;
var logModules = LogContext.LogModules;
using (var context = new LogPersistContext(sqlCeCSB.ToString()))
{
var query = this.BuildQuery(context, logFile.Item2, results.Count);
IEnumerable<LogEvent> queryResults = query; // Run the Query
results.AddRange(queryResults.Select(le => Models.LogLiveEvent.Create(logModules[le.ModuleId], logModules[le.ModuleId].EventTypes[le.EventTypeId], le.Timestamp, le.Arguments)));
}
if (this.Take.HasValue && this.Take.Value < results.Count)
break;
}
return results;
}
public void build_sqlce_connection_with_password()
{
// Arrange
SqlCeConnectionStringBuilder builder = new SqlCeConnectionStringBuilder();
string databaseName = "BlogApp";
string password = "******";
string dbName = databaseName;
if (databaseName.IndexOf(".sdf") == -1)
{
dbName = databaseName + ".sdf";
}
builder.DataSource = string.Format(@"|DataDirectory|\{0}", dbName);
if (!string.IsNullOrEmpty(password))
{
builder.Password = password;
}
// Act
var actual = builder.ToString();
var expected = @"Data Source=|DataDirectory|\BlogApp.sdf;Password=myPassword";
// Assert
Assert.AreEqual(expected, actual);
}
/// <summary>
/// Configures the context to connect to a Microsoft SQL Server Compact database.
/// </summary>
/// <param name="optionsBuilder"> The builder being used to configure the context. </param>
/// <param name="connectionStringBuilder"> A connection string builder with the connection string of the database to connect to. </param>
/// <param name="sqlCeOptionsAction">An optional action to allow additional SQL Server Compact specific configuration.</param>
/// <returns> The options builder so that further configuration can be chained. </returns>
public static DbContextOptionsBuilder <TContext> UseSqlCe <TContext>(
[NotNull] this DbContextOptionsBuilder <TContext> optionsBuilder,
[NotNull] SqlCeConnectionStringBuilder connectionStringBuilder,
[CanBeNull] Action <SqlCeDbContextOptionsBuilder> sqlCeOptionsAction = null)
where TContext : DbContext
=> (DbContextOptionsBuilder <TContext>)UseSqlCe(
(DbContextOptionsBuilder)optionsBuilder, connectionStringBuilder, sqlCeOptionsAction);
public static string GetConnStringExSecurityInfo(this IDbConnection connection)
{
try
{
if (connection is SqlConnection)
{
var builder = new SqlConnectionStringBuilder(connection.ConnectionString);
return(string.Format("DataSource: {0}, InitialCatalog: {1}", builder.DataSource, builder.InitialCatalog));
}
if (connection is SqlCeConnection)
{
var builder = new SqlCeConnectionStringBuilder(connection.ConnectionString);
return(string.Format("DataSource: {0}", builder.DataSource));
}
if (connection is MySqlConnection)
{
var builder = new MySqlConnectionStringBuilder(connection.ConnectionString);
return(string.Format("Server: {0}, Database: {1}", builder.Server, builder.Database));
}
}
catch (Exception ex)
{
LogHelper.WarnWithException(typeof(DbConnectionExtensions),
"Could not resolve connection string parameters", ex);
return("(Could not resolve)");
}
throw new ArgumentException(string.Format("The connection type {0} is not supported", connection.GetType()));
}
public void SetUp()
{
if (!IntegrationTestOptions.SqlServerCe.IsEnabled)
{
Assert.Ignore();
}
if (!HostUtilities.ProbeSqlServerCeBehavior())
{
Assert.Ignore("SQL Server CE binaries not found");
}
_tempDataDirectory = Path.Combine(Path.GetTempPath(), Guid.NewGuid().ToString("N"));
Directory.CreateDirectory(_tempDataDirectory);
AppDomain.CurrentDomain.SetData("DataDirectory", _tempDataDirectory);
var csb = new SqlCeConnectionStringBuilder(IntegrationTestOptions.SqlServerCe.ConnectionString);
DatabaseFilename = HostUtilities.ReplaceDataDirectory(csb.DataSource);
RecreateDatabase();
Connection = new SqlCeConnection(IntegrationTestOptions.SqlServerCe.ConnectionString);
Processor = new SqlServerCeProcessor(Connection, new SqlServerCeGenerator(), new TextWriterAnnouncer(TestContext.Out), new ProcessorOptions(), new SqlServerCeDbFactory());
Connection.Open();
Processor.BeginTransaction();
}
private static string ConvertToConnectionString(IConnectionString connectionString)
{
var cnStringBuilder = new SqlCeConnectionStringBuilder(connectionString.PlainString);
cnStringBuilder.Enlist = false;
return connectionString.ReplacePlain(cnStringBuilder.ConnectionString).PlainString;
}
public override void ChangeDatabase(string value)
{
if (isDisposed)
{
throw new ObjectDisposedException("SqlCeConnection");
}
CheckStateOpen("ChangeDatabase");
if (value == null || value.Trim().Length == 0)
{
throw new ArgumentException(Res.GetString("ADP_EmptyDatabaseName"));
}
string connectionString = ConnectionString;
try {
Close(true);
SqlCeConnectionStringBuilder sqlCeConnectionStringBuilder = new SqlCeConnectionStringBuilder(ConnectionString)
{
DataSource = value
};
ConnectionString = sqlCeConnectionStringBuilder.ConnectionString;
Open(true);
} catch (Exception) {
ConnectionString = connectionString;
throw;
}
}
public SqlServerCeStorageFixture()
{
Skip.IfNoConfig(nameof(TestConfig.Current.SQLServerCeConnectionString), TestConfig.Current.SQLServerCeConnectionString);
var connString = TestConfig.Current.SQLServerCeConnectionString;
var csb = new SqlCeConnectionStringBuilder(connString);
var filename = csb.DataSource;
Storage = new SqlServerCeStorage(
connString,
"MPTest" + TestId,
"MPTimingsTest" + TestId,
"MPClientTimingsTest" + TestId);
try
{
try
{
File.Delete(filename);
}
catch { /* expected */ }
var engine = new SqlCeEngine(connString);
engine.CreateDatabase();
Storage.CreateSchema();
}
catch (Exception e)
{
ShouldSkip = true;
SkipReason = e.Message;
}
}
public static void CreateDatabaseIfRequired(string connection, string applicationName)
{
string dataDirectory = AppDomain.CurrentDomain.GetData("DataDirectory") as string;
string physConnectionString = connection.Replace("|DataDirectory|" + System.IO.Path.DirectorySeparatorChar, dataDirectory + System.IO.Path.DirectorySeparatorChar);
physConnectionString = physConnectionString.Replace("|DataDirectory|", dataDirectory + System.IO.Path.DirectorySeparatorChar);
string sdfPath = string.Empty;
lock (_lock)
{
SqlCeConnectionStringBuilder builder = new SqlCeConnectionStringBuilder();
builder.ConnectionString = physConnectionString;
sdfPath = builder.DataSource;
if (string.IsNullOrWhiteSpace(sdfPath))
return;
if (!System.IO.File.Exists(sdfPath))
{
//OK, try to create the database file
using (var engine = new SqlCeEngine(connection))
{
engine.CreateDatabase();
}
}
ValidateDatabase(connection, applicationName);
}
}
public bool TryConnect(string connectionString, out string errorMessage)
{
try
{
var csb = new SqlCeConnectionStringBuilder(connectionString);
var replaceDataDirectory = ReplaceDataDirectory(csb.DataSource);
if (!File.Exists(replaceDataDirectory))
{
var directoryName = Path.GetDirectoryName(replaceDataDirectory);
if (directoryName != null && !Directory.Exists(directoryName))
Directory.CreateDirectory(directoryName);
new SqlCeEngine(connectionString).CreateDatabase();
}
errorMessage = "";
using (var connection = new SqlCeConnection(csb.ConnectionString))
{
connection.Open();
new SqlCeCommand("select 1", connection).ExecuteScalar();
}
return true;
}
catch (Exception e)
{
errorMessage = e.Message;
return false;
}
}
protected override DbConnection GetConnection()
{
SqlCeConnectionStringBuilder sqlCeConnectionStringBuilder = new SqlCeConnectionStringBuilder();
sqlCeConnectionStringBuilder.DataSource = "BrandNewDatabase.sdf";
return(new SqlCeConnection(sqlCeConnectionStringBuilder.ToString()));
}
static void Main()
{
try
{
///<remarks>
/// If you're using Visual Studio 2010 SP1 the sample database
/// included with this solution should also be available at:
/// %PROGRAMFILES%\Microsoft SQL Server Compact Edition\v4.0\Samples
///</remarks>
var res = Path.Combine(Path.GetDirectoryName(Assembly.GetExecutingAssembly().Location), "res");
var con = new SqlCeConnectionStringBuilder();
con.DataSource = Path.Combine(res, "Northwind.sdf");
var msg = "Retrieving the amount of customers and orders per city in the USA:";
using (var data = new NorthwindDataContext(con.ConnectionString))
{
// TODO: Create a complex LINQ expression with data from all tables.
var query = from customer in data.Customers
join order in data.Orders on customer.CustomerID equals order.CustomerID
where customer.Country == "USA"
group customer by customer.City into customerPerCity
select new
{
City = customerPerCity.Key,
CustomerCount = customerPerCity.Count(),
OrderCount = customerPerCity.Sum(c => c.Orders.Count)
};
if (query != null)
{
var txt = new StringBuilder();
txt.AppendLine(msg);
txt.Append(Environment.NewLine);
txt.AppendLine(String.Format("{0,-54} {1,11} {2,11}", "-".Repeat(54), "-".Repeat(11), "-".Repeat(11)));
txt.AppendLine(String.Format("{0,-54} {1,11} {2,11}", "City", "Customers", "Orders"));
txt.AppendLine(String.Format("{0,-54} {1,11} {2,11}", "-".Repeat(54), "-".Repeat(11), "-".Repeat(11)));
foreach (var item in query)
{
txt.AppendLine(String.Format("{0,-54} {1,11} {2,11}", item.City, item.CustomerCount, item.OrderCount));
}
txt.AppendLine(String.Format("{0,-54} {1,11} {2,11}", "-".Repeat(54), "-".Repeat(11), "-".Repeat(11)));
Console.Write(txt.ToString());
}
}
}
catch (Exception err)
{
Console.Write(Environment.NewLine);
Console.WriteLine(String.Format("Exception: {0}", err.Message));
}
finally
{
Console.Write(Environment.NewLine);
Console.Write("Press any key to continue . . .");
Console.ReadKey(true);
}
}
public static IEnumerable<string> GetFileDatabaseTables(string databaseFile, string password = null)
{
if (string.IsNullOrEmpty(databaseFile) || !File.Exists(databaseFile))
{
throw new Exception("Database not exists");
}
IList<string> tableNames = new List<string>();
SqlCeConnectionStringBuilder connectionString = new SqlCeConnectionStringBuilder();
connectionString.DataSource = databaseFile;
if (!string.IsNullOrEmpty(password))
connectionString.Password = password;
using (SqlCeConnection sqlCon = new SqlCeConnection(connectionString.ToString()))
{
string sqlStmt = "SELECT table_name FROM INFORMATION_SCHEMA.TABLES";
using (SqlCeCommand cmd = new SqlCeCommand(commandText: sqlStmt, connection: sqlCon))
{
sqlCon.Open();
var reader = cmd.ExecuteReader();
while (reader.Read())
{
tableNames.Add((string)reader["table_name"]);
}
sqlCon.Close();
}
}
return tableNames;
}
private void InitializeDbNameAndServerConnectionString()
{
var cnStringBuilder = new SqlCeConnectionStringBuilder(ConnectionString.PlainString);
_filePath = cnStringBuilder.DataSource;
const string dataDirectorySwitch = "|DataDirectory|";
if(_filePath.StartsWith(dataDirectorySwitch, StringComparison.InvariantCultureIgnoreCase))
{
_filePath = _filePath.Substring(dataDirectorySwitch.Length);
if (HttpContext.Current != null)
_filePath = Path.Combine(HttpContext.Current.Server.MapPath("App_Data"), _filePath);
}
_dbName = FilePath.Contains(Path.DirectorySeparatorChar.ToString())
? Path.GetFileNameWithoutExtension(FilePath)
: FilePath;
if (string.IsNullOrWhiteSpace(_dbName))
throw new SisoDbException(ExceptionMessages.ConnectionInfo_MissingName);
_serverPath = Path.GetDirectoryName(_filePath);
cnStringBuilder.DataSource = _serverPath;
_serverConnectionString = ConnectionString.ReplacePlain(cnStringBuilder.ConnectionString);
}
/// <summary>
/// If the database file does not exist, create it and run the install script to set
/// up the tables and default data.
/// </summary>
internal static void CreateDatabaseIfNecessary()
{
lock (_sharedLock)
{
SqlCeConnectionStringBuilder builder = new SqlCeConnectionStringBuilder();
builder.ConnectionString = ConnectionString;
string sdfPath = ReplaceDataDirectory(builder.DataSource);
if (string.IsNullOrWhiteSpace(sdfPath))
{
return;
}
if (!File.Exists(sdfPath))
{
// Database file doesn't exist, so create it.
using (var engine = new SqlCeEngine(ConnectionString))
{
engine.CreateDatabase();
}
Util.ExecuteSqlUpgradeScript(GalleryDataSchemaUpgradeScript.SqlCeInstallScript);
}
}
}
protected override void RunCore(IDbAccesser db)
{
var sqlCeCon = db.Connection as SqlCeConnection;
if (sqlCeCon == null)
{
throw new InvalidOperationException("需要使用 SqlCe 的连接，才能正确创建数据库。");
}
//保存目录存在。
var csb = new SqlCeConnectionStringBuilder(db.Connection.ConnectionString);
var path = CommonUtils.ReplaceDataDirectory(csb.DataSource);
var dir = Path.GetDirectoryName(path);
if (!string.IsNullOrWhiteSpace(dir))
{
Directory.CreateDirectory(dir);
}
else
{
Directory.CreateDirectory("\\");
}
//调用引擎创建数据库
var engine = new SqlCeEngine(db.Connection.ConnectionString);
engine.CreateDatabase();
}
public static void CreateDatabaseIfRequired(string connection, string applicationName)
{
lock (_lock)
{
SqlCeConnectionStringBuilder builder = new SqlCeConnectionStringBuilder();
builder.ConnectionString = connection;
string sdfPath = ReplaceDataDirectory(builder.DataSource);
if (string.IsNullOrWhiteSpace(sdfPath))
{
return;
}
if (!System.IO.File.Exists(sdfPath))
{
//OK, try to create the database file
using (var engine = new SqlCeEngine(connection))
{
engine.CreateDatabase();
}
Util.ExecuteSqlUpgradeScript(GalleryDataSchemaUpgradeScript.SqlCeInstallScript);
}
ValidateDatabase(connection, applicationName);
}
}
protected override IConnectionString OnFormatConnectionString(IConnectionString connectionString)
{
var cnString = base.OnFormatConnectionString(connectionString);
var cnStringBuilder = new SqlCeConnectionStringBuilder(cnString.PlainString) { Enlist = false };
return connectionString.ReplacePlain(cnStringBuilder.ConnectionString);
}
public void build_sqlce_connection_with_password()
{
// Arrange
SqlCeConnectionStringBuilder builder = new SqlCeConnectionStringBuilder();
string databaseName = "BlogApp";
string password = "******";
string dbName = databaseName;
if (databaseName.IndexOf(".sdf") == -1)
{
dbName = databaseName + ".sdf";
}
builder.DataSource = string.Format(@"|DataDirectory|\{0}", dbName);
if (!string.IsNullOrEmpty(password))
{
builder.Password = password;
}
// Act
var actual = builder.ToString();
var expected = @"Data Source=|DataDirectory|\BlogApp.sdf;Password=myPassword";
// Assert
Assert.AreEqual(expected, actual);
}
public static string GetConnStringExSecurityInfo(this IDbConnection connection)
{
try
{
switch (connection)
{
case SqlConnection _:
{
var builder = new SqlConnectionStringBuilder(connection.ConnectionString);
return($"DataSource: {builder.DataSource}, InitialCatalog: {builder.InitialCatalog}");
}
case SqlCeConnection _:
{
var builder = new SqlCeConnectionStringBuilder(connection.ConnectionString);
return($"DataSource: {builder.DataSource}");
}
case MySqlConnection _:
{
var builder = new MySqlConnectionStringBuilder(connection.ConnectionString);
return($"Server: {builder.Server}, Database: {builder.Database}");
}
}
}
catch (Exception ex)
{
Current.Logger.Warn(typeof(DbConnectionExtensions), ex, "Could not resolve connection string parameters");
return("(Could not resolve)");
}
throw new ArgumentException($"The connection type {connection.GetType()} is not supported");
}
public static void CreateDatabaseIfRequired(string connection, string applicationName)
{
lock (_lock)
{
SqlCeConnectionStringBuilder builder = new SqlCeConnectionStringBuilder();
builder.ConnectionString = connection;
string sdfPath = ReplaceDataDirectory(builder.DataSource);
if (string.IsNullOrWhiteSpace(sdfPath))
return;
if (!System.IO.File.Exists(sdfPath))
{
//OK, try to create the database file
using (var engine = new SqlCeEngine(connection))
{
engine.CreateDatabase();
}
Util.ExecuteSqlUpgradeScript(GalleryDataSchemaUpgradeScript.SqlCeInstallScript);
}
ValidateDatabase(connection, applicationName);
}
}
public SqlCeSimpleConnectionStringBuilder([NotNull] SqlCeConnectionStringBuilder connectionStringBuilder)
{
if (connectionStringBuilder == null)
{
throw new ArgumentNullException("connectionStringBuilder");
}
_connectionStringBuilder = connectionStringBuilder;
}
protected override string OnFormatServerConnectionString(string connectionString)
{
var cnStringBuilder = new SqlCeConnectionStringBuilder(connectionString)
{
Enlist = false
};
return(cnStringBuilder.ConnectionString);
}
private static string connectionString(FileInfo dbFile)
{
var builder = new SqlCeConnectionStringBuilder
{
DataSource = dbFile.Name
};
return(builder.ConnectionString);
}
static void Main()
{
var res = Path.Combine(Path.GetDirectoryName(Assembly.GetExecutingAssembly().Location), "res");
var str = new SqlCeConnectionStringBuilder();
str.DataSource = Path.Combine(res, "Catalog.sdf");
// INFO: Performing Read for simplicity but could be any CRUD operation.
var sql = "SELECT * FROM Books";
try
{
using (var con = new SqlCeConnection(str.ConnectionString))
{
con.Open();
using (var cmd = new SqlCeCommand(sql, con))
{
// TODO: Implement better handling of SqlCeDataReader.
using (SqlCeDataReader data = cmd.ExecuteReader())
{
/// <remarks>
/// SQL Server Compact Edition does not support
/// calls to HasRows property if the underlying
/// cursor is not scrollable.
/// </remarks>
var txt = new StringBuilder();
txt.AppendLine(String.Format("{0,-37} {1,-23} {2,10} {3,5}", "-".Repeat(37), "-".Repeat(23), "-".Repeat(10), "-".Repeat(5)));
txt.AppendLine(String.Format("{0,-37} {1,-23} {2,-10} {3,-5}", "Title", "Author", "Published", "Pages"));
txt.AppendLine(String.Format("{0,-37} {1,-23} {2,10} {3,5}", "-".Repeat(37), "-".Repeat(23), "-".Repeat(10), "-".Repeat(5)));
while (data.Read())
{
txt.AppendFormat("{0,-37} {1,-23} {2,10} {3,5}", data.GetString(1), data.GetString(2), data.GetDateTime(4).ToShortDateString(), data.GetValue(5));
txt.Append(Environment.NewLine);
}
txt.AppendLine(String.Format("{0,-37} {1,-23} {2,10} {3,5}", "-".Repeat(37), "-".Repeat(23), "-".Repeat(10), "-".Repeat(5)));
Console.Write(txt.ToString());
data.Close();
}
}
con.Close();
}
}
catch (Exception err)
{
Console.Write(Environment.NewLine);
Console.WriteLine(String.Format("Exception: {0}", err.Message));
}
finally
{
Console.Write(Environment.NewLine);
Console.Write("Press any key to continue . . .");
Console.ReadKey(true);
}
}
public static string GetConnectionString(string baseDirectoryPath, string dbFileName)
{
var ceConnectionstringBulder = new SqlCeConnectionStringBuilder
{
DataSource = GetDbFilePath(dbFileName),
PersistSecurityInfo = false,
};
return ceConnectionstringBulder.ConnectionString;
}
private void SetConnectedTitle(string connectionString) {
if (_connection is SqlConnection) {
var bldr = new SqlConnectionStringBuilder(connectionString);
Title = String.Format("Connected - {0} on {1}", bldr.InitialCatalog, bldr.DataSource);
}
else {
var bldr = new SqlCeConnectionStringBuilder(connectionString);
Title = String.Format("Connected - SQLCE {0}", bldr.DataSource);
}
}
public static SqlCeConnection GetConnection()
{
var builder = new SqlCeConnectionStringBuilder();
builder.DataSource = Path.Combine(Loader.EngineDirectory, "Commando.sdf");
var conn = new SqlCeConnection(builder.ConnectionString);
conn.Open();
return(conn);
}
public void CreateDatabaseIfNoExsits(DbContext context)
{
var stringbuilder = new SqlCeConnectionStringBuilder(context.Database.Connection.ConnectionString);
var database = stringbuilder.DataSource;
if (!File.Exists(database))
{
new SqlCeEngine(stringbuilder.ToString()).CreateDatabase();
}
}
public static string GetSqlCeConnectionString()
{
// ReSharper disable once CollectionNeverUpdated.Local
SqlCeConnectionStringBuilder sqlCeConnectionStringBuilder = new SqlCeConnectionStringBuilder();
//oleDbConnectionStringBuilder.Provider = "Microsoft.Jet.OLEDB.4.0";
//oleDbConnectionStringBuilder.DataSource = @".\Empty.mdb";
sqlCeConnectionStringBuilder.DataSource = GetSqlCeDatabaseFileName();
sqlCeConnectionStringBuilder.CaseSensitive = true;
return(sqlCeConnectionStringBuilder.ToString());
}
public static string PathFromConnectionString([NotNull] string connectionString)
{
Check.NotNull(connectionString, nameof(connectionString));
#if SQLCE35
var conn = new SqlCeConnection(GetFullConnectionString(connectionString));
return(conn.Database);
#else
var sb = new SqlCeConnectionStringBuilder(GetFullConnectionString(connectionString));
return(sb.DataSource);
#endif
}
public override System.Data.Common.DbConnection GetDBConn()
{
var cb = new SqlCeConnectionStringBuilder();
cb.DataSource = _server;
if (!string.IsNullOrEmpty(_psw))
{
cb.Password = _psw;
}
return(new SqlCeConnection(cb.ConnectionString));
}
private void DbMaintenance()
{
ConnectionInfo conInfo;
var constrsettings = ConfigurationManager.ConnectionStrings[Constants.serverConStrName];
if (constrsettings != null)
{
conInfo = new ConnectionInfo(constrsettings.ConnectionString.ExpandVariables(), constrsettings.ProviderName);
}
// create sdf db
if (conInfo.ProviderName == Constants.SqlCeProvider)
{
try
{
SqlHelper.CreateSqlCeByConStr(conInfo.ConnectionString);
}
catch
{
}
}
NHibernateHelper.Default.Init(conInfo, Side.Server);
if (NHibernateHelper.Default.InMemory)
{
demoMode = true;
return;
}
// backup
#if !DEBUG
if (conInfo.ProviderName == Constants.SqlCeProvider)
{
var sdfPath = new SqlCeConnectionStringBuilder(conInfo.ConnectionString).DataSource;
Diagnosis.Common.Services.BackupService.Backup(sdfPath, Constants.BackupDir, 5, 7);
}
#endif
bool?migrateUp = true;
if (migrateUp.HasValue)
{
if (migrateUp.Value)
{
new Migrator(conInfo, Side.Server, Constants.BackupDir).MigrateToLatest();
}
else
{
new Migrator(conInfo, Side.Server, Constants.BackupDir).Rollback();
}
}
}
public override void DropAndRecreate()
{
var masterConnectionString = ConfigurationManager.ConnectionStrings[MasterName].ConnectionString;
var connectionStringBuilder = new SqlCeConnectionStringBuilder
{
ConnectionString = masterConnectionString
};
string fileName = connectionStringBuilder.DataSource;
if (File.Exists(fileName)) File.Delete(fileName);
var en = new SqlCeEngine(masterConnectionString);
en.CreateDatabase();
}
public void DapperDbMultiInstanceTest()
{
SqlCeConnectionStringBuilder builder = new SqlCeConnectionStringBuilder()
{
DataSource = Path.Combine(Path.GetDirectoryName(Assembly.GetExecutingAssembly().Location), "DapperTests.sdf")
};
for (int i = 0; i < 100; i++)
{
DapperAccessLayer.Create <IDapperTestsDb>(() => new SqlCeConnection(builder.ToString()));
}
}
void EnsureDatabase()
{
var builder =
new SqlCeConnectionStringBuilder(
sql_connection_provider_.ConnectionString);
if (!File.Exists(builder.DataSource))
{
new SqlCeEngine(sql_connection_provider_.ConnectionString)
.CreateDatabase();
}
}
/// <summary>
/// Создаёт строку подключения формата Entity Framework.
/// </summary>
/// <param name="path">Путь к базе данных SQL Server CE</param>
/// <returns>Строка подключения</returns>
public static string BuidConStr(string path)
{
var ecsBuilder = new EntityConnectionStringBuilder(ConfigurationManager.ConnectionStrings["GamesEntities"].ConnectionString);
var sqlCeCsBuilder = new SqlCeConnectionStringBuilder(ecsBuilder.ProviderConnectionString)
{
DataSource = path,
MaxDatabaseSize = 4091
};
ecsBuilder.ProviderConnectionString = sqlCeCsBuilder.ToString();
return(ecsBuilder.ToString()); //Возвращаем готовую строку подключения
}
private void SetConnectedTitle(string connectionString)
{
if (_connection is SqlConnection)
{
var bldr = new SqlConnectionStringBuilder(connectionString);
Title = String.Format("Connected - {0} on {1}", bldr.InitialCatalog, bldr.DataSource);
}
else
{
var bldr = new SqlCeConnectionStringBuilder(connectionString);
Title = String.Format("Connected - SQLCE {0}", bldr.DataSource);
}
}
/// <summary>
/// 返回一个新的 SqlCe 连接字符串
/// </summary>
/// <param name="context"></param>
/// <returns></returns>
protected static DbContext ReplaceSqlCeConnection(DbContext context)
{
if (context.Database.Connection is SqlCeConnection)
{
var builder = new SqlCeConnectionStringBuilder(context.Database.Connection.ConnectionString);
if (!string.IsNullOrWhiteSpace(builder.DataSource))
{
builder.DataSource = ReplaceDataDirectory(builder.DataSource);
return(new DbContext(builder.ConnectionString));
}
}
return(context);
}
public void SetUp()
{
_tempDataDirectory = Path.Combine(Path.GetTempPath(), Guid.NewGuid().ToString("N"));
Directory.CreateDirectory(_tempDataDirectory);
AppDomain.CurrentDomain.SetData("DataDirectory", _tempDataDirectory);
var csb = new SqlCeConnectionStringBuilder(IntegrationTestOptions.SqlServerCe.ConnectionString);
DatabaseFilename = HostUtilities.ReplaceDataDirectory(csb.DataSource);
RecreateDatabase();
ServiceScope = ServiceProvider.CreateScope();
Processor = ServiceScope.ServiceProvider.GetRequiredService <SqlServerCeProcessor>();
}
private static DbContext ReplaceSqlCeConnection(DbContext context)
{
if (context.Database.Connection is SqlCeConnection)
{
SqlCeConnectionStringBuilder builder =
new SqlCeConnectionStringBuilder(context.Database.Connection.ConnectionString);
if (!String.IsNullOrWhiteSpace(builder.DataSource))
{
builder.DataSource = ReplaceDataDirectory(builder.DataSource);
return new DbContext(builder.ConnectionString);
}
}
return context;
}
private static void MainInternal ()
{
string connStr = new SqlCeConnectionStringBuilder { DataSource = DbFileName, FileMode = "Shared Read" }.ConnectionString;
Connect(connStr);
//Trace.Listeners.Add(new ConsoleTraceListener());
SimpleDataTraceSources.TraceSource.Listeners.Add(new ConsoleTraceListener());
var db = Database.OpenConnection(connStr);
db.Users.Insert(Name: "Athari", Avatar: "athari.jpg");
Console.WriteLine(db.Users.FindByName("Athari").Avatar);
db.IDoNotExist.Insert(Id: 0);
}
public static void Main()
{
try
{
var directory = Path.Combine(Path.GetDirectoryName(Assembly.GetExecutingAssembly().Location), "res");
var connection = new SqlCeConnectionStringBuilder();
connection.DataSource = Path.Combine(directory, "Northwind.sdf");
using (var data = new NorthwindDataContext(connection.ConnectionString))
{
var query = from customer in data.Customers
join order in data.Orders
on customer.CustomerID equals order.CustomerID
where customer.Country == "USA"
group customer by customer.City into customerPerCity
orderby customerPerCity.Count() descending
select new
{
City = customerPerCity.Key,
CustomerCount = customerPerCity.Count(),
OrdersCount = customerPerCity.Sum(c => c.Orders.Count)
};
if (query != null)
{
var builder = new StringBuilder();
builder.AppendConsoleFormat();
foreach (var item in query)
{
builder.AppendLine(string.Format("{0,-54} {1,11} {2,11}", item.City, item.CustomerCount, item.OrdersCount));
}
Console.Write(builder.ToString());
}
}
}
catch (Exception error)
{
Console.Write(Environment.NewLine);
Console.WriteLine(string.Format("Exception: {0}", error.Message));
}
finally
{
Console.Write(Environment.NewLine);
Console.Write("Press any key to continue . . .");
Console.ReadKey(true);
}
}
static SqlCeConnection GetSqlCeConnection()
{
string connectionString = ConfigurationManager.ConnectionStrings["test"].ConnectionString;
SqlCeConnectionStringBuilder connectionStringBuilder = new SqlCeConnectionStringBuilder(connectionString);
connectionStringBuilder.DataSource = Path.GetFullPath(connectionStringBuilder.DataSource);
Directory.CreateDirectory(Path.GetDirectoryName(connectionStringBuilder.DataSource));
SqlCeConnection connection = new SqlCeConnection(connectionStringBuilder.ConnectionString);
if (File.Exists(connectionStringBuilder.DataSource)) {
File.Delete(connectionStringBuilder.DataSource);
}
CreateDatabase(connectionStringBuilder.ConnectionString);
return connection;
}
protected string ExtractFilePath(string connectionString)
{
var cnStringBuilder = new SqlCeConnectionStringBuilder(connectionString);
var filePath = cnStringBuilder.DataSource;
const string dataDirectorySwitch = "|DataDirectory|";
if (filePath.StartsWith(dataDirectorySwitch, Sys.StringComparision))
{
filePath = filePath.Substring(dataDirectorySwitch.Length);
if (HttpContext.Current != null)
filePath = Path.Combine(HttpContext.Current.Server.MapPath("App_Data"), filePath);
}
return filePath;
}
private static string ExtractFilePath(IConnectionString connectionString)
{
var cnStringBuilder = new SqlCeConnectionStringBuilder(connectionString.PlainString);
var filePath = cnStringBuilder.DataSource;
const string dataDirectorySwitch = "|DataDirectory|";
if (filePath.StartsWith(dataDirectorySwitch, StringComparison.OrdinalIgnoreCase))
{
filePath = filePath.Substring(dataDirectorySwitch.Length);
if (HttpContext.Current != null)
filePath = Path.Combine(HttpContext.Current.Server.MapPath("App_Data"), filePath);
}
return filePath;
}
public override bool TryGetProvider(string connectionString, out IConnectionProvider provider, out Exception exception)
{
try
{
var _ = new SqlCeConnectionStringBuilder(connectionString);
provider = new SqlCe40ConnectionProvider();
provider.SetConnectionString(connectionString);
exception = null;
return true;
}
catch (Exception ex)
{
exception = ex;
provider = null;
return false;
}
}
private SqlCeConnectionStringBuilder ConnectionStringBuilder(string databaseName, string password)
{
SqlCeConnectionStringBuilder builder = new SqlCeConnectionStringBuilder();
string dbName = databaseName;
if (databaseName.IndexOf(".sdf") == -1)
{
dbName = databaseName + ".sdf";
}
builder.PersistSecurityInfo = false;
builder.DataSource = string.Format(@"|DataDirectory|\{0}", dbName);
if (!string.IsNullOrEmpty(password))
{
builder.Password = password;
}
return builder;
}
public void build_sqlce_connection_with_filename_extension()
{
// Arrange
SqlCeConnectionStringBuilder builder = new SqlCeConnectionStringBuilder();
string databaseName = "BlogApp.sdf";
string dbName = databaseName;
if (databaseName.IndexOf(".sdf") == -1)
{
dbName = databaseName + ".sdf";
}
builder.DataSource = string.Format(@"|DataDirectory|\{0}", dbName);
// Act
var actual = builder.ToString();
var expected = @"Data Source=|DataDirectory|\BlogApp.sdf";
// Assert
Assert.AreEqual(expected, actual);
}
protected override void RunCore(IDbAccesser db)
{
var sqlCeCon = db.Connection as SqlCeConnection;
if (sqlCeCon == null) throw new InvalidOperationException("需要使用 SqlCe 的连接，才能正确创建数据库。");
//保存目录存在。
var csb = new SqlCeConnectionStringBuilder(db.Connection.ConnectionString);
var path = CommonUtils.ReplaceDataDirectory(csb.DataSource);
var dir = Path.GetDirectoryName(path);
if (!string.IsNullOrWhiteSpace(dir))
{
Directory.CreateDirectory(dir);
}
else
{
Directory.CreateDirectory("\\");
}
//调用引擎创建数据库
var engine = new SqlCeEngine(db.Connection.ConnectionString);
engine.CreateDatabase();
}
public string PathFromConnectionString(string connectionString)
{
SqlCeConnectionStringBuilder sb = new SqlCeConnectionStringBuilder(connectionString);
return sb.DataSource;
}
protected override string OnFormatServerConnectionString(string connectionString)
{
var cnStringBuilder = new SqlCeConnectionStringBuilder(connectionString) { Enlist = false };
return cnStringBuilder.ConnectionString;
}
private void CreateDb()
{
//保存目录存在。
var csb = new SqlCeConnectionStringBuilder(_db.Connection.ConnectionString);
var path = CommonUtils.ReplaceDataDirectory(csb.DataSource);
var dir = Path.GetDirectoryName(path);
if (!string.IsNullOrWhiteSpace(dir))
{
Directory.CreateDirectory(dir);
}
else
{
Directory.CreateDirectory("\\");
}
_db.RawAccesser.ExecuteText(@"
CREATE TABLE [Entities] (
[Region] nvarchar(100) NOT NULL,
[Key] nvarchar(100) NOT NULL,
[ChangeChecker] varbinary(8000) NULL,
[Time] datetime NOT NULL,
[ValuePath] nvarchar(1000) NULL,
[Value] varbinary(8000) NULL
);
");
_db.RawAccesser.ExecuteText(@"
ALTER TABLE [Entities] ADD CONSTRAINT [PK__Entities__000000000000000D] PRIMARY KEY ([Region],[Key]);");
}
private bool IsDbExist(string connectionString)
{
var ceConnectionStringBuilder = new SqlCeConnectionStringBuilder(connectionString);
var dbFilePath = ceConnectionStringBuilder.DataSource;
#warning хак - используется |DataDirectory| для обхода ограничения на длину строки Data Source
if (dbFilePath.Contains("|DataDirectory|"))
dbFilePath = dbFilePath.Replace("|DataDirectory|\\", AppDomain.CurrentDomain.GetData("DataDirectory") as string);
return File.Exists(dbFilePath);
}
/// <summary>
/// Configura a string de conexão de acordo com o banco de dados definido no arquivo de configuração RegLog.config.
/// </summary>
private static void ConfigConnection()
{
var path = Path.GetDirectoryName(Assembly.GetAssembly(typeof(LogModel)).Location);
var xmlConfig = new XmlTextReader(path + "\\RegLog.config");
while (xmlConfig.Read())
{
if (xmlConfig.NodeType != XmlNodeType.Element) continue;
var elementos = new string[] {"host","port", "database", "databasename", "connectiontimeout","connectionlifetime", "user", "password"};
if (!elementos.Contains(xmlConfig.Name)) continue;
_host = xmlConfig.Name.ToLower().Equals("host") ? xmlConfig.ReadString() : _host;
_port = xmlConfig.Name.ToLower().Equals("port") && !string.IsNullOrWhiteSpace(xmlConfig.ReadString()) ? Convert.ToInt32(xmlConfig.ReadString()) : _port;
_database = xmlConfig.Name.ToLower().Equals("database") ? Convert.ToInt32(xmlConfig.ReadString()) : _database;
_dbName = xmlConfig.Name.ToLower().Equals("databasename") ? xmlConfig.ReadString() : _dbName;
_timeOut = xmlConfig.Name.ToLower().Equals("connectiontimeout") ? Convert.ToInt32(xmlConfig.ReadString()) : _timeOut;
_lifeTime = xmlConfig.Name.ToLower().Equals("connectionlifetime") ? Convert.ToInt32(xmlConfig.ReadString()) : _lifeTime;
_user = xmlConfig.Name.ToLower().Equals("user") ? xmlConfig.ReadString() : _user;
_password = xmlConfig.Name.ToLower().Equals("password") ? xmlConfig.ReadString() : _password;
}
switch (_database)
{
case 0: //SQLITE
var sqlite = new SQLiteConnection(Path.Combine(_host, _dbName));
_connection = sqlite.ConnectionString;
break;
case 1: //MSSQL
case 11:
var mssql = new System.Data.SqlClient.SqlConnectionStringBuilder
{
ConnectTimeout = _timeOut,
DataSource = _host,
Enlist = true,
InitialCatalog = _dbName,
UserID = _user,
Password = _password,
IntegratedSecurity = false,
LoadBalanceTimeout = _lifeTime,
};
_connection = mssql.ConnectionString;
break;
case 19: //MSSQL CE
var sqlce = new SqlCeConnectionStringBuilder
{
DataSource = Path.Combine(_host, _dbName),
Enlist = true,
Password = _password,
FileAccessRetryTimeout = _timeOut,
};
_connection = sqlce.ConnectionString;
break;
case 2: //MYSQL
var mysql = new MySql.Data.MySqlClient.MySqlConnectionStringBuilder
{
Database = _dbName,
Server = _host,
Password = _password,
UserID = _user,
ConnectionLifeTime = (uint)_lifeTime,
ConnectionTimeout = (uint)_timeOut,
IntegratedSecurity = false,
AutoEnlist = true
};
if(_port > 0)
mysql.Port = (uint)_port;
_connection = mysql.ConnectionString;
break;
case 3: //POSTGRESQL
var pgsql = new Npgsql.NpgsqlConnectionStringBuilder
{
Database = _dbName,
Host = _host,
Password = _password,
UserName = _user,
ConnectionLifeTime = _lifeTime,
Timeout = _timeOut,
IntegratedSecurity = false,
Enlist = true
};
if (_port > 0)
pgsql.Port = _port;
_connection = pgsql.ConnectionString;
break;
case 4: //DB2
//var db2 =
// DB2Configuration.Standard.ConnectionString(
// c => c.Database(_dbName).Server(_host).Username(_user).Password(_password));
break;
case 5: //FIREBIRD
var firebird = new FirebirdSql.Data.FirebirdClient.FbConnectionStringBuilder
{
Database = _dbName,
Password = _password,
ConnectionLifeTime = _lifeTime,
Enlist = true,
UserID = _user,
DataSource = _host,
ConnectionTimeout = _timeOut
};
if (_port > 0)
firebird.Port = _port;
_connection = firebird.ConnectionString;
break;
case 6: //ORACLE 9
case 61: //ORACLE 10
var oracle = new Oracle.DataAccess.Client.OracleConnectionStringBuilder
{
Password = _password,
ConnectionLifeTime = _lifeTime,
Enlist = "true",
UserID = _user,
DataSource = _dbName,
ConnectionTimeout = _timeOut
};
oracle["Server"] = _host;
_connection = oracle.ConnectionString;
break;
default:
_connection = "";
break;
}
}
