///////////////////////////////////////////////////////////////////////
// Public STATIC Functions
///////////////////////////////////////////////////////////////////////
static public bool createDBFile()
{
bool result = false;
SqlCeEngine engine = new SqlCeEngine();
engine.LocalConnectionString = Properties.Settings.Default.FFDBConnectionString;
string filePath = AppDomain.CurrentDomain.GetData("DataDirectory").ToString();
filePath += "\\" + Properties.Settings.Default.DBFileName;
if (File.Exists(filePath))
{
result = false;
}
else
{
engine.CreateDatabase();
engine.Dispose();
result = buildTables();
}
return(result);
}
public static void CreateDB()
{
SqlCeEngine engine = new SqlCeEngine(ConStrDB);
engine.CreateDatabase();
engine.Dispose();
}
public void RunApplication(string[] args)
{
// example command line args:
// ClearCanvas.Dicom.DataStore.SetupApplication.Application "<TrunkPath>\Dicom\DataStore\AuxiliaryFiles\empty_viewer.sdf" "<TrunkPath>\Dicom\DataStore\AuxiliaryFiles\CreateTables.clearcanvas.dicom.datastore.ddl"
string databaseFile = args[0];
string scriptFile = args[1];
File.Delete(databaseFile);
string connectionString = String.Format("Data Source=\"{0}\"", databaseFile);
SqlCeEngine engine = new SqlCeEngine(connectionString);
engine.CreateDatabase();
engine.Dispose();
StreamReader reader = new StreamReader(scriptFile);
string scriptText = reader.ReadToEnd();
reader.Close();
SqlCeConnection connection = new SqlCeConnection(connectionString);
connection.Open();
SqlCeTransaction transaction = connection.BeginTransaction();
SqlCeCommand command = new SqlCeCommand();
command.Connection = connection;
command.CommandText = scriptText;
command.ExecuteNonQuery();
transaction.Commit();
connection.Close();
}
/// <summary>
/// Copies the LM.
/// </summary>
/// <returns></returns>
/// <remarks>Documented by Dev07, 2009-05-20</remarks>
public static bool CopyLM(string source, string destination)
{
try
{
string filename = destination;
if (File.Exists(filename))
{
int i = 0;
while (File.Exists(filename.Replace(".mlm", "_" + i + ".mlm"))) i++;
filename = filename.Replace(".mlm", "_" + i + ".mlm");
}
string sourceConString = MSSQLCEConn.GetFullConnectionString(source);
string desConString = MSSQLCEConn.GetFullConnectionString(filename);
SqlCeEngine engine = new SqlCeEngine(sourceConString);
engine.Compact(desConString);
engine.Dispose();
}
catch (Exception e)
{
Trace.WriteLine(e.ToString());
return false;
}
return true;
}
public void DBCreate()
{
string connStr = "Data Source='" + myDBName + "'; LCID=1033; Password=\"" + myDBPassword + "\"; Encrypt = ";
if (myDBEncrypt == true)
{
connStr += "TRUE;";
}
else
{
connStr += "FALSE;";
}
if (myDBEncrypt == false && File.Exists(myDBName))
{
return;
}
else
{
File.Delete(myDBName);
}
try
{
mySqlEngine = new SqlCeEngine(connStr);
mySqlEngine.CreateDatabase();
}
catch
{
}
finally
{
mySqlEngine.Dispose();
}
}
public static bool existe_db()
{
msj = null;
try
{
if (!System.IO.File.Exists(System.IO.Directory.GetCurrentDirectory() + "\\DB.sdf"))
{
string connectionString = ConfigurationManager.ConnectionStrings["RSSFeed.Properties.Settings.DBConnectionString"].ConnectionString;
SqlCeEngine en = new SqlCeEngine(connectionString);
en.CreateDatabase();
var conexion = new SqlCeConnection(connectionString);
conexion.Open();
var ruta = System.IO.Directory.GetCurrentDirectory() + "DBCreation.sql";
var contenido = System.IO.File.ReadAllText(ruta);
var comando = new SqlCeCommand(contenido, conexion);
comando.ExecuteNonQuery();
conexion.Close();
en.Dispose();
return(true);
}
else
{
return(false);
}
}
catch (Exception f)
{
msj = f;
}
return(false);
}
/// <summary>
/// Copies the LM.
/// </summary>
/// <returns></returns>
/// <remarks>Documented by Dev07, 2009-05-20</remarks>
public static bool CopyLM(string source, string destination)
{
try
{
string filename = destination;
if (File.Exists(filename))
{
int i = 0;
while (File.Exists(filename.Replace(".mlm", "_" + i + ".mlm")))
{
i++;
}
filename = filename.Replace(".mlm", "_" + i + ".mlm");
}
string sourceConString = MSSQLCEConn.GetFullConnectionString(source);
string desConString = MSSQLCEConn.GetFullConnectionString(filename);
SqlCeEngine engine = new SqlCeEngine(sourceConString);
engine.Compact(desConString);
engine.Dispose();
}
catch (Exception e)
{
Trace.WriteLine(e.ToString());
return(false);
}
return(true);
}
public bool Create()
{
bool ret = true;
// Проверяем если файла нет в наличии то создаем базу данных
if (!File.Exists(FileName))
{
//Создание файла базы данных !!!!!!!!!!!!!!!!!!!!!!!!!!!!!
SqlCeEngine engine = new SqlCeEngine();
try
{
engine.LocalConnectionString = connString;
engine.CreateDatabase();
}
catch
{
ret = false;
}
finally
{
if (engine != null)
{
engine.Dispose();
}
}
}
return(ret);
}
public void SetupSQLCEDatabase()
{
// Discard the previous iteration of this test database.
if (File.Exists(sqlCEDatabaseName))
{
File.Delete(sqlCEDatabaseName);
}
// Connect to SQL CE, and create the new database
sqlCEEngine = new SqlCeEngine(StaticTestUtilities.connectionString(sqlCEDatabaseName, sqlCEPassword));
sqlCEEngine.CreateDatabase();
// Connect to the sucker
SqlCeConnection connection = new SqlCeConnection(StaticTestUtilities.connectionString(sqlCEDatabaseName, sqlCEPassword));
if (connection.State == ConnectionState.Closed)
{
connection.Open();
}
// Create the table with the test results.
String tableCreate = "CREATE TABLE [TestRecords] ([StringData] nvarchar(255), [MoreString] nvarchar(255), [DateColumn] DATETIME, [IntegerColumn] bigint, [NumericColumn] numeric(15,2), [SHA1BinaryOutput] varbinary(20), [SHA1HexOutput] nvarchar(42), [SHA1BaseOutput] nvarchar(28))";
SqlCeCommand command = new SqlCeCommand(tableCreate, connection);
command.ExecuteNonQuery();
// Diconnect from the SQL CE database
connection.Close();
sqlCEEngine.Dispose();
}
private SqlCeConnection GetConnection()
{
//int empresa_id = int.Parse(file.Name.Split('_')[0]);
//empresa = CntLainsaSci.GetEmpresa(empresa_id, ctx);
//AppDomain.CurrentDomain.SetData("SQLServerCompactEditionUnderWebHosting", true);
//SqlCeConnection conn = GetConnection();
//GuardarDispositivos(conn);
//ctx.SaveChanges();
string conn = string.Format("Data Source={0};Password =;Persist Security Info=True", file);
SqlCeEngine DBDatabase = new SqlCeEngine(conn);
SqlCeConnection vCon = new System.Data.SqlServerCe.SqlCeConnection(conn);
try
{
vCon.Open();
return(vCon);
}
catch (Exception VError)
{
ControlDeError(VError);
}
finally
{
DBDatabase.Dispose();
}
return(null);
}
private void btnconectar_Click(object sender, EventArgs e)
{
string baseDados = Application.StartupPath + @"\db\DBSQLServer.sdf"; // caminho do BD
string strConnection = @"DataSource = " + baseDados + "; Password = '******'";
SqlCeEngine db = new SqlCeEngine(strConnection);
if (!File.Exists(baseDados))
{
db.CreateDatabase();
}
db.Dispose();
SqlCeConnection conexao = new SqlCeConnection(strConnection);
try
{
conexao.Open();
resultado.Text = "Banco de Dados Conectado";
}
catch (Exception ex)
{
resultado.Text = "ERRO, não conectado! \n" + ex;
}
finally
{
conexao.Close();
}
}
/// <summary>
/// Creates the new DB.
/// </summary>
/// <param name="filename">The filename.</param>
/// <param name="overwrite">if set to <c>true</c> overwrite exisiting file.</param>
/// <returns></returns>
public static string CreateNewDB(string filename, bool overwrite)
{
string file = filename;
if (File.Exists(file))
{
if (overwrite)
{
File.Delete(file);
}
else
{
int cnt = 1;
while (File.Exists(Path.ChangeExtension(file, "_" + cnt + Path.GetExtension(file))))
{
cnt++;
}
file = Path.ChangeExtension(file, "_" + cnt + Path.GetExtension(file));
}
}
SqlCeEngine engine = new SqlCeEngine(GetConnectionString(file));
engine.CreateDatabase();
engine.Dispose();
return(file);
}
public void Dispose()
{
engine.Dispose();
connection.Dispose();
engine = null;
connection = null;
RemoveDataSource(constr);
}
public void Upgrade()
{
using (SqlCeEngine engine = new SqlCeEngine(_connString))
{
engine.Upgrade();
engine.Dispose();
}
}
public void CreateDatabase()
{
using (SqlCeEngine engine = new SqlCeEngine(_connString))
{
engine.CreateDatabase();
engine.Dispose();
}
}
public static void CreateDB()
{
SqlCeEngine Engine = new SqlCeEngine("DataSource=ankara.sdf");
Engine.CreateDatabase();
Engine.Dispose();
}
public void crearBD()
{
SqlCeEngine engine = new SqlCeEngine("Data Source =" + Configuracion.directorio + Configuracion.baseDatos);
engine.CreateDatabase();
engine.Dispose();
this.crearTablas();
}
/// <summary>
/// Static helper function to create an empty CE database
/// </summary>
/// <param name="client"></param>
public static void CheckAndCreateCEDatabase(CEDatabase client)
{
if (!File.Exists(client.Location))
{
SqlCeEngine engine = new SqlCeEngine(client.Connection.ConnectionString);
engine.CreateDatabase();
engine.Dispose();
}
}
static void Main(string[] args)
{
var databaseFilePath = @"c:\code\achlookup\db.sdf";
var connectionString = String.Format("Data Source={0}", databaseFilePath);
var filePath = @"c:\code\achlookup\fpddir.txt";
if (File.Exists(databaseFilePath))
{
File.Delete(databaseFilePath);
}
var engine = new SqlCeEngine(connectionString);
engine.CreateDatabase();
engine.Dispose();
using (var connection = new SqlCeConnection(connectionString))
{
connection.Open();
var command = connection.CreateCommand();
command.CommandText = @"
CREATE TABLE Banks (RoutingNumber nvarchar(10), Name nvarchar(255))
";
command.ExecuteNonQuery();
connection.Close();
}
var sql = @"
INSERT INTO Banks(RoutingNumber,Name) VALUES(@RoutingNumber,@BankName)
";
using (var connection = new SqlCeConnection(connectionString))
{
connection.Open();
var command = connection.CreateCommand();
command.CommandText = sql;
command.Parameters.Add("@RoutingNumber", SqlDbType.NVarChar);
command.Parameters.Add("@BankName", SqlDbType.NVarChar);
foreach (var line in File.ReadLines(filePath))
{
BankRecord record;
record.RoutingNumber = line.Substring(0, 9);
record.Name = line.Substring(27, 36);
command.Parameters["@RoutingNumber"].Value = record.RoutingNumber;
command.Parameters["@BankName"].Value = record.Name;
command.ExecuteNonQuery();
}
}
Console.WriteLine("All DONE :)");
Console.ReadLine();
}
public override void TearDown()
{
base.TearDown();
_provider.Dispose();
engine.Dispose();
connection.Dispose();
engine = null;
connection = null;
SqlCeEngineTestContainer.RemoveDataSource(constr);
}
public void AfterDatabaseIntegration()
{
try
{
sqlCeEngine.Dispose();
File.Delete(DatabaseFileName);
}
catch (IOException)
{
}
}
public void FixtureSetUp()
{
dbPath = System.IO.Path.Combine(Environment.GetEnvironmentVariable("TEMP"), Guid.NewGuid().ToString()) + "ScrewTurnWikiTest.sdf";
connString = "Persist Security Info = False; Data Source = '" + dbPath + "';";
// Create database with no tables
SqlCeEngine engine = new SqlCeEngine(connString);
engine.CreateDatabase();
engine.Dispose();
}
public void AfterDatabaseIntegration()
{
try
{
sqlCeEngine.Dispose();
File.Delete("TestDB.sdf");
}
catch (IOException)
{
}
}
static void Main(string[] args)
{
var databaseFilePath = @"c:\code\achlookup\db.sdf";
var connectionString = String.Format("Data Source={0}", databaseFilePath);
var filePath = @"c:\code\achlookup\fpddir.txt";
if(File.Exists(databaseFilePath))
{
File.Delete(databaseFilePath);
}
var engine = new SqlCeEngine(connectionString);
engine.CreateDatabase();
engine.Dispose();
using(var connection = new SqlCeConnection(connectionString))
{
connection.Open();
var command = connection.CreateCommand();
command.CommandText = @"
CREATE TABLE Banks (RoutingNumber nvarchar(10), Name nvarchar(255))
";
command.ExecuteNonQuery();
connection.Close();
}
var sql = @"
INSERT INTO Banks(RoutingNumber,Name) VALUES(@RoutingNumber,@BankName)
";
using (var connection = new SqlCeConnection(connectionString))
{
connection.Open();
var command = connection.CreateCommand();
command.CommandText = sql;
command.Parameters.Add("@RoutingNumber", SqlDbType.NVarChar);
command.Parameters.Add("@BankName", SqlDbType.NVarChar);
foreach (var line in File.ReadLines(filePath))
{
BankRecord record;
record.RoutingNumber = line.Substring(0, 9);
record.Name = line.Substring(27, 36);
command.Parameters["@RoutingNumber"].Value = record.RoutingNumber;
command.Parameters["@BankName"].Value = record.Name;
command.ExecuteNonQuery();
}
}
Console.WriteLine("All DONE :)");
Console.ReadLine();
}
public static void CompactDatabase()
{
string src = ConnectionParamter;
string dest = ConnectionParamter+".tmp";
// Initialize SqlCeEngine object.
SqlCeEngine engine = new SqlCeEngine("Data Source = " + src);
try {
//engine.Repair("Data Source = " + src, RepairOption.DeleteCorruptedRows);
engine.Compact("Data Source = " + dest);
engine.Dispose();
File.Delete(src);
File.Move(dest, src);
} catch(SqlCeException e) {
//Use your own error handling routine.
//ShowErrors(e);
CommonUtils.LogError(e.Message,e);
} finally {
//Dispose of the SqlCeEngine object.
engine.Dispose();
}
}
public static void CreateDatabase(string databaseName)
{
if (File.Exists(databaseName))
{
File.Delete(databaseName);
}
var engine = new SqlCeEngine(GetConnectionString(databaseName));
engine.CreateDatabase();
engine.Dispose();
}
private void mitemRecreate_Click(object sender, EventArgs e)
{
if (File.Exists(strDBFile))
{
File.Delete(strDBFile);
}
SqlCeEngine engine = new SqlCeEngine(strConnLocal);
engine.CreateDatabase();
engine.Dispose();
}
/// <summary>
/// Opens the file.
/// </summary>
private void OpenFile()
{
bool createSchema = false;
if (!File.Exists(Filename))
{
createSchema = true;
SqlCeEngine engine = new SqlCeEngine(SqlCE.GetConnectionString(Filename));
engine.CreateDatabase();
engine.Dispose();
}
if (!SqlCeTicketDataSource.ActiveRecordsInitialized)
{
IDictionary <string, string> properties = new Dictionary <string, string>();
properties.Add("connection.driver_class", "NHibernate.Driver.SqlServerCeDriver");
properties.Add("dialect", "NHibernate.Dialect.MsSqlCeDialect");
properties.Add("connection.provider", "NHibernate.Connection.DriverConnectionProvider");
properties.Add("connection.connection_string", SqlCE.GetConnectionString(Filename));
properties.Add("proxyfactory.factory_class", "NHibernate.ByteCode.Castle.ProxyFactoryFactory, NHibernate.ByteCode.Castle");
InPlaceConfigurationSource source = new InPlaceConfigurationSource();
source.Add(typeof(ActiveRecordBase), properties);
ActiveRecordStarter.Initialize(Assembly.GetExecutingAssembly(), source);
SqlCeTicketDataSource.ActiveRecordsInitialized = true;
}
else
{
SqlCeConnection con = new SqlCeConnection(SqlCE.GetConnectionString(Filename));
con.Open();
DifferentDatabaseScope scope = new DifferentDatabaseScope(con);
}
if (createSchema)
{
ActiveRecordStarter.CreateSchema();
}
AllTickets = new ObservableCollection <ITicket>();
IQueryable <ITicket> list = (from t in TicketRecord.Queryable
select t).Cast <ITicket>();
list.ToList().ForEach((Action <ITicket>) delegate(ITicket t)
{
t.PropertyChanged += new PropertyChangedEventHandler(ticket_PropertyChanged);
AllTickets.Add(t);
});
AllTickets.CollectionChanged += new NotifyCollectionChangedEventHandler(Tickets_CollectionChanged);
UpdateActiveTickets();
}
private static String CreateDatabase()
{
String dbPath = String.Format("{0}scanner.sdf", rootPath);
if (File.Exists(dbPath))
File.Delete(dbPath);
String connectionString = String.Format("DataSource=\"{0}\";Max Database Size=3000;", dbPath);
SqlCeEngine en = new SqlCeEngine(connectionString);
en.CreateDatabase();
en.Dispose();
return connectionString;
}
//#### Methods
//Takes connection string returns SqlCeConnection
public IDbConnection Connection(string aConnectionString)
{
ConnectionString = aConnectionString;
//Create SqlCe file if it does not exist
if (File.Exists(m_DbFileName) == false)
{
SqlCeEngine SqlEngine = new SqlCeEngine(aConnectionString);
SqlEngine.CreateDatabase();
SqlEngine.Dispose();
}
return(Connection());
}
private void CreateDatabase()
{
try
{
SqlCeEngine en = new SqlCeEngine(connectionString);
en.CreateDatabase();
en.Dispose();
}
catch (Exception e)
{
Console.WriteLine($"Could not create database. Error: {e}");
MessageBox.Show($"Could not create database. Error: {e}");
}
}
private bool criarBanco()
{
//se nao existir, cria o banco de dados
if (!File.Exists(pastaDados + nomeBD))
{
SqlCeEngine engine = new SqlCeEngine(connStr);
engine.CreateDatabase();
engine.Dispose();
return(true);
}
else
{
return(false);
}
}
private void Dispose(bool ADisposing)
{
if (!m_Disposed)
{
// wenn true, alle managed und unmanaged resources mussen aufgelegt werden.
if (ADisposing)
{
//nix zu machen in moment
}
try
{
if (null != m_Cmd)
{
m_Cmd.Dispose();
}
m_Cmd = null;
if (null != m_Transaction)
{
m_Transaction.Dispose();
}
m_Transaction = null;
if (null != m_Conn)
{
m_Conn.Close();
m_Conn.Dispose();
}
m_Conn = null;
if (null != m_Engine)
{
//m_Engine.Shrink();
m_Engine.Dispose();
m_Engine = null;
}
}
catch
{
}
}
m_Disposed = true;
}
public static bool CriarBancoDados()
{
try
{
string connString = "Data Source = '" + Program.ARQUIVO_DADOS + "'; LCID=1033;";
SqlCeEngine engine = new SqlCeEngine(connString);
engine.CreateDatabase();
engine.Dispose();
return(CriarTabelas());
}
catch
{
return(false);
}
}
/// <summary>
/// Creat new local database
/// </summary>
/// <param name="path"></param>
private void CreateDBFIle(string path)
{
try
{
SqlCeEngine engine = new SqlCeEngine("Data Source = " + path);
engine.CreateDatabase();
engine.Dispose();
Connect(path);
model.logsViewModel.AddTextToLogs("Create new local SDF file.");
}
catch {
model.logsViewModel.AddTextToLogs("Conection to local SDF file.");
}
}
public static dynamic CreateConnection(string dbName, IList<string> createTables, IList<string> queries)
{
if (File.Exists(dbName))
File.Delete(dbName);
var en = new SqlCeEngine("Data Source = " + dbName);
en.CreateDatabase();
en.Dispose();
var conn = new SqlCeConnection("Data Source = " + dbName);
conn.Open();
foreach (var comm in createTables.Select(createTable => new SqlCeCommand(createTable, conn)))
{
Console.WriteLine("Response: " + comm.ExecuteNonQuery());
}
foreach (var query in queries)
{
SqlCeCommand cmd = conn.CreateCommand();
cmd.CommandText = query;
cmd.ExecuteNonQuery();
}
conn.Close();
return Database.OpenFile(dbName);
}
/**
*
* 创建PDA端的数据库,并且初始化数据库。包括出库表，返库表，入库表
*
* */
public bool createDB()
{
try
{
if (!System.IO.File.Exists(cfg.LocalDBFile))
{
SqlCeEngine ECsqlEngine = new SqlCeEngine(cfg.LocalConnString);
ECsqlEngine.CreateDatabase();
ECsqlEngine.Dispose();
string outLibrary = @"CREATE TABLE OUT_LIBRARY (epc nvarchar(32) NOT NULL,time datetime,person nvarchar(32), salepointcode nvarchar(50))";
string backLibrary = @"CREATE TABLE BACK_LIBRARY (epc nvarchar(32) NOT NULL,time datetime, person nvarchar(32))";
string inLibrary = @"CREATE TABLE IN_LIBRARY (epc nvarchar(32) NOT NULL,time datetime, person nvarchar(32))";
//string manufacture = @"CREATE TABLE MANUFACTURE (manufacture_code nvarchar(32) NOT NULL,name nvarchar(64),address nvarchar(64),license nvarchar(32))";
string classify = @"CREATE TABLE PRODUCT_CLASSIFY (code nvarchar(32) NOT NULL,name nvarchar(64))";
string product = @"CREATE TABLE PRODUCT (product_code nvarchar(32) NOT NULL,product_name nvarchar(64),classify nvarchar(64),format nvarchar(64),level nvarchar(64),location nvarchar(64))";
string account = @"CREATE TABLE ACCOUNT (name nvarchar(32) NOT NULL,password nvarchar(32))";
string salePoint = @"CREATE TABLE SALEPOINT (code nvarchar(50) NOT NULL,name nvarchar(50))";
DBAccess dbAccess = new SqlCeDBAccess();
dbAccess.excute(outLibrary);
dbAccess.excute(backLibrary);
dbAccess.excute(inLibrary);
//dbAccess.excute(manufacture);
dbAccess.excute(classify);
dbAccess.excute(product);
dbAccess.excute(account);
dbAccess.excute(salePoint);
}
}
catch (Exception e)
{
MessageBox.Show(e.Message);
return false;
}
return true;
}
/// <summary>
/// Creates a new extension file.
/// </summary>
/// <remarks>Documented by Dev07, 2009-07-06</remarks>
public void Create()
{
if (!Directory.Exists(Path.GetDirectoryName(User.ConnectionString.ConnectionString)))
{
Directory.CreateDirectory(Path.GetDirectoryName(User.ConnectionString.ConnectionString));
}
if (!File.Exists(User.ConnectionString.ConnectionString))
{
using (SqlCeEngine clientEngine = new SqlCeEngine("Data Source=" + User.ConnectionString.ConnectionString))
{
clientEngine.CreateDatabase();
clientEngine.Dispose();
}
}
using (SqlCeCommand cmd = MSSQLCEConn.CreateCommand(User))
{
cmd.CommandText = Resources.MsSqlCeDbCreateScript;
MSSQLCEConn.ExecuteNonQuery(cmd);
}
}
// Сформировать
private void button1_Click(object sender, EventArgs e)
{
XmlTextReader reader = new XmlTextReader("http://test-danru.rhcloud.com/data.xml");
TAG id = 0; // идентификатор тегов
int PK = 0; // номер записи
ArrayList tuple = new ArrayList(); // таблица statistics
TUPLE temp = new TUPLE(); // строка таблицы
// создание таблицы в памяти
while (reader.Read())
{
switch (reader.NodeType)
{
case XmlNodeType.Element: // открывающий тег
switch (reader.Name)
{
case "Item":
// <Item Date>
if (reader.AttributeCount != 0)
temp.Date = "'"
+ DateTime.ParseExact(reader.GetAttribute(0), "dd.MM.yyyy",
CultureInfo.InvariantCulture).ToString("yyyyMMdd") +
"'";
break;
case "Views": id = TAG.Views; break;
case "Clicks": id = TAG.Clicks; break;
}
break;
case XmlNodeType.Text: // содержимое
switch (id)
{
case TAG.Views:
temp.Views = reader.Value;
id = 0;
break;
case TAG.Clicks:
temp.Clicks = reader.Value;
id = 0;
temp.ID = ++PK;
tuple.Add(new TUPLE(temp.ID, temp.Date, temp.Views, temp.Clicks));
break;
}
break;
}
}
// ---------------------------------------------------------------------------------------------
// для создания новой БД
if (File.Exists("advertisement-statistics.sdf"))
File.Delete("advertisement-statistics.sdf");
// создание файла БД
SqlCeEngine engine = new SqlCeEngine("Data Source='advertisement-statistics.sdf'; LCID=1033;");
engine.CreateDatabase();
engine.Dispose();
// соединение с файлом БД
var connection = new System.Data.SqlServerCe.SqlCeConnection();
connection.ConnectionString = "Data Source='advertisement-statistics.sdf'";
connection.Open();
// создание БД
var command = new System.Data.SqlServerCe.SqlCeCommand();
command.Connection = connection;
command.CommandText = "CREATE TABLE data ("
+ "ID int,"
+ "Date datetime NOT NULL,"
+ "Views int NOT NULL,"
+ "Clicks int NOT NULL,"
+ "PRIMARY KEY (ID) );";
command.ExecuteReader();
// заполнение БД
foreach (TUPLE a in tuple)
{
command.CommandText = "INSERT INTO [data] VALUES (" + a.ID + ", "
+ a.Date + ", "
+ a.Views + ", "
+ a.Clicks + ")";
command.ExecuteReader();
}
listView1.Items.Clear();
// отображение БД
command.CommandText = "SELECT * FROM [data]";
var table = command.ExecuteReader();
int i;
int fieldCount = table.FieldCount;
while (table.Read() == true)
{
i = 0;
lvi = new ListViewItem();
lvi.Text = table.GetValue(i++).ToString();
listView1.Items.Add(lvi);
for (; i < fieldCount; ++i)
lvi.SubItems.Add(table.GetValue(i).ToString());
}
// среднее количество просмотров и кликов
command.CommandText = "SELECT AVG(Views), AVG(Clicks) FROM [data]";
table = command.ExecuteReader();
listView1.Items.Add(new ListViewItem()); // пустая строка
while (table.Read() == true)
{
lvi = new ListViewItem();
listView1.Items.Add(lvi);
lvi.SubItems.Add("");
lvi.SubItems.Add("AVG = " + table.GetValue(0).ToString());
lvi.SubItems.Add("AVG = " + table.GetValue(1).ToString());
}
// общее количество просмотров и кликов
command.CommandText = "SELECT SUM(Views), SUM(Clicks) FROM [data]";
table = command.ExecuteReader();
while (table.Read() == true)
{
lvi = new ListViewItem();
listView1.Items.Add(lvi);
lvi.SubItems.Add("");
lvi.SubItems.Add("SUM = " + table.GetValue(0).ToString());
lvi.SubItems.Add("SUM = " + table.GetValue(1).ToString());
}
// данные за последние 5 дней
command.CommandText = "SELECT TOP 5 * FROM [data] ORDER BY Date DESC";
table = command.ExecuteReader(System.Data.CommandBehavior.CloseConnection);
listView1.Items.Add(new ListViewItem()); // пустая строка
lvi = new ListViewItem(); // строка
listView1.Items.Add(lvi);
lvi.SubItems.Add("----- Данные за последние 5 дней -----");
while (table.Read() == true)
{
i = 0;
lvi = new ListViewItem();
lvi.Text = table.GetValue(i++).ToString();
listView1.Items.Add(lvi);
for (; i < fieldCount; ++i)
lvi.SubItems.Add(table.GetValue(i).ToString());
}
table.Close();
connection.Close();
}
public int AddNewLM(string guid, int categoryId, string title, string licenceKey, bool contentProtected, int calCount)
{
if (!Parent.CurrentUser.ConnectionString.ConnectionString.EndsWith(Helper.EmbeddedDbExtension))
{
ConnectionStringStruct css = Parent.CurrentUser.ConnectionString;
css.ConnectionString += @"\" + title.Replace(@"\", "_") + Helper.EmbeddedDbExtension;
css.Typ = DatabaseType.MsSqlCe;
Parent.CurrentUser.ConnectionString = css;
}
{
//replace invalid filename characters
ConnectionStringStruct css = Parent.CurrentUser.ConnectionString;
css.ConnectionString = Helper.FilterInvalidFilenameCharacters(css.ConnectionString);
Parent.CurrentUser.ConnectionString = css;
}
if (File.Exists(Parent.CurrentUser.ConnectionString.ConnectionString))
{
int i = 1;
while (File.Exists(Parent.CurrentUser.ConnectionString.ConnectionString.Replace(Helper.EmbeddedDbExtension, "_" + i + Helper.EmbeddedDbExtension))) i++;
ConnectionStringStruct css = Parent.CurrentUser.ConnectionString;
css.ConnectionString = css.ConnectionString.Replace(Helper.EmbeddedDbExtension, "_" + i + Helper.EmbeddedDbExtension);
Parent.CurrentUser.ConnectionString = css;
}
if (!Directory.Exists(Path.GetDirectoryName(Parent.CurrentUser.ConnectionString.ConnectionString)))
{
Directory.CreateDirectory(Path.GetDirectoryName(Parent.CurrentUser.ConnectionString.ConnectionString));
}
using (SqlCeEngine clientEngine = new SqlCeEngine("Data Source=" + Parent.CurrentUser.ConnectionString.ConnectionString))
{
clientEngine.CreateDatabase();
clientEngine.Dispose();
}
using (SqlCeCommand cmd = MSSQLCEConn.CreateCommand(Parent.CurrentUser))
{
cmd.CommandText = Resources.MsSqlCeDbCreateScript;
MSSQLCEConn.ExecuteNonQuery(cmd);
}
MSSQLCEConn.ApplyIndicesToDatabase(MSSQLCEConn.GetConnection(Parent.CurrentUser));
int cat_id;
using (SqlCeCommand cmd = MSSQLCEConn.CreateCommand(Parent.CurrentUser))
{
cmd.CommandText = "SELECT id FROM Categories WHERE global_id=@cat_id;";
cmd.Parameters.Add("@cat_id", categoryId);
cat_id = MSSQLCEConn.ExecuteScalar<int>(cmd).Value;
}
using (SqlCeCommand cmd = MSSQLCEConn.CreateCommand(Parent.CurrentUser))
{
cmd.CommandText = "INSERT INTO LearningModules (guid, title, categories_id, default_settings_id, allowed_settings_id, licence_key, content_protected, cal_count) " +
"VALUES (@guid, @title, @cat_id, @dset, @aset, @lk, @cp, @cals); SELECT @@IDENTITY;";
cmd.Parameters.Add("@guid", guid);
cmd.Parameters.Add("@title", title);
cmd.Parameters.Add("@cat_id", cat_id);
cmd.Parameters.Add("@lk", licenceKey);
cmd.Parameters.Add("@cp", contentProtected);
cmd.Parameters.Add("@cals", calCount);
cmd.Parameters.Add("@dset", MsSqlCeSettingsConnector.CreateNewDefaultSettings(Parent));
cmd.Parameters.Add("@aset", MsSqlCeSettingsConnector.CreateNewAllowedSettings(Parent));
return MSSQLCEConn.ExecuteScalar<int>(cmd).Value;
}
}
public void CreateInitialLocalDB(string strConnectionString,bool isCreated)
{
try
{
strClientConnectionString = strConnectionString;
// sync = new SqlCeClientSyncProvider(strClientConnectionString);
clientSyncProvider = new SqlCeClientSyncProvider(strClientConnectionString);
if (!isCreated)
{
SqlCeEngine clientEngine = new SqlCeEngine(strClientConnectionString);
clientEngine.CreateDatabase();
clientEngine.Dispose();
tblCallTable = CreateCallTable();
tblLeadsTable = CreateLeadsTable();
tblCallBackTable = CreateCallBackTable();
tblDispositionTable = CreateDispositionTable();
}
else
{
tblCallTable = new SyncTable("Call");
tblCallTable.SyncDirection = SyncDirection.UploadOnly;
tblLeadsTable = new SyncTable("Leads");
tblLeadsTable.SyncDirection = SyncDirection.UploadOnly;
tblCallBackTable = new SyncTable("CallBack");
tblCallBackTable.SyncDirection = SyncDirection.UploadOnly;
//Creating Disposition Table (Added by Alpa)
tblDispositionTable = new SyncTable("Disposition");
tblDispositionTable.SyncDirection = SyncDirection.UploadOnly;
}
strClientConnectionString = strConnectionString;
// sync = new SqlCeClientSyncProvider(strClientConnectionString);
serverSyncProvider = new DbServerSyncProvider();
syncAgent = new SyncAgent();
// syncAgent.ServerSyncProvider = serverSyncProvider;
syncAgent.RemoteProvider = serverSyncProvider;
serverConnection = new SqlConnection(VMuktiAPI.VMuktiInfo.MainConnectionString);
serverSyncProvider.Connection = serverConnection;
serverSyncProvider.ApplyChangeFailed += new EventHandler<ApplyChangeFailedEventArgs>(serverSyncProvider_ApplyChangeFailed);
//syncAgent.ClientSyncProvider = clientSyncProvider;
syncAgent.LocalProvider = clientSyncProvider;
myGroup = new SyncGroup("DialerGroup");
tblCallTable.SyncGroup = myGroup;
tblLeadsTable.SyncGroup = myGroup;
tblCallBackTable.SyncGroup = myGroup;
tblDispositionTable.SyncGroup = myGroup;
syncAgent.Configuration.SyncTables.Add(tblCallTable);
syncAgent.Configuration.SyncTables.Add(tblLeadsTable);
syncAgent.Configuration.SyncTables.Add(tblCallBackTable);
syncAgent.Configuration.SyncTables.Add(tblDispositionTable);
CallAdapter = new SqlSyncAdapterBuilder();
CallAdapter.Connection = serverConnection;
CallAdapter.SyncDirection = SyncDirection.UploadOnly;
CallAdapter.TableName = "Call";
// CallAdapter.DataColumns.Add("ID");
CallAdapter.DataColumns.Add("LeadID");
CallAdapter.DataColumns.Add("CalledDate");
CallAdapter.DataColumns.Add("ModifiedDate");
CallAdapter.DataColumns.Add("ModifiedBy");
CallAdapter.DataColumns.Add("GeneratedBy");
CallAdapter.DataColumns.Add("StartDate");
CallAdapter.DataColumns.Add("StartTime");
CallAdapter.DataColumns.Add("DurationInSecond");
CallAdapter.DataColumns.Add("DespositionID");
CallAdapter.DataColumns.Add("CampaignID");
CallAdapter.DataColumns.Add("ConfID");
CallAdapter.DataColumns.Add("IsDeleted");
CallAdapter.DataColumns.Add("CallNote");
CallAdapter.DataColumns.Add("IsDNC");
CallAdapter.DataColumns.Add("IsGlobal");
CallAdapter.DataColumns.Add("RecordedFileName"); //For Recording File Name
CallAdapterSyncAdapter = CallAdapter.ToSyncAdapter();
CallAdapterSyncAdapter.DeleteCommand = null;
serverSyncProvider.SyncAdapters.Add(CallAdapterSyncAdapter);
LeadAdapter = new SqlSyncAdapterBuilder();
LeadAdapter.Connection = serverConnection;
LeadAdapter.SyncDirection = SyncDirection.UploadOnly;
LeadAdapter.TableName = "Leads";
LeadAdapter.DataColumns.Add("ID");
LeadAdapter.DataColumns.Add("PhoneNo");
LeadAdapter.DataColumns.Add("LeadFormatID");
LeadAdapter.DataColumns.Add("CreatedDate");
LeadAdapter.DataColumns.Add("CreatedBy");
LeadAdapter.DataColumns.Add("DeletedDate");
LeadAdapter.DataColumns.Add("DeletedBy");
LeadAdapter.DataColumns.Add("IsDeleted");
LeadAdapter.DataColumns.Add("ModifiedDate");
LeadAdapter.DataColumns.Add("ModifiedBy");
LeadAdapter.DataColumns.Add("DNCFlag");
LeadAdapter.DataColumns.Add("DNCBy");
LeadAdapter.DataColumns.Add("ListID");
LeadAdapter.DataColumns.Add("LocationID");
LeadAdapter.DataColumns.Add("RecycleCount");
LeadAdapter.DataColumns.Add("Status");
LeadAdapter.DataColumns.Add("IsGlobalDNC");
//LeadAdapter.DataColumns.Add("LastEditDate");
//LeadAdapter.DataColumns.Add("CreationDate");
LeadAdapterSyncAdapter = LeadAdapter.ToSyncAdapter();
LeadAdapterSyncAdapter.DeleteCommand = null;
LeadAdapterSyncAdapter.InsertCommand = null;
serverSyncProvider.SyncAdapters.Add(LeadAdapterSyncAdapter);
CallBackAdapter = new SqlSyncAdapterBuilder();
CallBackAdapter.Connection = serverConnection;
CallBackAdapter.SyncDirection = SyncDirection.UploadOnly;
CallBackAdapter.TableName = "CallBack";
CallBackAdapter.DataColumns.Add("ID");
CallBackAdapter.DataColumns.Add("CallID");
CallBackAdapter.DataColumns.Add("CallBackDate");
CallBackAdapter.DataColumns.Add("Comment");
CallBackAdapter.DataColumns.Add("IsPublic");
CallBackAdapter.DataColumns.Add("IsDeleted");
CallBackAdapterSyncAdapter = CallBackAdapter.ToSyncAdapter();
CallBackAdapterSyncAdapter.DeleteCommand = null;
serverSyncProvider.SyncAdapters.Add(CallBackAdapterSyncAdapter);
//Creating Disposition Table in sdf (Added by Alpa)
DispositionAdapter = new SqlSyncAdapterBuilder();
DispositionAdapter.Connection = serverConnection;
DispositionAdapter.SyncDirection = SyncDirection.UploadOnly;
DispositionAdapter.TableName = "Disposition";
DispositionAdapter.DataColumns.Add("ID");
DispositionAdapter.DataColumns.Add("DespositionName");
DispositionAdapter.DataColumns.Add("Description");
DispositionAdapter.DataColumns.Add("IsActive");
DispositionAdapter.DataColumns.Add("IsDeleted");
DispositionAdapter.DataColumns.Add("CreatedDate");
DispositionAdapter.DataColumns.Add("CreatedBy");
DispositionAdapter.DataColumns.Add("ModifiedDate");
DispositionAdapter.DataColumns.Add("ModifiedBy");
DispositionAdapterSyncAdapter = DispositionAdapter.ToSyncAdapter();
DispositionAdapterSyncAdapter.DeleteCommand = null;
DispositionAdapterSyncAdapter.InsertCommand = null;
serverSyncProvider.SyncAdapters.Add(DispositionAdapterSyncAdapter);
ce = new SqlCeConnection(strClientConnectionString);
ce.Open();
CheckPreviousSyncWithServer();
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}
// SqlCeConnection ce = null;
public void CreateInitialLocalDB(string strConnectionString, bool isCreated)
{
try
{
strClientConnectionString = strConnectionString;
// sync = new SqlCeClientSyncProvider(strClientConnectionString);
clientSyncProvider = new SqlCeClientSyncProvider(strClientConnectionString);
if (!isCreated)
{
SqlCeEngine clientEngine = new SqlCeEngine(strClientConnectionString);
clientEngine.CreateDatabase();
clientEngine.Dispose();
tblCallTable = CreateCallTable();
tblLeadsTable = CreateLeadsTable();
tblCallBackTable = CreateCallBackTable();
}
else
{
tblCallTable = new SyncTable("Call");
tblCallTable.SyncDirection = SyncDirection.UploadOnly;
tblLeadsTable = new SyncTable("Leads");
tblLeadsTable.SyncDirection = SyncDirection.UploadOnly;
tblCallBackTable = new SyncTable("CallBack");
tblCallBackTable.SyncDirection = SyncDirection.UploadOnly;
}
strClientConnectionString = strConnectionString;
// sync = new SqlCeClientSyncProvider(strClientConnectionString);
serverSyncProvider = new DbServerSyncProvider();
syncAgent = new SyncAgent();
// syncAgent.ServerSyncProvider = serverSyncProvider;
syncAgent.RemoteProvider = serverSyncProvider;
serverConnection = new SqlConnection(VMuktiInfo.MainConnectionString);
serverSyncProvider.Connection = serverConnection;
//SqlCommand cmdAnchor = new SqlCommand();
// cmdAnchor.CommandType = CommandType.Text;
// cmdAnchor.CommandText = "SELECT @@DBTS";
// serverSyncProvider.SelectNewAnchorCommand = cmdAnchor;
// SqlCommand cmdClientId = new SqlCommand();
// cmdClientId.CommandType = CommandType.Text;
// cmdClientId.CommandText = "SELECT 1";
// serverSyncProvider.SelectClientIdCommand = cmdClientId;
//syncAgent.ClientSyncProvider = clientSyncProvider;
syncAgent.LocalProvider = clientSyncProvider;
myGroup = new SyncGroup("DialerGroup");
tblCallTable.SyncGroup = myGroup;
tblLeadsTable.SyncGroup = myGroup;
tblCallBackTable.SyncGroup = myGroup;
//syncAgent.SyncTables.Add(tblCallTable);
//syncAgent.SyncTables.Add(tblLeadsTable);
//syncAgent.SyncTables.Add(tblCallBackTable);
syncAgent.Configuration.SyncTables.Add(tblCallTable);
syncAgent.Configuration.SyncTables.Add(tblLeadsTable);
syncAgent.Configuration.SyncTables.Add(tblCallBackTable);
CallAdapter = new SqlSyncAdapterBuilder();
CallAdapter.Connection = serverConnection;
CallAdapter.SyncDirection = SyncDirection.UploadOnly;
CallAdapter.TableName = "Call";
CallAdapter.DataColumns.Add("ID");
CallAdapter.DataColumns.Add("LeadID");
CallAdapter.DataColumns.Add("CalledDate");
CallAdapter.DataColumns.Add("ModifiedDate");
CallAdapter.DataColumns.Add("ModifiedBy");
CallAdapter.DataColumns.Add("GeneratedBy");
CallAdapter.DataColumns.Add("StartDate");
CallAdapter.DataColumns.Add("StartTime");
CallAdapter.DataColumns.Add("DurationInSecond");
CallAdapter.DataColumns.Add("DespositionID");
CallAdapter.DataColumns.Add("CampaignID");
CallAdapter.DataColumns.Add("ConfID");
CallAdapter.DataColumns.Add("IsDeleted");
CallAdapter.DataColumns.Add("CallNote");
CallAdapter.DataColumns.Add("IsDNC");
CallAdapter.DataColumns.Add("IsGlobal");
CallAdapterSyncAdapter = CallAdapter.ToSyncAdapter();
CallAdapterSyncAdapter.DeleteCommand = null;
serverSyncProvider.SyncAdapters.Add(CallAdapterSyncAdapter);
LeadAdapter = new SqlSyncAdapterBuilder();
LeadAdapter.Connection = serverConnection;
LeadAdapter.SyncDirection = SyncDirection.UploadOnly;
LeadAdapter.TableName = "Leads";
LeadAdapter.DataColumns.Add("ID");
LeadAdapter.DataColumns.Add("PhoneNo");
LeadAdapter.DataColumns.Add("LeadFormatID");
LeadAdapter.DataColumns.Add("CreatedDate");
LeadAdapter.DataColumns.Add("CreatedBy");
LeadAdapter.DataColumns.Add("DeletedDate");
LeadAdapter.DataColumns.Add("DeletedBy");
LeadAdapter.DataColumns.Add("IsDeleted");
LeadAdapter.DataColumns.Add("ModifiedDate");
LeadAdapter.DataColumns.Add("ModifiedBy");
LeadAdapter.DataColumns.Add("DNCFlag");
LeadAdapter.DataColumns.Add("DNCBy");
LeadAdapter.DataColumns.Add("ListID");
LeadAdapter.DataColumns.Add("LocationID");
LeadAdapter.DataColumns.Add("RecycleCount");
LeadAdapter.DataColumns.Add("Status");
LeadAdapter.DataColumns.Add("IsGlobalDNC");
//LeadAdapter.DataColumns.Add("LastEditDate");
//LeadAdapter.DataColumns.Add("CreationDate");
LeadAdapterSyncAdapter = LeadAdapter.ToSyncAdapter();
LeadAdapterSyncAdapter.DeleteCommand = null;
LeadAdapterSyncAdapter.InsertCommand = null;
//LeadAdapterSyncAdapter.ColumnMappings.Add("Status", "Status");
//LeadAdapterSyncAdapter.ColumnMappings.Add("DNCFlag", "DNCFlag");
//LeadAdapterSyncAdapter.ColumnMappings.Add("DNCBy", "DNCBy");
serverSyncProvider.SyncAdapters.Add(LeadAdapterSyncAdapter);
CallBackAdapter = new SqlSyncAdapterBuilder();
CallBackAdapter.Connection = serverConnection;
CallBackAdapter.SyncDirection = SyncDirection.UploadOnly;
CallBackAdapter.TableName = "CallBack";
CallBackAdapter.DataColumns.Add("ID");
CallBackAdapter.DataColumns.Add("CallID");
CallBackAdapter.DataColumns.Add("CallBackDate");
CallBackAdapter.DataColumns.Add("Comment");
CallBackAdapter.DataColumns.Add("IsPublic");
CallBackAdapter.DataColumns.Add("IsDeleted");
CallBackAdapterSyncAdapter = CallBackAdapter.ToSyncAdapter();
CallBackAdapterSyncAdapter.DeleteCommand = null;
serverSyncProvider.SyncAdapters.Add(CallBackAdapterSyncAdapter);
CheckPreviousSyncWithServer();
}
catch (Exception ex)
{
VMuktiAPI.VMuktiHelper.ExceptionHandler(ex, "CreateInitialLocalDB()", "ClsUserDataService.cs");
//MessageBox.Show("CreateInitialLocalDB: " + ex.Message);
}
}
protected override void DbCreateDatabase(DbConnection connection, int? timeOut, StoreItemCollection storeItemCollection)
{
#region Check arguments
ADP1.CheckArgumentNull(connection, "Connection");
ADP1.CheckArgumentNull(storeItemCollection, "StoreItemCollection");
#endregion
// Validate that connection is a SqlCeConnection.
ValidateConnection(connection);
// We don't support create/delete database operations inside a transaction as they can't be rolled back.
if (InTransactionScope())
{
throw ADP1.CreateDatabaseNotAllowedWithinTransaction();
}
if (_isLocalProvider)
{
var engine = new SqlCeEngine(connection.ConnectionString);
engine.CreateDatabase();
engine.Dispose();
}
else
{
try
{
Type rdpType;
// If we are working with RDP, then we will need to invoke the APIs through reflection.
var engine = RemoteProviderHelper.GetRemoteSqlCeEngine(connection.ConnectionString, out rdpType);
Debug.Assert(engine != null);
// Invoke the required method on SqlCeEngine.
var mi = rdpType.GetMethod("CreateDatabase", new[] { typeof(int?) });
Debug.Assert(mi != null);
// We will pass 'timeout' to RDP, this will be used as timeout period for connecting and executing on TDSServer.
mi.Invoke(engine, new object[] { timeOut });
}
catch (Exception e)
{
throw e.GetBaseException();
}
}
// Create the command object depending on provider.
var command = connection.CreateCommand();
// Create the command texts from StoreItemCollection.
var commandTextCollection = SqlDdlBuilder.CreateObjectsScript(storeItemCollection, false);
DbTransaction transaction = null;
try
{
// Open the connection.
connection.Open();
// Open a transaction and attach to the command.
transaction = connection.BeginTransaction();
command.Transaction = transaction;
// Execute each statement.
foreach (var text in commandTextCollection)
{
command.CommandText = text;
command.ExecuteNonQuery();
}
// Commit the transaction.
transaction.Commit();
}
catch (Exception e)
{
if (transaction != null)
{
// Rollback the transaction.
transaction.Rollback();
}
// Throw IOE with SqlCeException embedded as inner exception.
throw new InvalidOperationException(EntityRes.GetString(EntityRes.IncompleteDatabaseCreation), e);
}
finally
{
// Close connection and cleanup objects.
if (command != null)
{
command.Dispose();
}
if (transaction != null)
{
transaction.Dispose();
}
if (connection != null)
{
connection.Close();
}
}
}
private void button2_Click(object sender, EventArgs e)
{
File.Delete("Test.sdf");
SqlCeEngine engine = new SqlCeEngine(connString);
engine.CreateDatabase();
engine.Dispose();
try
{
if (conn == null)
{
conn = new SqlCeConnection(connString);
conn.Open();
}
SqlCeCommand cmd = conn.CreateCommand();
cmd.CommandText = @"CREATE TABLE banklogs (
idx INT IDENTITY NOT NULL PRIMARY KEY ,
date DATETIME NOT NULL ,
category NVARCHAR( 20 ) NOT NULL ,
name NVARCHAR( 20 ) NOT NULL ,
expense INT NOT NULL ,
income INT NOT NULL ,
bank NVARCHAR( 20 ) NOT NULL ,
memo NVARCHAR( 100 ) NOT NULL ,
cat INT
);";
cmd.ExecuteNonQuery();
}
catch (Exception E)
{
MessageBox.Show(E.Message);
conn.Close();
conn.Dispose();
conn = null;
}
}
public static void createDatabase(string filename, string path)
{
SqlCeEngine Engine = new SqlCeEngine(String.Format(@"DataSource={0}\{1}.sdf", path, filename));
Engine.CreateDatabase();
Engine.Dispose();
}
private int initDb()
{
//sjekker om databasen allerede eksisterer
if (File.Exists(fileName))
{
Logger.Info("Fant eksisterende database", thismodule);
return (int)dbStatus.EXISTING;
}
else
{
Logger.Info("Oppretter ny database...", thismodule);
//Dersom den ikke eksisterer opprettes databasen.
SqlCeEngine en = new SqlCeEngine(ConnectionString);
try
{
en.CreateDatabase();
}
catch (Exception) { }
finally
{
en.Dispose();
}
//lager tabeller i databasen vi nettop opprettet.
int cT = createTables();
if (cT != (int)dbStatus.SUCCESS) //bruker != success slik at dbstatus kan kodes med flere forskjellige error koder senere.
{
//TODO: gi melding til bruker om at noe gikk galt - se log.
}
Logger.Info("...Success", thismodule);
return (int)dbStatus.NEW;
}
}
public int createdatabase()
{
if (this.isExists())
{
MessageBox.Show("DataBase exists already");
return 0;
}
//File.Delete("ACSystem.sdf");
SqlCeEngine engine = new SqlCeEngine(connString);
engine.CreateDatabase();
MessageBox.Show("DataBase has been created sucessfully");
engine.Dispose();
return 1;
}
/// <summary>
/// Gets the persistent LM connection.
/// </summary>
/// <param name="testContext">The test context.</param>
/// <param name="repositoryName">Name of the repository (Hostname of the Server).</param>
/// <param name="LMId">The LM id.</param>
/// <param name="callback">The callback.</param>
/// <param name="connectionType">Type of the connection.</param>
/// <param name="standAlone">if set to <c>true</c> a stand alone user will be created.</param>
/// <returns></returns>
/// <remarks>Documented by Dev10, 2008-08-01</remarks>
public static IDictionary GetPersistentLMConnection(TestContext testContext, string repositoryName, int LMId, GetLoginInformation callback, string connectionType, bool standAlone, string password, bool isProtected)
{
string connectionString = string.Empty;
ConnectionStringStruct ConnectionString;
string type = connectionType == "" ? (string)testContext.DataRow["Type"] : connectionType;
bool IsValid = (bool)testContext.DataRow["IsValid"];
switch (type.ToLower())
{
case "file":
#region ODX Tests
if (repositoryName == string.Empty) //Not persistent
{
repositoryName = MachineName;
ConnectionString = new ConnectionStringStruct(DatabaseType.Xml, TestDic, false);
}
else //Persistent (During Unit Test)
{
string persistentPath;
persistentPath = Path.GetTempPath() + repositoryName + Helper.OdxExtension;
cleanupQueue.Enqueue(persistentPath);
ConnectionString = new ConnectionStringStruct(DatabaseType.Xml, persistentPath, false);
}
#endregion
break;
case "pgsql":
#region PgSQL Tests
repositoryName = MachineName.ToLower();
if (testContext.DataRow["ConnectionString"] != System.DBNull.Value)
{
connectionString = (string)testContext.DataRow["ConnectionString"];
connectionString = connectionString.Replace("DYNAMIC", repositoryName);
//Only create DataBase if it was not yet created, so only for the first time, one database for all unit tests in this run
if (!DBCreated)
{
if (dbAccessByOtherUser)
throw new Exception("DB is being accessed by other users");
else
{
try
{
DropDB(repositoryName);
}
catch (NpgsqlException exp)
{
if (exp.Code == "55006") //Pgsql error code for: Database is accessed by other users
{
dbAccessByOtherUser = true;
throw new Exception("DB is being accessed by other users");
}
}
CreateDB(repositoryName, testContext, isProtected);
DBCreated = true;
tmpRepository = repositoryName;
}
}
}
else
throw new Exception("PGSQL connection string could not be read from unit test database.");
if (LMId < 0) //Persistent (During Test Run)
LMId = AddNewLM(repositoryName, isProtected);
ConnectionString = new ConnectionStringStruct(DatabaseType.PostgreSQL, connectionString, LMId);
#endregion
break;
case "sqlce":
#region SqlCe Tests
if (repositoryName == string.Empty) //Not persistent
{
repositoryName = MachineName;
ConnectionString = new ConnectionStringStruct(DatabaseType.MsSqlCe, TestDicSqlCE, false);
}
else //Persistent (During Unit Test)
{
string persistentPath;
persistentPath = Path.GetTempPath() + repositoryName + Helper.EmbeddedDbExtension;
cleanupQueue.Enqueue(persistentPath);
ConnectionString = new ConnectionStringStruct(DatabaseType.MsSqlCe, persistentPath, false);
}
if (password != string.Empty)
{
ConnectionString.ProtectedLm = true;
ConnectionString.Password = password;
}
if (LMId < 0)
{
string sqlCeConnString = GetFullSqlCeConnectionString(ConnectionString);
using (SqlCeEngine clientEngine = new SqlCeEngine(sqlCeConnString))
{
clientEngine.CreateDatabase();
clientEngine.Dispose();
}
using (SqlCeConnection con = new SqlCeConnection(sqlCeConnString))
{
con.Open();
SqlCeTransaction transaction = con.BeginTransaction();
string tmp = Helper.GetMsSqlCeScript();
string[] msSqlScriptArray = Helper.GetMsSqlCeScript().Split(';'); //Split the whole DB-Script into single commands (SQL-CE can not execute multiple queries)
foreach (string sqlCommand in msSqlScriptArray)
{
if (sqlCommand.TrimStart(' ', '\r', '\n').StartsWith("--") || sqlCommand.TrimStart(' ', '\r', '\n').Length < 5)
continue;
using (SqlCeCommand cmd = con.CreateCommand())
{
cmd.CommandText = sqlCommand;
cmd.ExecuteNonQuery();
}
}
int cat_id;
using (SqlCeCommand cmd = con.CreateCommand())
{
cmd.CommandText = "SELECT id FROM Categories WHERE global_id=@cat_id;";
cmd.Parameters.Add("@cat_id", 1);
cat_id = Convert.ToInt32(cmd.ExecuteScalar());
}
using (SqlCeCommand cmd = con.CreateCommand())
{
cmd.CommandText = "INSERT INTO LearningModules (guid, title, categories_id, default_settings_id, allowed_settings_id, licence_key, content_protected, cal_count) " +
"VALUES (@guid, @title, @cat_id, @dset, @aset, @lk, @cp, @cals);";
cmd.Parameters.Add("@guid", new Guid().ToString());
cmd.Parameters.Add("@title", "eDB test title");
cmd.Parameters.Add("@cat_id", cat_id);
cmd.Parameters.Add("@lk", "ACDED-LicenseKey-DEDAF");
cmd.Parameters.Add("@cp", password == string.Empty ? 0 : 1);
cmd.Parameters.Add("@cals", 1);
cmd.Parameters.Add("@dset", MsSqlCeCreateNewSettings(sqlCeConnString));
cmd.Parameters.Add("@aset", MsSqlCeCreateNewAllowedSettings(sqlCeConnString));
cmd.ExecuteNonQuery();
}
using (SqlCeCommand cmd = con.CreateCommand())
{
cmd.CommandText = "SELECT @@IDENTITY;";
LMId = Convert.ToInt32(cmd.ExecuteScalar());
}
transaction.Commit();
}
}
ConnectionString.LmId = LMId;
#endregion
break;
default:
throw new Exception("TestInfrastructure Class: conditions where set which are not applicable to the current db connection infrastructure");
}
IUser user;
if (callback == null)
user = UserFactory.Create((GetLoginInformation)GetTestUser, ConnectionString, (DataAccessErrorDelegate)delegate { return; }, standAlone);
else
user = UserFactory.Create(callback, ConnectionString, (DataAccessErrorDelegate)delegate { return; }, standAlone);
return user.Open();
}
public SQLCEDatabase()
{
try
{
IPathManager pathManager = ServiceRegistration.Get<IPathManager>();
string dataDirectory = pathManager.GetPath("<DATABASE>");
string databaseFile = Path.Combine(dataDirectory, DEFAULT_DATABASE_FILE);
int databaseSize = INITIAL_MAX_DATABASE_SIZE;
FileInfo databaseFileInfo = new FileInfo(databaseFile);
if (databaseFileInfo.Exists)
{
int bufferFileSize = (int) (databaseFileInfo.Length/(1024*1024)) + DATABASE_SIZE_BUFFER;
if (bufferFileSize > databaseSize)
databaseSize = bufferFileSize;
}
_connectionString = "Data Source='" + databaseFile + "'; Default Lock Timeout=" + LOCK_TIMEOUT + "; Max Buffer Size = " + MAX_BUFFER_SIZE + "; Max Database Size = " + databaseSize;
SqlCeEngine engine = new SqlCeEngine(_connectionString);
if (File.Exists(databaseFile))
CheckUpgrade(engine);
else
{
Directory.CreateDirectory(dataDirectory);
engine.CreateDatabase();
engine.Dispose();
}
}
catch (Exception e)
{
ServiceRegistration.Get<ILogger>().Critical("Error establishing database connection", e);
throw;
}
}
