public static void CreateCompanyTable(OrmLiteConnectionFactory factory)
{
using (IDbConnection db = factory.OpenDbConnection())
{
db.CreateTableIfNotExists<Company>();
}
}
public void ReFreshProList()
{
List<RegProListInfo> lits;
_dbFactory = new OrmLiteConnectionFactory(MmbpriceDbConnectionString, SqlServerDialect.Provider);
try
{
using (var db = _dbFactory.OpenDbConnection())
{
lits =
db.SqlList<RegProListInfo>(
"select 0 as id,siteid,urladdress as ProListUrl,listsReg,singleReg,UrlReg,PriceReg,TitleReg,PicReg,isSellReg,CommentCountReg,commentUrlReg,idReg as SkuReg,shopIDReg,maxpageReg,pageStart,pageStep,'' as Remark,getdate() as UpdateTime from JD_Reg_SiteList where [type]=0");
}
_dbFactory = new OrmLiteConnectionFactory(ConnectionString, SqliteDialect.Provider);
using (var db = _dbFactory.OpenDbConnection())
{
db.CreateTable<RegProListInfo>(true);
}
AddRegProList(lits);
}
catch (Exception ex)
{
LogServer.WriteLog(ex, "DBError");
throw;
}
}
public void AddSiteProInfo(IEnumerable<SiteProInfo> siteList)
{
if (siteList == null) throw new ArgumentNullException("siteList");
_dbFactory = new OrmLiteConnectionFactory(ConnectionString, SqliteDialect.Provider);
using (var db = _dbFactory.OpenDbConnection())
{
try
{
db.InsertAll(siteList);
}
catch (Exception ex)
{
AddSitePro(siteList);
LogServer.WriteLog(ex, "DBError");
}
}
//var mmbdbFactory = new OrmLiteConnectionFactory(mmbpriceDBConnectionString, SqlServerDialect.Provider);
//using (var db = mmbdbFactory.OpenDbConnection())
//{
// try
// {
// db.CreateTable<SiteProInfo>();
// db.SaveAll(siteList);
// }
// catch (Exception ex)
// {
// LogServer.WriteLog(ex, "DBError");
// throw;
// }
//}
}
protected override void Load(ContainerBuilder builder)
{
OrmLiteConfig.DialectProvider = PostgreSQLDialectProvider.Instance;
ServiceStack.OrmLite.OrmLiteConfig.DialectProvider.NamingStrategy = new LowercaseNamingStrategy();
var connectionFactory = new OrmLiteConnectionFactory("Server=127.0.0.1;Port=5432;Database=transactions;User Id=postgres;Password=canada;", false, PostgreSqlDialect.Provider);
using (var db = connectionFactory.OpenDbConnection())
{
DbSetup.Setup(db);
}
builder.Register(c => connectionFactory).As<IDbConnectionFactory>();
builder.Register(c => connectionFactory.Open()).As<IDbConnection>().InstancePerLifetimeScope();
builder.RegisterType<UserRepository>().As<IUserRepository>().As<IRepository<User>>().InstancePerLifetimeScope();
builder.RegisterType<BudgetRepository>().As<IBudgetRepository>().As<IRepository<Budget>>().InstancePerLifetimeScope();
builder.RegisterType<CategoryRepository>().As<ICategoryRepository>().As<IRepository<Category>>().InstancePerLifetimeScope();
builder.RegisterType<TransactionRepository>().As<ITransactionRepository>().As<IRepository<Transaction>>().InstancePerLifetimeScope();
//builder.RegisterType<UserRepository>().As<IRepository<User>>().InstancePerLifetimeScope();
//builder.RegisterType<BudgetRepository>().As<IRepository<Budget>>().InstancePerLifetimeScope();
//builder.RegisterType<CategoryRepository>().As<IRepository<Category>>().InstancePerLifetimeScope();
//builder.RegisterType<TransactionRepository>().As<IRepository<Transaction>>().InstancePerLifetimeScope();
builder.RegisterType<BillRepository>().As<IBillRepository>().As<IRepository<Bill>>().InstancePerLifetimeScope();
builder.RegisterType<RepositoryUnitOfWork>().As<IRepositoryUnitOfWork>().InstancePerLifetimeScope();
builder.RegisterType<SimpleBudgetService>().As<ISimpleBudgetService>().InstancePerLifetimeScope();
// make sure our mock provider is accessible through IVoiceProvider and MockProvider for when we need to fake validation for things.
//builder.RegisterType<MockProvider>().As<IVoiceProvider>().As<MockProvider>().InstancePerLifetimeScope();
//builder.RegisterType<VoiceCore>().As<IVoiceCore>().As<VoiceCore>().InstancePerLifetimeScope();
}
public static IDbConnection GetOpenConnection()
{
string connString = string.Empty;
IOrmLiteDialectProvider provider = null;
if (ConnectionString.StartsWith("postgres://"))
{
connString = GenerateConnectionStringForPostgreSQL(ConnectionString);
provider = PostgreSqlDialect.Provider;
}
else if (ConnectionString.StartsWith("sqlserver://"))
{
connString = GenerationConnectionStringForSqlServer(ConnectionString);
provider = SqlServerDialect.Provider;
}
else
{
connString = ConnectionString;
var providerString = config.Get("provider");
switch (providerString)
{
case "postgres":
provider = PostgreSqlDialect.Provider;
break;
case "sqlserver":
provider = SqlServerDialect.Provider;
break;
default:
break;
}
}
var dbFactory = new OrmLiteConnectionFactory(connString, provider);
return dbFactory.OpenDbConnection();
}
public IDbConnection CreateDatabaseSession()
{
var connectionFactory = new OrmLiteConnectionFactory(this.ConnectionString, false, SqlServerOrmLiteDialectProvider.Instance);
IDbConnection connection = connectionFactory.OpenDbConnection();
return connection;
}
/// <summary>
/// sqllit 删除时候 文件大小不便 需要执行此方法来让文件变小
/// </summary>
public void Clear()
{
_dbFactory = new OrmLiteConnectionFactory(ConnectionString, SqliteDialect.Provider);
using (var db = _dbFactory.OpenDbConnection())
{
db.ExecuteNonQuery("VACUUM");
}
}
public SiteProductsDb()
{
_dbFactory = new OrmLiteConnectionFactory(ZnmDbConnectionString, SqlServerDialect.Provider);
using (var db = _dbFactory.OpenDbConnection())
{
db.CreateTable<SiteProInfo>();
}
}
public PromotionsDB()
{
_dbFactory = new OrmLiteConnectionFactory(ConnectionString, SqliteDialect.Provider);
using (var db = _dbFactory.OpenDbConnection())
{
db.CreateTable<PromotionsInfo>();
}
}
public CommentDb()
{
_dbFactory = new OrmLiteConnectionFactory(ConnectionString, SqliteDialect.Provider);
using (var db = _dbFactory.OpenDbConnection())
{
db.CreateTable<CommentInfo>();
}
}
public UserTransactionDb()
{
_dbFactory = new OrmLiteConnectionFactory(ConnectionString, SqliteDialect.Provider);
using (var db = _dbFactory.OpenDbConnection())
{
db.CreateTable<UserTransaction>();
}
}
public ApiRequstHistroyDB()
{
_dbFactory = new OrmLiteConnectionFactory(ZnmDbConnectionString,SqlServerDialect.Provider);
using (var db = _dbFactory.OpenDbConnection())
{
db.CreateTable<APIRequstHistroy>();
}
}
//private static IDbConnection TheOneConnection;
public SqLiteInMemoryFactory()
{
ConnectionString = MemoryConnectionString;
OrmLiteConnectionFactory = new OrmLiteConnectionFactory (MemoryConnectionString, false, SqliteDialect.Provider);
var db= OrmLiteConnectionFactory.OpenDbConnection ();
db.CreateTableIfNotExists<Thing> ();
db.TableExists ("Thing").ShouldBe(true);
}
public void InsertData()
{
var dbFactory = new OrmLiteConnectionFactory(_connectionString, true, MySqlDialect.Provider);
var db = dbFactory.OpenDbConnection();
db.DropAndCreateTable<NflDotComGame>();
db.InsertAll(_nflDotComGames);
}
public IDbConnection openConn()
{
string connectionString = ConfigurationManager.ConnectionStrings["DefaultConnection"].ConnectionString;
var dbFactory = new OrmLiteConnectionFactory(connectionString,SqlServerOrmLiteDialectProvider.Instance);
IDbConnection dbConn = dbFactory.OpenDbConnection();
OrmLiteConfig.DialectProvider.UseUnicode = true;
return dbConn;
}
public ErrorInfoDB()
{
_dbFactory = new OrmLiteConnectionFactory(ConnectionString, SqliteDialect.Provider);
using (var db = _dbFactory.OpenDbConnection())
{
db.CreateTable<SpiderConfig>();
}
}
public BondInfoDB()
{
_dbFactory = new OrmLiteConnectionFactory(ZnmDbConnectionString, SqlServerDialect.Provider);
// _dbFactory = new OrmLiteConnectionFactory(ConnectionString, SqliteDialect.Provider);
using (var db = _dbFactory.OpenDbConnection())
{
db.CreateTable<BondInfo>();
}
}
public MedicineSiteDB()
{
//_dbFactory = new OrmLiteConnectionFactory(ConnectionString, SqliteDialect.Provider);
_dbFactory=new OrmLiteConnectionFactory(ZnmDbConnectionString,SqlServerDialect.Provider);
using (var db = _dbFactory.OpenDbConnection())
{
db.CreateTable<MedicineSiteInfo>();
}
}
public SiteClassInfoDB()
{
// _dbFactory = new OrmLiteConnectionFactory(ConnectionString, SqliteDialect.Provider);
_dbFactory = new OrmLiteConnectionFactory(MmbpriceDbConnectionString, SqlServerDialect.Provider);
using (var db = _dbFactory.OpenDbConnection())
{
db.CreateTable<SiteClassInfo>();
}
}
public IDbConnection CreateDatabaseSession()
{
VerifyValidSqLiteFile(this.ConnectionString);
var connectionFactory = new OrmLiteConnectionFactory(this.ConnectionString, false, SqliteOrmLiteDialectProvider.Instance);
IDbConnection connection = connectionFactory.OpenDbConnection();
return connection;
}
public static IDbCommand baglanti()
{
string conn = ConfigurationManager.ConnectionStrings["myConnectionString"].ConnectionString;
var dbFactory = new OrmLiteConnectionFactory(conn, SqlServerOrmLiteDialectProvider.Instance);
IDbConnection dbConn = dbFactory.OpenDbConnection();
IDbCommand dbCmd = dbConn.CreateCommand();
return dbCmd;
}
public SettingsViewModel GetSettings()
{
var dbFactory = new OrmLiteConnectionFactory(
_connectionString,
SqlServerOrmLiteDialectProvider.Instance);
using (IDbConnection dbConnection = dbFactory.OpenDbConnection())
{
Dictionary<string, int> deltas = dbConnection.Dictionary<string, int>("SELECT Name, Delta FROM source");
int refreshInterval = dbConnection.Scalar<int>("SELECT TOP 1 RefreshIntervalMinutes FROM settings");
int contactInterval = dbConnection.Scalar<int>("SELECT TOP 1 ContactIntervalMinutes FROM settings");
bool alertsEnabled = dbConnection.Scalar<bool>("SELECT TOP 1 AlertsEnabled FROM settings");
List<Email> emails = dbConnection.Select<Email>("SELECT EmailAddress FROM email");
List<Phone> phones = dbConnection.Select<Phone>("SELECT PhoneNumber FROM phone");
dbConnection.Close();
string emailString = string.Empty;
string phoneString = string.Empty;
for (int i = 0; i < emails.Count; i++)
{
if (i == emails.Count - 1)
{
emailString += emails[i].EmailAddress;
}
else
{
emailString += emails[i].EmailAddress + ", ";
}
}
for (int i = 0; i < phones.Count; i++)
{
if (i == phones.Count - 1)
{
phoneString += phones[i].PhoneNumber;
}
else
{
phoneString += phones[i].PhoneNumber + ", ";
}
}
SettingsViewModel settings = new SettingsViewModel
{
Deltas = deltas,
Emails = emailString,
Phones = phoneString,
RefreshInterval = refreshInterval,
ContactInterval = contactInterval,
AlertsEnabled = alertsEnabled
};
return settings;
}
}
public ApiConfigDB()
{
//_dbFactory = new OrmLiteConnectionFactory(ConnectionString,SqliteDialect.Provider);
_dbFactory = new OrmLiteConnectionFactory(ZnmDbConnectionString, SqlServerDialect.Provider);
using (var db = _dbFactory.OpenDbConnection())
{
db.CreateTable<ApiConfig>();
}
}
/// <summary>
/// Drops all tables matching the name of the LayersCmsDomainObjects to be created,
/// then creates fresh tables for those domain objects.
/// </summary>
private void InitialiseCoreTables(DatabaseSetupConfig config, OrmLiteConnectionFactory dbFactory)
{
if (String.IsNullOrWhiteSpace(config.UserEmailAddress))
throw new NullReferenceException("No UserEmailAddress specified. Cannot create primary user without an email address.");
if (String.IsNullOrWhiteSpace(config.UserPassword))
throw new NullReferenceException("No UserPassword specified. Cannot create primary user without a password.");
// Open a database connection
using (IDbConnection dbConn = dbFactory.OpenDbConnection())
{
// Create the LayersCmsPage table
dbConn.DropAndCreateTable<LayersCmsPage>();
// Add the homepage
dbConn.Save(new LayersCmsPage()
{
Active = true,
Content = "<p>Welcome to Layers CMS</p>",
DisplayName = "Home",
PageTitle = "Index",
ParentId = null,
PublishEnd = null,
PublishStart = DateTime.Now.Date,
RedirectTypeEnum = RedirectTypeEnum.None,
RedirectUrl = null,
ShowInNavigation = true,
SortOrder = 0,
Url = "/",
WindowTitle = "Index"
});
// Create the LayersCmsUser table and insert the first user
dbConn.DropAndCreateTable<LayersCmsUser>();
dbConn.Save(new LayersCmsUser()
{
Active = true,
EmailAddress = config.UserEmailAddress,
Password = _hashHelper.HashString(config.UserPassword) // A hashed version of the plain text password
});
// Create the settings table and add some default settings
dbConn.DropAndCreateTable<LayersCmsSetting>();
dbConn.SaveAll(new []
{
new LayersCmsSetting(){SettingType = LayersCmsSettingType.ContactEmailAddress, Value = "*****@*****.**"},
new LayersCmsSetting(){SettingType = LayersCmsSettingType.ContactTelephoneNumber, Value = "0000 000000"},
new LayersCmsSetting(){SettingType = LayersCmsSettingType.GoogleAnalyticsAccountId, Value = ""}
});
}
}
public void TestMethod2()
{
var dbFactory = new OrmLiteConnectionFactory(ConfigurationManager.ConnectionStrings["DefaultConnection"].ToString(), SqlServerDialect.Provider);
using (var db = dbFactory.OpenDbConnection())
{
var goodses = db.Select<Goods>();
foreach (var goodse in goodses)
{
goodse.Materials = db.Select<GoodsMaterial>(item => item.GoodsID == goodse.ID).ToList();
db.Update(goodse);
}
}
}
// For now this returns just one value, but if we need to separate text vs email contact intervals
// it is already set up in the database to be able to do so. Just have to have separate methods
// to get them.
public DateTime GetLastContact()
{
var dbFactory = new OrmLiteConnectionFactory(
_connectionString,
SqlServerOrmLiteDialectProvider.Instance);
using (IDbConnection dbConnection = dbFactory.OpenDbConnection())
{
DateTime lastContact = dbConnection.Scalar<DateTime>("SELECT TOP 1 LastContacted FROM contactGroup");
dbConnection.Close();
return lastContact;
}
}
public TempSQLiteFactory()
{
SqlFile = new TempFile();
SQLiteConnection.CreateFile(SqlFile.Path);
ConnectionString = SqlFile.Path;
OrmLiteConnectionFactory = new OrmLiteConnectionFactory (ConnectionString, true, SqliteDialect.Provider);
var db= OrmLiteConnectionFactory.OpenDbConnection ();
db.CreateTableIfNotExists<Thing> ();
db.CreateTableIfNotExists<User>();
db.TableExists("Thing").ShouldBe(true);
db.TableExists("User").ShouldBe(true);
}
public void Can_save_and_load_country_region()
{
var factory = new OrmLiteConnectionFactory(ConnectionString, SqlServerDialect.Provider);
using (var db = factory.OpenDbConnection())
{
db.DropTables(typeof(Territory), typeof(CountryRegion));
db.CreateTables(false, typeof(CountryRegion), typeof(Territory));
db.Insert(new CountryRegion { Code = "AD", Name = "Andorra", ModifiedDate = DateTime.Now });
using (var dbTrans = db.BeginTransaction())
{
db.Insert(new CountryRegion { Code = "AE", Name = "United Arab Emirates", ModifiedDate = DateTime.Now });
db.Insert(new CountryRegion { Code = "AF", Name = "Afghanistan", ModifiedDate = DateTime.Now });
dbTrans.Commit();
}
Assert.That(db.Select<CountryRegion>(), Has.Count.EqualTo(3));
using (var dbTrans = db.BeginTransaction())
{
db.Insert(new CountryRegion { Code = "AG", Name = "Antigua and Barbuda", ModifiedDate = DateTime.Now });
Assert.That(db.Select<CountryRegion>(), Has.Count.EqualTo(4));
dbTrans.Rollback();
}
Assert.That(db.Select<CountryRegion>(), Has.Count.EqualTo(3));
db.Insert(new CountryRegion { Code = "US", Name = "United States", ModifiedDate = DateTime.Now });
db.Insert(new Territory
{
CountryRegionCode = "US",
Name = "Northwest"
});
db.Insert(new Territory
{
CountryRegionCode = "US",
Name = "Northeast"
});
var usRegion = db.Select<CountryRegion>(cr => cr.Code == "US")[0];
var territories = db.Select<Territory>(t => t.CountryRegionCode == usRegion.Code);
territories.ForEach(t => t.CountryRegion = usRegion);
usRegion.Territories = territories;
Assert.IsTrue(usRegion.Territories.Count == 2);
}
}
public void Load()
{
var weekNumber = 20140721;
var factory = new OrmLiteConnectionFactory(SqliteFileDb, SqliteDialect.Provider);
using (var db = factory.OpenDbConnection())
{
WeekSchedule week = db.Select<WeekSchedule>(q => q.Id == weekNumber).FirstOrDefault();
Approvals.Verify(week.Dump());
}
//var week = LookupWeekSchedule(weekNumber);
}
public Database()
{
//Use in-memory Sqlite DB instead
var dbPath = System.IO.Path.Combine(Environment.GetFolderPath(Environment.SpecialFolder.MyDocuments), "Calls.db");
var dbFactory = new OrmLiteConnectionFactory(dbPath, false, SqliteOrmLiteDialectProvider.Instance);
//Non-intrusive: All extension methods hang off System.Data.* interfaces
IDbConnection dbConn = dbFactory.OpenDbConnection();
dbCmd = dbConn.CreateCommand();
////Re-Create all table schemas:
//dbCmd.DropTable<CallItem>();
dbCmd.CreateTable<CallItem>();
}
public virtual IDbConnection OpenDbConnectionString(string connectionString, string providerName)
{
if (connectionString == null)
{
throw new ArgumentNullException(nameof(connectionString));
}
if (providerName == null)
{
throw new ArgumentNullException(nameof(providerName));
}
if (!DialectProviders.TryGetValue(providerName, out var dialectProvider))
{
throw new ArgumentException($"{providerName} is not a registered DialectProvider");
}
var dbFactory = new OrmLiteConnectionFactory(connectionString, dialectProvider, setGlobalDialectProvider: false);
return(dbFactory.OpenDbConnection());
}
