Exemplo n.º 1
0
        public void buildAccessFile()
        {
            if (File.Exists(accessFilePath))
                File.Delete(accessFilePath);

            /* NOTE */
            //COM MS ADO EXT and disable embeded type
            /* NOTE */

            CatalogClass catalog = new CatalogClass();
            catalog.Create("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + accessFilePath);

            //Build tables
            library.oledbConn accessFile = new library.oledbConn(this.accessFilePath, "mdb");

            accessFile.execute("create table Sites "
            + " ( "
            + " [siteId]    integer,"
            + " [siteName]  TEXT"
            + " ) "
            );

            accessFile.execute("create table Data"
            + " ( "
            + " [dataId]    AUTOINCREMENT(1, 1) PRIMARY KEY,"
            + " [siteId]    integer,"
            + " [_weekNum]  integer,"
            + " [_year]     integer,"
            + " [_calValue] TEXT"
            + " ) "
            );

            accessFile.terminate();
        }
Exemplo n.º 2
0
        public void CreateDatabase([JetBrains.Annotations.NotNull] string databaseName, bool   deleteIfExists = false)
        {
            if (databaseName == null) throw new ArgumentNullException("databaseName");

            databaseName = databaseName.Trim();

            if (!databaseName.ToLower().EndsWith(".mdb"))
                databaseName += ".mdb";

            if (File.Exists(databaseName))
            {
                if (!deleteIfExists)
                    return;
                File.Delete(databaseName);
            }

            var connectionString = string.Format(
                @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};Locale Identifier=1033;Jet OLEDB:Engine Type=5",
                databaseName);

            CreateFileDatabase(
                databaseName, deleteIfExists, ".mdb",
                dbName =>
                {
                    dynamic catalog = new CatalogClass();

                    var conn = catalog.Create(connectionString);

                    if (conn != null)
                        conn.Close();
                });
        }
Exemplo n.º 3
0
        /// <summary>
        /// 创建Access库
        /// </summary>
        /// <param name="connectionString"></param>
        /// <param name="accessFileFullpath"></param>
        /// <param name="dts"></param>
        public static void CreateDataBase(string connectionString, string accessFileFullpath, DataFieldTypeCollection dts)
        {
            CatalogClass cat = null;

            try
            {
                cat = new CatalogClass();
                cat.Create(connectionString);
            }
            catch (Exception ex)
            {
                throw new Exception($"创建Access数据库失败,路径[{accessFileFullpath}],异常信息:[{ex.Message}]");
            }


            #region 新建表
            try
            {
                TableClass tbl = new TableClass();
                tbl.ParentCatalog = cat;
                tbl.Name          = "mytable";

                foreach (DataFieldType dt in dts)
                {
                    //增加一个文本字段
                    ColumnClass col2 = new ColumnClass();
                    col2.ParentCatalog = cat;
                    col2.Name          = dt.FiledName;
                    col2.Properties["Jet OLEDB:Allow Zero Length"].Value = true;
                    switch (dt.Type.ToLower())
                    {
                    case "string":
                        col2.Type = ADOX.DataTypeEnum.adLongVarWChar;
                        tbl.Columns.Append(col2, ADOX.DataTypeEnum.adLongVarWChar, 16);
                        break;

                    case "datetime":
                        tbl.Columns.Append(col2, ADOX.DataTypeEnum.adDate, dt.Length);
                        break;

                    case "int":
                        tbl.Columns.Append(col2, ADOX.DataTypeEnum.adInteger, dt.Length);
                        break;
                    }
                }
                //把表加入数据库(非常重要)
                cat.Tables.Append(tbl);

                //转换为ADO连接,并关闭
                (cat.ActiveConnection as ADODB.Connection).Close();
                cat.ActiveConnection = null;
                cat = null;
            }
            catch (Exception ex)
            {
                throw new Exception($"创建Access数据库表失败,路径[{accessFileFullpath}],异常信息:[{ex.Message}]");
            }
            #endregion
        }
