예제 #1
0
        public void ReadAccessIntoSQL()
        {
            using (SchemaMapper.Converters.MsAccessImport smAccess = new SchemaMapper.Converters.MsAccessImport(@"G:\Passwords.mdb"))
            {
                //Read Access
                smAccess.BuildConnectionString();
                smAccess.getSchemaTable();

                string con = @"Data Source=.\SQLInstance;Initial Catalog=tempdb;integrated security=SSPI;";

                using (SchemaMapper.SchemaMapping.SchemaMapper SM = new SchemaMapper.SchemaMapping.SchemaMapper("dbo", "Passwords"))
                {
                    SM.CreateDestinationTable(con);

                    foreach (DataRow schRow in smAccess.SchemaTable.Rows)
                    {
                        string strTablename = schRow["TABLE_NAME"].ToString().Trim('\'');

                        DataTable dt     = smAccess.GetTableByName(strTablename);
                        bool      result = SM.ChangeTableStructure(ref dt);

                        if (result == true)
                        {
                            SM.InsertToSQLUsingSQLBulk(dt, con);
                        }
                    }
                }
            }
        }
예제 #2
0
        private void Form1_Load(object sender, EventArgs e)
        {
            SchemaMapper.SchemaMapping.SchemaMapper smPasswords = new SchemaMapper.SchemaMapping.SchemaMapper("dbo", "Passwords");

            //Define Server_Name , User_Name, Password columns
            SchemaMapper_Column smServerCol = new SchemaMapper_Column("Server_Name", SchemaMapper_Column.ColumnDataType.Text);
            SchemaMapper_Column smUserCol   = new SchemaMapper_Column("User_Name", SchemaMapper_Column.ColumnDataType.Text);
            SchemaMapper_Column smPassCol   = new SchemaMapper_Column("Password", SchemaMapper_Column.ColumnDataType.Text);

            //Define AddedDate column and fill it with a fixed value = Date.Now
            SchemaMapper_Column smAddedDate = new SchemaMapper_Column("AddedDate", SchemaMapper_Column.ColumnDataType.Date, DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"));

            //Define UserAndPassword column with and expression = [User_Name] + '|' + [Password]
            SchemaMapper_Column smUserPasswordCol = new SchemaMapper_Column("UserAndPassword", SchemaMapper_Column.ColumnDataType.Text, true, "[User_Name] + '|' + [Password]");

            //Add columns to SchemaMapper
            smPasswords.Columns.Add(smServerCol);
            smPasswords.Columns.Add(smUserCol);
            smPasswords.Columns.Add(smPassCol);
            smPasswords.Columns.Add(smAddedDate);
            smPasswords.Columns.Add(smUserPasswordCol);

            //Add all possible input Columns Names for each Column
            smServerCol.MappedColumns.AddRange(new[] { "server", "SQL Instance", "Server Name" });
            smUserCol.MappedColumns.AddRange(new[] { "username", "user", "Login" });
            smPassCol.MappedColumns.AddRange(new[] { "Password", "pass", "password" });

            //Sys_SheetName and Sys_ExtraFields are an auto generated columns while reading Excel file
            smPasswords.IgnoredColumns.AddRange(new[] { "ID", "AddedBy", "AddedDate", "Sys_Sheetname", "Sys_ExtraFields" });


            //Excel file
            DataTable dtExcel;
            DataTable dtText;
            DataTable dtAccess;

            //Excel worksheet
            using (SchemaMapper.Converters.MsExcelImport smExcel = new SchemaMapper.Converters.MsExcelImport(@"D:\SchemaMapperTest\Password_Test.xlsx"))
            {
                //Read Excel
                smExcel.BuildConnectionString();
                var lst = smExcel.GetSheets();
                //Read only from the first worksheet and consider the first row as header
                dtExcel = smExcel.GetTableByName(lst.First(), true, 0);
            }

            //Flat file
            using (SchemaMapper.Converters.FlatFileImportTools smFlat = new SchemaMapper.Converters.FlatFileImportTools(@"D:\SchemaMapperTest\Password_Test.txt", true, 0))
            {
                //Read flat file structure
                smFlat.BuildDataTableStructure();
                //Import data from flat file
                dtText = smFlat.FillDataTable();
            }

            //Access database

            using (SchemaMapper.Converters.MsAccessImport smAccess = new SchemaMapper.Converters.MsAccessImport(@"D:\SchemaMapperTest\Password_Test.accdb"))
            {
                //Build connection string and retrieve Access metadata
                smAccess.BuildConnectionString();
                smAccess.getSchemaTable();
                //Read data from Passwords table
                dtAccess = smAccess.GetTableByName("Passwords");
            }

            smPasswords.ChangeTableStructure(ref dtExcel);
            smPasswords.ChangeTableStructure(ref dtText);
            smPasswords.ChangeTableStructure(ref dtAccess);

            string connectionstring = @"Data Source=vaio\dataserver;Initial Catalog=tempdb;integrated security=SSPI;";

            smPasswords.CreateDestinationTable(connectionstring);

            smPasswords.InsertToSQLUsingSQLBulk(dtExcel, connectionstring);
            smPasswords.InsertToSQLUsingSQLBulk(dtText, connectionstring);
            smPasswords.InsertToSQLUsingSQLBulk(dtAccess, connectionstring);
        }