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); } }
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; } }