Exemplo n.º 4
0
        /// <summary>
        ///
        /// </summary>
        /// <returns></returns>
        CatalogClass openDatabase()
        {
            CatalogClass catalog = new CatalogClass();

            con = new Connection();
            try {
                con.Open(this.conString);
                catalog.ActiveConnection = con;
            } catch { catalog.Create(this.conString); }
            return(catalog);
        }
Exemplo n.º 5
0
        /// <summary>
        ///
        /// </summary>
        /// <param name="name"></param>
        protected override void CreateCatalog(string name)
        {
            // Force the run-time to let go of the file!  Otherwise,
            // cleanup and other operations might fail because the file
            // will still be in use.

            Catalog catalog = new CatalogClass();

            catalog.Create(GetConnectionString(name));
            catalog.ActiveConnection = null;
            catalog = null;
            GC.Collect();
        }
Exemplo n.º 6
0
        public static bool CreateMDBDataBase(string mdbPath)
        {
            bool result;

            try
            {
                CatalogClass catalogClass = new CatalogClass();
                catalogClass.Create("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + mdbPath + ";");
                result = true;
            }
            catch
            {
                result = false;
            }
            return(result);
        }
Exemplo n.º 7
0
 /// <summary>
 /// Girilen dosyayoluna mdb uzantılı access doyası oluşturan metot.
 /// </summary>
 /// <param name="MdbFilePath">Mdb dosyasının tamyolu ve dosyanın uzantılı ismi</param>
 /// <returns>
 /// 1: Dosya oluşturuldu.
 /// 0: Dosya zaten mevcut olduğundan oluşturulmadı.
 /// </returns>
 public static Int32 createMDBFile(string MdbFilePath)
 {
     try
     {
         Int32        retInt = 0;
         CatalogClass Cat    = new CatalogClass();
         Cat.Create(String.Format(@"Provider=Microsoft.Jet.OLEDB.4.0;
            Data Source={0};
            Jet OLEDB:Engine Type=5", MdbFilePath));
         retInt = 1;
         Cat    = null;
         return(retInt);
     }
     catch (Exception exc)
     {
         throw exc;
     }
 }
Exemplo n.º 8
0
        public void CreateDatabase([JetBrains.Annotations.NotNull] string databaseName, bool deleteIfExists = false)
        {
            if (databaseName == null)
            {
                throw new ArgumentNullException(nameof(databaseName));
            }

            databaseName = databaseName.Trim();

            if (!databaseName.ToLower().EndsWith(".mdb"))
            {
                databaseName += ".mdb";
            }

            if (File.Exists(databaseName))
            {
                if (!deleteIfExists)
                {
                    return;
                }
                File.Delete(databaseName);
            }

            var connectionString = string.Format(
                @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};Locale Identifier=1033;Jet OLEDB:Engine Type=5",
                databaseName);

            CreateFileDatabase(
                databaseName, deleteIfExists, ".mdb",
                dbName =>
            {
                dynamic catalog = new CatalogClass();

                var conn = catalog.Create(connectionString);

                if (conn != null)
                {
                    conn.Close();
                }
            });
        }
Exemplo n.º 9
0
        public void buildAccessFile()
        {
            if (File.Exists(accessFilePath))
            {
                File.Delete(accessFilePath);
            }

            /* NOTE */
            //COM MS ADO EXT and disable embeded type
            /* NOTE */


            CatalogClass catalog = new CatalogClass();

            catalog.Create("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + accessFilePath);

            //Build tables
            library.oledbConn accessFile = new library.oledbConn(this.accessFilePath, "mdb");

            accessFile.execute("create table Sites "
                               + " ( "
                               + " [siteId]    integer,"
                               + " [siteName]  TEXT"
                               + " ) "
                               );

            accessFile.execute("create table Data"
                               + " ( "
                               + " [dataId]    AUTOINCREMENT(1, 1) PRIMARY KEY,"
                               + " [siteId]    integer,"
                               + " [_weekNum]  integer,"
                               + " [_year]     integer,"
                               + " [_calValue] TEXT"
                               + " ) "
                               );

            accessFile.terminate();
        }
Exemplo n.º 10
0
        public static void WriteDB(List <xl2cad_cad.midTable> tables)
        {
            string filePath   = @"D:\xl2cad.mdb";
            string mdbCommand = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + filePath + ";Jet OLEDB:Engine Type=5";

            ADODB.Connection  cn  = null;
            ADOX.CatalogClass cat = null;
            try
            {
                //创建数据库,有就删除重建,没有则新建
                cat = new CatalogClass();
                if (File.Exists(filePath))
                {
                    File.Delete(filePath);
                }
                cat.Create(mdbCommand);
                //连接数据库
                cn  = new ADODB.Connection();
                cat = null;
                cat = new CatalogClass();
                //创建数据表
                foreach (xl2cad_cad.midTable midtable in tables)
                {
                    cn.Open("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + filePath, null, null, -1);
                    cat.ActiveConnection = cn;
                    ADOX.TableClass table     = new TableClass();
                    string          tablename = midtable.Name;
                    table.ParentCatalog = cat;
                    table.Name          = tablename;
                    cat.Tables.Append(table);
                    //将数据写入数据表
                    //Excel.WorkSheet.Range导出的object[obj1,obj2]中,obj1为行,obj2为列
                    //因此先按obj2的个数建立字段,在按obj1的个数一行行填入数据
                    object[,] datas = midtable.data;
                    int rowCount    = 0;
                    int columnCount = 0;
                    rowCount    = datas.GetUpperBound(0);   //第一维obj1的最大值,行数
                    columnCount = datas.GetUpperBound(1);   //第二维obj2的最大值,列数
                    //建立字段
                    for (int i = 0; i <= columnCount; i++)
                    {
                        ADOX.ColumnClass col = null;
                        col = new ADOX.ColumnClass();
                        col.ParentCatalog = cat;
                        col.Properties["Jet OLEDB:Allow Zero Length"].Value = true;
                        col.Name = "Value" + i;
                        table.Columns.Append(col, ADOX.DataTypeEnum.adVarChar, 25);
                    }
                    //按行填入数据
                    object          ra = null;
                    ADODB.Recordset rs = new ADODB.Recordset();
                    for (int i = 0; i <= rowCount; i++)
                    {
                        //构造按行写入的sql语句
                        string sql1     = String.Format("INSERT INTO {0} (", tablename);
                        string sql2     = String.Format(") VALUES (");
                        string strValue = null;
                        for (int j = 0; j <= columnCount; j++)
                        {
                            if (datas[i, j] == null)
                            {
                                strValue = datas[i, j] as string;
                            }
                            else
                            {
                                strValue = datas[i, j].ToString();
                            }
                            sql1 += String.Format("Value{0},", j);
                            sql2 += String.Format("'{0}',", strValue);
                        }
                        string sql = sql1 + sql2 + ")";
                        //将 ,) 替换为 )
                        sql = sql.Replace(",)", ")");
                        rs  = cn.Execute(sql, out ra, -1);
                    }
                }
            }
            catch (System.Exception ex)
            {
                MessageBox.Show(ex.ToString());
            }
            finally
            {
                cn.Close();
                System.Runtime.InteropServices.Marshal.FinalReleaseComObject(cn);
                System.Runtime.InteropServices.Marshal.FinalReleaseComObject(cat);
                System.GC.Collect();
            }
        }
Exemplo n.º 11
0
        //Создаем новую пустую БД из схемы
        public void CreateDB(string pathDB)
        {
            if (!m_isSchema)
            {
                return;
            }

            if (File.Exists(pathDB))
            {
                File.Copy(pathDB, pathDB + ".old", true);
                File.Delete(pathDB);
            }

            string connStr = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + pathDB;

            try
            {
                m_catalogADOX.Create(connStr);

                for (int i = 0; i < m_schema.tables.Length; i++)
                {
                    TableClass tableADOX = new TableClass();
                    tableADOX.Name          = m_schema.tables[i].name;
                    tableADOX.ParentCatalog = m_catalogADOX;

                    jcolumns[] cols = m_schema.tables[i].columns;
                    for (int j = 0; j < cols.Length; j++)
                    {
                        ColumnClass columnADOX = new ColumnClass();

                        columnADOX.ParentCatalog = m_catalogADOX;

                        columnADOX.Name        = cols[j].name;
                        columnADOX.Type        = cols[j].type;
                        columnADOX.DefinedSize = cols[j].definedSize;
                        columnADOX.Precision   = cols[j].precision;

                        columnADOX.Properties["Autoincrement"].Value = (object)cols[j].autoincrement;
                        columnADOX.Properties["Nullable"].Value      = (object)cols[j].nullable;
                        columnADOX.Properties["Fixed Length"].Value  = (object)cols[j].fixedLength;

                        tableADOX.Columns.Append(columnADOX, cols[j].type, cols[j].definedSize);
                    }

                    m_catalogADOX.Tables.Append(tableADOX);

                    //index
                    jindexs[] ind = m_schema.tables[i].indexs;
                    if (ind != null)
                    {
                        for (int j = 0; j < ind.Length; j++)
                        {
                            IndexClass indexADOX = new IndexClass();
                            indexADOX.Name       = ind[j].name;
                            indexADOX.Clustered  = ind[j].clustered;
                            indexADOX.IndexNulls = ind[j].indexNulls;
                            indexADOX.PrimaryKey = ind[j].primaryKey;
                            indexADOX.Unique     = ind[j].unique;

                            m_catalogADOX.Tables[m_schema.tables[i].name].Indexes.Append(ind[j].name, ind[j].name);
                        }
                    }

                    //key
                    jkeys[] key = m_schema.tables[i].keys;
                    if (key != null)
                    {
                        for (int j = 0; j < key.Length; j++)
                        {
                            KeyClass keyADOX = new KeyClass();
                            keyADOX.Name = key[j].name;
                            keyADOX.Type = key[j].type;
                            //keyADOX.Columns = key[j].column;
                            //keyADOX.Columns.Append(key[j].column, ADOX.DataTypeEnum.adInteger, 0);
                            //ColumnClass columnADOX = new ColumnClass();
                            //columnADOX.Name = key[j].column;

                            m_catalogADOX.Tables[m_schema.tables[i].name].Keys.Append(
                                key[j].name, key[j].type, m_catalogADOX.Tables[m_schema.tables[i].name].Columns[key[j].column], "", "");

                            //Без этой записи на win2000 выдавала глюк!!! Первая строка в vars отказывалась запичыватся
                            m_catalogADOX.Tables[m_schema.tables[i].name].Keys.Refresh();
                        }
                    }
                }
            }
            finally
            {
                //if (conn != null)
                //	conn.Close();
                //m_catalogADOX.ActiveConnection = null;
            }

            //Data и
            ConnectionClass conn = null;

            try
            {
                conn = new ConnectionClass();
                conn.Open(connStr, "", "", 0);

                for (int i = 0; i < m_schema.tables.Length; i++)
                {
                    jrows[] rows = m_schema.tables[i].rows;
                    if (rows != null && m_schema.tables[i].name == "vars")
                    {
                        FillDataRestruct(conn, m_schema.tables[i].name, rows);
                    }
                }
            }
            finally
            {
                if (conn != null)
                {
                    conn.Close();
                }
            }
        }
Exemplo n.º 12
0
        private void BT_LoginLogin_Click(object sender, EventArgs e)
        {
            var a = 0;

            #region "Timer arguments"

            timer1.Start();
            // Start timer!
            toolStripStatusLabel1.Visible = true;
            toolStripStatusLabel1.Text    = "Loading";
            // UI stuff
            // Shows in the bottom of the form that you are loading, etc.

            #endregion



            try
            {
                a += 1;
                // Increment a + 1



                usernameFromLogin = TB_LoginUsername.Text;
                // Declare that current username is now cloned into usernameFromLogin
                const string constring =
                    @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=A:\Users\Atul Anand Sinha\Documents\Visual Studio 2013\Projects\Database\Database.accdb;Persist Security Info=False";
                // Create connection string
                var connection = new OleDbConnection(constring);
                // OleDbConnection is now connected using the connection string/address
                var command =
                    new OleDbCommand(
                        "SELECT * FROM TPersons WHERE UserName='******' AND PassWord='******';", connection);
                // OleDbCommand assigns command the new instance and the ADO.NET query
                // Select everything from the TPersons table where the username and password are equal to the textbox values
                OleDbDataReader reader;
                // Create a OleDbDataReader instance to read data
                {
                    connection.Open();
                    // Open this node
                    reader = command.ExecuteReader();
                    // Execute and run the reader
                    var count = 0;
                    // Error counter
                    while (reader != null && reader.Read())
                    {
                        count = count + 1;
                    }
                    if (count == 1)
                    // If count is exactly equal to 1 then:
                    {
                        if (
                            MessageBox.Show("Login successful!", "Success!", MessageBoxButtons.OK,
                                            MessageBoxIcon.Information) == DialogResult.OK)
                        // Show success message!
                        // If OK is pressed in success message then
                        {
                            using (var clientForm = new ClientMain())
                            // Create an instance of ClientMain form
                            {
                                clientForm.ShowDialog();
                                // Show it
                            }
                            connection.Dispose();
                            // Dispose the connection to prevent future mishaps
                            #region "Create log database args"
                            // If the user is new, they might now have a training record database.
                            // This creates it for them

                            Catalog cat = new CatalogClass();
                            // Create a new catalog using the CatalogClass instance
                            var cntPath = Directory.GetCurrentDirectory();
                            // Declare local directory
                            var createStr = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + cntPath + "\\" +
                                            TB_LoginUsername.Text.ToLower() + "_LOG.accdb;";
                            // Connection string with local directory and the lowercase username_LOG.accdb
                            if (!File.Exists(cntPath + "\\" + TB_LoginUsername.Text.ToLower() + "_LOG.accdb"))
                            // If the file doesn't exist (if the user is new or file got deleted) then
                            {
                                cat.Create(createStr);
                                // Create the connection string
                                var tbl = new Table();
                                // Create a new table
                                tbl.Name = TB_LoginUsername.Text + "_SESSIONS";
                                // Table shall be called the username_SESSIONS
                                tbl.Columns.Append("ID", DataTypeEnum.adInteger);
                                // Add an ID column only for integers
                                tbl.Columns.Append("UserName", DataTypeEnum.adVarWChar, 100);
                                // Create a username column only for shorttext. Max limit is 100.
                                // ClientMain.Load() will update UserName to TB_LoginUsername.Text or usernamestringo if you will
                                tbl.Columns.Append("Cycling", DataTypeEnum.adVarWChar, 100);
                                // Create a cycling column only for shorttext. Max limit is 100.
                                tbl.Columns.Append("Running", DataTypeEnum.adVarWChar, 100);
                                // Create a running column only for shorttext. Max limit is 100.
                                tbl.Columns.Append("Swimming", DataTypeEnum.adVarWChar, 100);
                                // Create a swimming column only for shorttext. Max limit is 100.
                                cat.Tables.Append(tbl);
                                // Append changes to the table
                                Marshal.FinalReleaseComObject(tbl);
                                Marshal.FinalReleaseComObject(cat.Tables);
                                Marshal.FinalReleaseComObject(cat.ActiveConnection);
                                Marshal.FinalReleaseComObject(cat);
                                // Use System.InteropServices and Marshal to create the table

                                /*
                                 * Inserting UserName into Row
                                 */
                                try
                                {
                                    const string empty = "";
                                    // Empty string to use this as a placeholder for cells we are not filling right now

                                    ////int emptyInt = int.Parse(empty);
                                    //int? emptyInt = null; // using nullable int
                                    //int empInt = 0;
                                    var tableau = TB_LoginUsername.Text + "_SESSIONS";
                                    // username_SESSIONS will be the table name
                                    var usernametostring = TB_LoginUsername.Text;
                                    // Already explained :)
                                    var startPath = Directory.GetCurrentDirectory();
                                    // Declare local directory path
                                    var myCon =
                                        new OleDbConnection(@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" +
                                                            startPath + "\\" +
                                                            TB_LoginUsername.Text.ToLower() + "_LOG.accdb;");

                                    // Declare connection strings
                                    var cmd = new OleDbCommand();
                                    // Create an OleDbCommand instance
                                    cmd.CommandType = CommandType.Text;
                                    var query = "INSERT INTO " + tableau +
                                                " ([UserName], [ID], [Cycling], [Running], [Swimming]) VALUES(@username, @id, @cycling, @running, @swimming)";
                                    // Declare ADO.NET query into a string
                                    cmd.CommandText = query;
                                    // The query is the command
                                    cmd.Parameters.AddWithValue("@username", usernametostring);
                                    cmd.Parameters.AddWithValue("@id", a);
                                    // Has to be parsed otherwise @id returns a graphical ID value (eg. {00000000-CF60-05B3-B1D6-020F00000000})
                                    // Nope, I accidentally used DataTypeEnum.adGUID thinking that it would be the most suitable for an ID variable
                                    // adInteger is way more better!
                                    cmd.Parameters.AddWithValue("@cycling", empty);
                                    cmd.Parameters.AddWithValue("@running", empty);
                                    cmd.Parameters.AddWithValue("@swimming", empty);
                                    // We don't want anything in the sports columns yet as that will be updated in ClientMain.Log()
                                    // We just want the username so ClientMain.BT_Log() can find is using WHERE
                                    cmd.Connection = myCon;
                                    // Declaring myCon as the connection
                                    myCon.Open();
                                    // Open the connection
                                    cmd.ExecuteNonQuery();
                                    // Execute the above command
                                    MessageBox.Show("Username has been entered in your session database!");
                                    // Show message!
                                    cmd.Parameters.Clear();
                                    // Clear verbatim values
                                    //myCon.Dispose();
                                }
                                catch (OleDbException ex)
                                {
                                    MessageBox.Show(ex.ToString());
                                }
                            }
                        }
                    }
                    else if (count > 1)
                    {
                        // If not then either username or password is duplicate
                        toolStripStatusLabel1.Visible = true;
                        toolStripStatusLabel1.Text    = "Try again!";
                        MessageBox.Show("Duplicate username or password");
                    }
                    else
                    {
                        // Or it doesn't exist!
                        toolStripStatusLabel1.Visible = true;
                        toolStripStatusLabel1.Text    = "Try again!";
                        MessageBox.Show("Username or password do not match.");
                        //ProgBarColor.SetState(toolStripProgressBar1, 2);
                    }
                }
            }
            catch (OleDbException exception)
            {
                var errorMessages = "";
                for (var i = 0; i < exception.Errors.Count; i++)
                {
                    var oleDbError = exception.Errors[i];
                    if (oleDbError != null)
                    {
                        errorMessages += "Index #" + i + "\n" +
                                         "Message: " + oleDbError.Message + "\n" +
                                         "NativeError: " + oleDbError.NativeError + "\n" +
                                         "Source: " + oleDbError.Source + "\n" +
                                         "SQLState: " + oleDbError.SQLState + "\n";
                    }
                    // Generate OleDbException message
                }

                MessageBox.Show(errorMessages);
                // Print it and throw.
                throw;
            }

            #endregion
        }
Exemplo n.º 13
0
        private void CreateCatalogue()
        {
            CatalogClass catalogClass = new CatalogClass();

            catalogClass.Create(CONNECTION_STRING);
        }