public InputScreen()
{
InitializeComponent();
//Initialize DataSet and SqlCeConnection.
this.m_oDs = new DataSet();
this.m_sDataSource = "Connection Type=file ; Initial Catalog=/Storage Card/Northwind/NorthwindEF; User=sa; Password=;";
//this.m_sDataSource = "\\my documents\\TestDB1.sdf";
this.m_oCn = new EfzConnection(m_sDataSource); ;
// Create the SELECT Command
string sSelectSQL = "SELECT p.ProductID, c.CategoryID, c.CategoryName, p.ProductName, " +
" p.UnitPrice, p.UnitsInStock, p.UnitsOnOrder, p.ReorderLevel " +
" FROM Products p INNER JOIN Categories c ON p.CategoryID = c.CategoryID " +
" ORDER BY c.CategoryName, p.ProductName";
this.m_oDA = new EfzDataAdapter();
this.m_oDA.SelectCommand = new EfzCommand(sSelectSQL);
this.m_oDA.SelectCommand.Connection = this.m_oCn;
// Create the UPDATE Command
string sUpdateSQL = "UPDATE Products SET UnitPrice = @UnitPrice, UnitsInStock = @UnitsInStock, UnitsOnOrder = @UnitsOnOrder, ReorderLevel = @ReorderLevel WHERE ProductID = @ProductID";
this.m_oDA.UpdateCommand = new EfzCommand(sUpdateSQL);
this.m_oDA.UpdateCommand.Connection = this.m_oCn;
this.m_oDA.UpdateCommand.Parameters.Add(new EfzParameter("@UnitPrice", EfzType.Money, 8, "UnitPrice"));
this.m_oDA.UpdateCommand.Parameters.Add(new EfzParameter("@UnitsInStock", EfzType.SmallInt, 2, "UnitsInStock"));
this.m_oDA.UpdateCommand.Parameters.Add(new EfzParameter("@UnitsOnOrder", EfzType.SmallInt, 2, "UnitsOnOrder"));
this.m_oDA.UpdateCommand.Parameters.Add(new EfzParameter("@ReorderLevel", EfzType.SmallInt, 2, "ReorderLevel"));
this.m_oDA.UpdateCommand.Parameters.Add(new EfzParameter("@ProductID", EfzType.Int, "PRODUCTID"));
LoadData(false);
}
private void btnMem_Click(object sender, RoutedEventArgs e)
{
string connectionString = "connection type=MEMORY; initial catalog=TestDb; user=SA; password="******"CREATE TABLE Test(ID int, Name varchar(100));";
EfzCommand cmd = conn.CreateCommand();
cmd.CommandText = sql;
cmd.ExecuteNonQuery();
sql = "INSERT INTO Test(ID , Name ) VALUES(1,'Car');";
cmd.CommandText = sql;
cmd.ExecuteNonQuery();
sql = "SELECT * FROM TEST;";
cmd.CommandText = sql;
EfzDataReader reader = cmd.ExecuteReader();
reader.Read();
tbkText.Text = String.Format("ID = {0}, Name = {1} ", reader.GetInt32(0), reader.GetString(1));
}
}
public static bool EnsureDBConnection()
{
if(m_conDB != null) { //already existing
if(m_conDB.State != ConnectionState.Open) {
m_conDB.Open();
}
return (true);
}
if(!m_bDidCheckFiles) {
if(!EnsureDBInIsolatedStorage()) {
return (false);
}
}
m_conDB = new EfzConnection(m_strConnection);
m_conDB.Open();
return (true);
}
static void Main(string[] args)
{
string connString = "Connection Type=File ; Initial Catalog=/Storage Card/TestDB; User=sa; Password=;"; //for file DB
//string connString = "Connection Type=Memory ; Initial Catalog=TestDB; User=sa; Password=;";
using (DbConnection conn = new EfzConnection(connString))
{
conn.Open();
DbCommand command = conn.CreateCommand();
command.CommandText = "CREATE TABLE Test(ID INT PRIMARY KEY, Name VARCHAR(100));";
command.ExecuteNonQuery();
command.CommandText = "INSERT INTO Test(ID , Name) VALUES(@ID , @Name);";
DbParameter id = command.CreateParameter();
id.ParameterName = "@ID";
id.Value = 1;
command.Parameters.Add(id);
DbParameter name = command.CreateParameter();
name.ParameterName = "@NAME";
name.Value = "Van";
command.Parameters.Add(name);
command.ExecuteNonQuery();
id.Value = 2;
name.Value = "Car";
command.ExecuteNonQuery();
command.CommandText = "SELECT * FROM TEST;";
DbDataReader reader = command.ExecuteReader();
while (reader.Read())
{
System.Console.WriteLine(String.Format("ID= {0} , Name= {1}",
reader.GetInt32(0), reader.GetString(1)));
}
Console.WriteLine("Press Any Key to Continue...");
}
}
public static void ClrFunctionTest()
{
string connString = "Connection Type=Memory ;Initial Catalog=CLRSampleDB; User=sa; Password=;";
using (DbConnection cnn = new EfzConnection(connString))
{
cnn.Open();
using (DbCommand cmd = cnn.CreateCommand())
{
string sql = "CREATE FUNCTION add_num(x INT, y INT)\n" +
"RETURNS INT\n NO SQL\n" +
"LANGUAGE DOTNET\n EXTERNAL NAME 'ClrRoutines:EffiProz.Samples.ClrRoutines.Add'";
cmd.CommandText = sql;
cmd.ExecuteNonQuery();
cmd.CommandText = "SELECT add_num(3,4) from dual;";
int result = (int)cmd.ExecuteScalar();
Console.WriteLine("Result: {0}", result);
}
}
}
internal void CreateDatabase(string filename)
{
var dbname = "Test_" + Guid.NewGuid();
var connectionString = string.Format("Connection Type=File; Initial Catalog={0}; User=sa; Password=;", dbname);
try
{
var content = File.ReadAllText(filename).Replace("\r\n\\", Delimiter.ToString())
.Replace(";\r\n", Delimiter.ToString())
.Replace("*/\r\n", "*/" + Delimiter)
.Replace("\r\n", " ");
Console.WriteLine(content.Length);
using (DbConnection conn = new EfzConnection(connectionString))
{
conn.ConnectionString = connectionString;
conn.Open();
var commands = (from item in content.Split(Delimiter)
let trim = item.Trim()
where !string.IsNullOrEmpty(trim) && !(trim.StartsWith("/*") && trim.EndsWith("*/"))
select trim).ToArray();
foreach (var processingCommand in commands)
{
var command = conn.CreateCommand();
command.CommandText = processingCommand;
command.ExecuteNonQuery();
}
}
}
catch (Exception e)
{
Trace.WriteLine("Error: " + e.Message);
}
}
public EfzConnectionWrapper(string connectionString)
{
m_connection = new EfzConnection(connectionString);
}
/// <summary>
/// Retrieves the cached connection object.
/// </summary>
/// <param name="connectionName">Connection name in the configuration file.</param>
/// <returns>A connection object for this specific database.</returns>
protected EfzConnection GetConnection(string connectionName)
{
// Creates an ADO.NET connection to the database, if not created yet.
if (!Connections.ContainsKey(connectionName))
{
var section = (ConnectionStringsSection)ConfigurationManager.GetSection("connectionStrings");
foreach (var entry in section.ConnectionStrings.Cast<ConnectionStringSettings>()
.Where(entry => entry.Name == connectionName))
{
var regex = new Regex(InitialCatalogPattern, RegexOptions.IgnorePatternWhitespace | RegexOptions.Multiline | RegexOptions.IgnoreCase | RegexOptions.Compiled);
var match = regex.Match(entry.ConnectionString);
var filename = match.Groups["InitialCatalog"].Value;
var dbname = "Test_" + Guid.NewGuid();
var connectionString = entry.ConnectionString.Replace(filename, dbname);
var conn = Connections[connectionName] = new EfzConnection(connectionString);
// Read the script to initialize database.
var content = File.ReadAllText(filename).Replace("\r\n\\", Delimiter.ToString())
.Replace(";\r\n", Delimiter.ToString())
.Replace("*/\r\n", "*/" + Delimiter)
.Replace("\r\n", " ");
var commands = (from item in content.Split(Delimiter)
let trim = item.Trim()
where
!string.IsNullOrEmpty(trim) &&
!(trim.StartsWith("/*") && trim.EndsWith("*/"))
select trim).ToArray();
// Initialize database.
try
{
conn.Open();
foreach (var processingCommand in commands)
{
var command = conn.CreateCommand();
command.CommandText = processingCommand;
command.ExecuteNonQuery();
}
}
finally
{
//conn.Close();
}
Trace.WriteLine("Test database created: " + dbname);
break;
}
// If we failed to create a connection, then throw an exception.
if (!Connections.ContainsKey(connectionName))
throw new ApplicationException("There is no connection string defined in app.config file.");
}
return Connections[connectionName];
}
public void ADOCommandBuilderTest1()
{
string connString = @"Connection Type=File ; Initial Catalog=D:\T2; User=sa; Password=;";
string sql = "CREATE TABLE TEST(ID INT,ID2 INT, NAME VARCHAR(100),ID3 INT,ID4 INT DEFAULT 5, PRIMARY KEY(ID), UNIQUE (ID2), UNIQUE (ID3,ID4));";
using (EfzConnection conn = new EfzConnection(connString))
{
DbCommand command = conn.CreateCommand();
command.CommandText = sql;
conn.Open();
int count = command.ExecuteNonQuery();
//command.CommandText = "INSERT INTO TEST(ID , ID2, NAME ) VALUES(1, 100,'irantha'); INSERT INTO TEST(ID ,ID2, NAME ) VALUES(2, 500,'subash');";
//count = command.ExecuteNonQuery();
//Assert.AreEqual(count, 1);
DataTable tb = conn.GetSchema("Tables", new string[] { null, null, null, "TABLE" });
Assert.IsNotNull(tb);
string tableName = tb.Rows[0]["Table_Name"].ToString();
Assert.AreEqual(tableName, "TEST");
Assert.AreEqual(1, tb.Rows.Count);
tb = conn.GetSchema("Columns", new string[] { null, "PUBLIC", "TEST", "ID" });
Assert.AreEqual(1, tb.Rows.Count);
tb = conn.GetSchema("Columns", new string[] { null, "PUBLIC", "TEST", "NAME" });
Assert.AreEqual(1, tb.Rows.Count);
tb = conn.GetSchema("PRIMARYKEYS", new string[] { null, "PUBLIC", "TEST" });
Assert.AreEqual(1, tb.Rows.Count);
tb = conn.GetSchema("INDEXES", new string[] { null, "PUBLIC", "TEST" });
Assert.AreEqual(3, tb.Rows.Count);
tb = conn.GetSchema("INDEXES", new string[] { null, "PUBLIC", "TEST", null, "true" });
Assert.AreEqual(3, tb.Rows.Count);
tb = conn.GetSchema("SCHEMAS");
Assert.AreEqual(3, tb.Rows.Count);
tb = conn.GetSchema("TYPES");
Assert.IsTrue(tb.Rows.Count > 0);
tb = conn.GetSchema("DataTypes");
Assert.IsTrue(tb.Rows.Count > 0);
//PrintDT(tb);
command = conn.CreateCommand();
command.CommandText = "SELECT * FROM TEST";
DbDataReader reader = command.ExecuteReader(CommandBehavior.KeyInfo);
DataTable columnSchemaTbl = reader.GetSchemaTable();
Assert.IsTrue((bool)columnSchemaTbl.Rows[0]["IsKey"]);
Assert.IsTrue((bool)columnSchemaTbl.Rows[0]["IsUnique"]);
Assert.IsFalse((bool)columnSchemaTbl.Rows[1]["IsKey"]);
Assert.IsTrue((bool)columnSchemaTbl.Rows[1]["IsUnique"]);
Assert.IsFalse((bool)columnSchemaTbl.Rows[2]["IsUnique"]);
Assert.IsFalse((bool)columnSchemaTbl.Rows[3]["IsUnique"]);
Assert.IsFalse((bool)columnSchemaTbl.Rows[2]["IsKey"]);
Assert.IsFalse((bool)columnSchemaTbl.Rows[3]["IsKey"]);
Assert.IsFalse((bool)columnSchemaTbl.Rows[4]["IsUnique"]);
Assert.IsFalse((bool)columnSchemaTbl.Rows[4]["IsKey"]);
Assert.AreEqual("5", columnSchemaTbl.Rows[4][SchemaTableOptionalColumn.DefaultValue]);
command = conn.CreateCommand();
command.CommandText = "SELECT * FROM TEST";
reader = command.ExecuteReader();
columnSchemaTbl = reader.GetSchemaTable();
Assert.IsFalse((bool)columnSchemaTbl.Rows[0]["IsKey"]);
Assert.IsFalse((bool)columnSchemaTbl.Rows[1]["IsKey"]);
Assert.IsFalse((bool)columnSchemaTbl.Rows[1]["IsUnique"]);
tb = new DataTable("Test");
tb.Load(reader);
tb = conn.GetSchema("METADATACOLLECTIONS");
Assert.IsTrue(tb.Rows.Count > 0);
tb = conn.GetSchema("CharacterSets");
Assert.IsTrue(tb.Rows.Count > 0);
tb = conn.GetSchema("CheckConstraints");
Assert.IsTrue(tb.Rows.Count > 0);
tb = conn.GetSchema("Collations");
Assert.IsTrue(tb.Rows.Count > 0);
tb = conn.GetSchema("Domains");
Assert.IsTrue(tb.Rows.Count > 0);
tb = conn.GetSchema("ColumnPrivilages");
Assert.IsTrue(tb.Rows.Count > 0);
tb = conn.GetSchema("PROCEDURES");
Assert.IsTrue(tb.Rows.Count > 0);
tb = conn.GetSchema("PROCEDUREPARAMETERS");
Assert.IsTrue(tb.Rows.Count > 0);
tb = conn.GetSchema("TABLEPRIVILEGES");
Assert.IsTrue(tb.Rows.Count > 0);
tb = conn.GetSchema("TableConstraints");
Assert.IsTrue(tb.Rows.Count > 0);
tb = conn.GetSchema("TRIGGERS", new string[] { });
Assert.IsTrue(tb.Rows.Count == 0);
tb = conn.GetSchema("VIEWS");
int oldViewCount = tb.Rows.Count;
command = conn.CreateCommand();
command.CommandText = "CREATE VIEW v1 AS SELECT ID,NAME FROM TEST";
command.ExecuteNonQuery();
tb = conn.GetSchema("VIEWS");
Assert.IsTrue(tb.Rows.Count == oldViewCount + 1);
tb = conn.GetSchema("ViewColumns");
Assert.IsTrue(tb.Rows.Count > 0);
command = conn.CreateCommand();
command.CommandText = "CREATE TABLE TEST2(IDD INT,IDD2 INT, NAME VARCHAR(100), FOREIGN KEY(IDD2)" +
" REFERENCES TEST(ID));";
command.ExecuteNonQuery();
tb = conn.GetSchema("EXPORTEDKEYS");
Assert.IsTrue(tb.Rows.Count == 1);
tb = conn.GetSchema("IMPORTEDKEYS");
Assert.IsTrue(tb.Rows.Count == 1);
tb = conn.GetSchema("FOREIGNKEYS");
Assert.IsTrue(tb.Rows.Count == 1);
tb = conn.GetSchema("ForeignKeyColumns");
Assert.IsTrue(tb.Rows.Count > 0);
tb = conn.GetSchema("RESERVEDWORDS");
Assert.IsTrue(tb.Rows.Count > 0);
tb = conn.GetSchema("RESTRICTIONS");
Assert.IsTrue(tb.Rows.Count > 0);
tb = conn.GetSchema("FUNCTIONS");
tb = conn.GetSchema("FunctionParameters");
// Assert.IsTrue(tb.Rows.Count> 0);
// PrintDT(tb);
}
}
public EfzConnectionWrapper(string connectionString)
{
m_connection = new EfzConnection(connectionString);
}
private void CloseInternalConnection()
{
if (currentConnection != null)
{
if (currentConnection.State == ConnectionState.Open ||
currentConnection.State == ConnectionState.Fetching ||
currentConnection.State == ConnectionState.Executing ||
currentConnection.State == ConnectionState.Connecting)
{
currentConnection.Close();
}
currentConnection = null;
}
}
