Example #1
2
        private static void ReadFromExcel()
        {
            DataTable dt = new DataTable("table");
            OleDbConnectionStringBuilder csBuilder = new OleDbConnectionStringBuilder();
            csBuilder.Provider = "Microsoft.ACE.OLEDB.12.0";
            csBuilder.DataSource = @"..\..\Table.xlsx";
            csBuilder.Add("Extended Properties", "Excel 12.0 Xml;HDR=YES");

            using (OleDbConnection connection  = new OleDbConnection(csBuilder.ConnectionString))
            {
                connection.Open();
                string query = @"SELECT * FROM Sample";

                using (OleDbDataAdapter adapter = new OleDbDataAdapter(query, connection))
                {
                    adapter.FillSchema(dt, SchemaType.Source);
                    adapter.Fill(dt);
                }
            }

            foreach (DataRow row in dt.Rows)
            {
                foreach (var item in row.ItemArray)
                {
                    Console.WriteLine(item);
                }
            }
        }
Example #2
0
        private static void InputDataFromExl(string directoryPath, string excelFileName)
        {
            DataTable dt = new DataTable("newtable");
            OleDbConnectionStringBuilder csbuilder = new OleDbConnectionStringBuilder();
            csbuilder.Provider = "Microsoft.ACE.OLEDB.12.0";
            csbuilder.DataSource = directoryPath + ReportsDirectory + excelFileName;
            csbuilder.Add("Extended Properties", "Excel 12.0 Xml;HDR=YES");

            using (OleDbConnection connection = new OleDbConnection(csbuilder.ConnectionString))
            {
                connection.Open();
                string selectSql = @"SELECT * FROM [Sales$]";
                using (OleDbDataAdapter adapter = new OleDbDataAdapter(selectSql, connection))
                {
                    adapter.FillSchema(dt, SchemaType.Source);
                    adapter.Fill(dt);
                }

                connection.Close();
            }

            Console.WriteLine(dt.Rows[0].ItemArray[0]);
            int rowsCount = dt.Rows.Count - 1;
            for (int i = 2; i < rowsCount; i++)
            {
                foreach (var item in dt.Rows[i].ItemArray)
                {
                    Console.WriteLine(item);
                }
            }
        }
    static void Main(string[] args)
    {
        DataTable dt = new DataTable("newtable");
            OleDbConnectionStringBuilder csbuilder = new OleDbConnectionStringBuilder();
            csbuilder.Provider = "Microsoft.ACE.OLEDB.12.0";
            csbuilder.DataSource = @"..\..\Table.xlsx";
            csbuilder.Add("Extended Properties", "Excel 12.0 Xml;HDR=YES");

            using (OleDbConnection connection = new OleDbConnection(csbuilder.ConnectionString))
            {
                connection.Open();
                string selectSql = @"SELECT * FROM [Sheet2$]";
                using (OleDbDataAdapter adapter = new OleDbDataAdapter(selectSql, connection))
                {
                    adapter.FillSchema(dt, SchemaType.Source);
                    adapter.Fill(dt);
                }
                connection.Close();
            }

            foreach (DataRow row in dt.Rows)
            {
                foreach (var item in row.ItemArray)
                {
                    Console.WriteLine(item);
                }
            }
    }
Example #4
0
        static void Main(string[] args)
        {
            //Using JET provider for xls files, because the newer ones do not work
            DataTable table = new DataTable("scores");
            string strAccessConn = string.Format(
                    "Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};Extended Properties=Excel 8.0;",
                    "../../Table.xls");
            OleDbConnection dbCon = new OleDbConnection(strAccessConn);

            using (dbCon)
            {
                string selectSql = @"SELECT * FROM [Sheet2$]";
                using (OleDbDataAdapter adapter = new OleDbDataAdapter(selectSql, dbCon))
                {
                    adapter.FillSchema(table, SchemaType.Source);
                    adapter.Fill(table);
                }            
            }

           // Console.WriteLine("Name, score:");
            foreach (DataRow row in table.Rows)
            {
                foreach (var item in row.ItemArray)
                {
                    Console.Write(item + " ");
                }
                Console.WriteLine();
            }
        }
Example #5
0
        public void GetTracks()
        {
            var dt = new DataTable("Track");

            using (var conn = new OleDbConnection())
            {
                conn.ConnectionString = GetConnectionString(Filename);
                conn.Open();

                var commandText = string.Format("SELECT * FROM [{0}${1}]", SheetName, SheetRange);

                using (var cmd = new OleDbCommand(commandText, conn))
                {

                    var adapter = new OleDbDataAdapter();
                    adapter.SelectCommand = cmd;
                    adapter.FillSchema(dt, SchemaType.Source);
                    adapter.Fill(dt);
                }
            }

            _reservedColumnsCount = 0;
            _tracks = new List<string>();
            foreach (DataColumn col in dt.Columns)
            {
                if (!MetadataFileFormat.GetReservedColumnNames().Contains(col.ColumnName))
                    _tracks.Add(col.ColumnName);
                else
                    _reservedColumnsCount++;
            }
        }
    private static void ReadExcelsFromDirectory(string filePath, List<SellsReport> reports)
    {
        DataTable dt = new DataTable("newtable");

        using (OleDbConnection connection = new OleDbConnection(@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + filePath + ";Extended Properties=Excel 12.0;"))
        {
            connection.Open();
            string selectSql = @"SELECT * FROM [Sales$]";
            using (OleDbDataAdapter adapter = new OleDbDataAdapter(selectSql, connection))
            {
                adapter.FillSchema(dt, SchemaType.Source);
                adapter.Fill(dt);
            }
            connection.Close();
        }

       
        for (int i = 2; i < dt.Rows.Count - 2; i++)
        {
            string location = dt.Rows[0][0].ToString();

            SellsReport report = new SellsReport()
            {
                Location = location,
                ProductID = int.Parse(dt.Rows[i][0].ToString()),
                Quantity = int.Parse(dt.Rows[i][1].ToString()),
                UnitPrice = decimal.Parse(dt.Rows[i][2].ToString())
            };

            reports.Add(report);
        }
    }
        private static void ReadExcelsFromDirectory(string filePath)
        {
            DataTable dt = new DataTable("newtable");

            using (OleDbConnection connection = new OleDbConnection(@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + filePath + ";Extended Properties=\"Excel 12.0;HDR=NO;IMEX=1\""))
            {
                connection.Open();
                string selectSql = @"SELECT * FROM [Sales$]";
                using (OleDbDataAdapter adapter = new OleDbDataAdapter(selectSql, connection))
                {
                    adapter.FillSchema(dt, SchemaType.Source);
                    adapter.Fill(dt);
                }
                connection.Close();
            }

            string location = dt.Rows[1][0].ToString();

            SuperMarket newSupermarket = new SuperMarket()
            {
                Name = location,
            };

            for (int i = 3; i < dt.Rows.Count - 1; i++)
            {
                int prodId = 0;
                string productId = dt.Rows[i][0].ToString();
                int.TryParse(productId, out prodId);
                if (prodId > 0)
                {
                    using (var ctx = new SupermarketEntities())
                    {
                        if (ctx.Products.Find(prodId) != null)
                        {
                            var supermarket = ctx.SuperMarkets.Where(s => s.Name == newSupermarket.Name).ToList();
                            if (supermarket.Count == 0)
                            {
                                ctx.SuperMarkets.Add(newSupermarket);
                                supermarket.Add(newSupermarket);
                            }

                            Sale newSale = new Sale()
                            {
                                ProductId = prodId,
                                SuperMarketId = supermarket[0].Id,
                                Date = DateTime.Now,
                                Quantity = int.Parse(dt.Rows[i][1].ToString()),
                                Price = decimal.Parse(dt.Rows[i][2].ToString()),
                                Sum = decimal.Parse(dt.Rows[i][3].ToString())
                            };

                            ctx.Sales.Add(newSale);
                            ctx.SaveChanges();
                        }
                    }
                }
            }
        }
        private static List<SaleInfo> ReadCurrentExcel(string path)
        {
            List<SaleInfo> allSales = new List<SaleInfo>();
            using (OleDbConnection conn = new OleDbConnection(path))
            {

                conn.Open();
                string command = @"select * from [Sales$]";
                OleDbDataAdapter adapter = new OleDbDataAdapter(command, conn);
                DataTable table = new DataTable();
                using (adapter)
                {
                    adapter.FillSchema(table, SchemaType.Source);
                    adapter.Fill(table);
                }

                int counter = 0;
                string[] splittedName = path.Split(new string[] { "-Sales-Report-" }, StringSplitOptions.RemoveEmptyEntries);
                string location = splittedName[0].Substring(splittedName[0].LastIndexOf('\\'));
                int dotIndex = splittedName[1].IndexOf('.');
                string date = splittedName[1].Substring(0, dotIndex);
                foreach (DataRow row in table.Rows)
                {
                    SaleInfo currentSale = new SaleInfo();
                    List<decimal> info = new List<decimal>();
                    bool getInside = false;

                    foreach (DataColumn  col in table.Columns)
                    {
                        if (row[col].ToString()!="")
                        {
                            info.Add(decimal.Parse(row[col].ToString()));
                           // Console.Write(row[col].ToString() + " ");
                            getInside = true;
                        }

                    }

                    if (getInside && counter != table.Rows.Count-1)
                    {
                        currentSale.ProductId = (int)info[0];
                        currentSale.Quantity = (int)info[1];
                        currentSale.UnitPrice = info[2];
                        currentSale.Sum = info[3];
                        currentSale.Location = location;
                        currentSale.SaleDate = DateTime.ParseExact(date, "dd-MMM-yyyy", CultureInfo.InvariantCulture);
                        allSales.Add(currentSale);
                    }
                    counter++;
                }

            }

            return allSales;
        }
Example #9
0
        void ReadExcel()
        {
            var _oleConn = new OleDbConnection(ExcelConnection());
            _oleConn.Open();
            var _oleCmdSelect = new OleDbCommand("SELECT * FROM Sheet", _oleConn);
            OleDbDataAdapter oleAdapter = new OleDbDataAdapter();
            oleAdapter.SelectCommand = _oleCmdSelect;
            DataTable dt = new DataTable("stock");
            oleAdapter.FillSchema(dt, SchemaType.Source);
            oleAdapter.Fill(dt);
            gridControl1.DataSource = dt;

            // select * from [sheetname$[range]
        }
Example #10
0
        public DataSet GetWorkplace()
        {
            DataSet workplace;

            OleDbConnection connection = new OleDbConnection(strConnection);
            OleDbDataAdapter adaptor = new OleDbDataAdapter("SELECT * FROM *", connection);
            workplace = new DataSet();
            adaptor.FillSchema(workplace, SchemaType.Source);
            adaptor.Fill(workplace);

            adaptor.Dispose();
            connection.Close();

            return workplace;
        }
Example #11
0
        static void Main(string[] args)
        {
            string filePath = @"D:\t\edp\Processed\mastinv_47100_4627900.xls";
            string connectionstring = @"Provider=Microsoft.Jet.OleDb.4.0;Data Source=" + filePath + ";Extended Properties=Excel 8.0;";
            DataTable table = new DataTable();
            OleDbConnection con = new OleDbConnection(connectionstring);
            con.Open();
            OleDbDataAdapter adap = new OleDbDataAdapter("Select * from [data$]", con);
            OleDbCommand InsertC = new OleDbCommand("insert into [data$]([File name], [client], [batch], [scac], [carracct], [invoice number], [invoice date], [currency], [billed amount], [vat]) values('123', '123', '123', '123', '123', '123', '123','123', '123', '1234')", con);
            adap.InsertCommand = InsertC;
            adap.FillSchema(table, SchemaType.Mapped);
            adap.UpdateCommand = new OleDbCommand("update [data$] set batch = 'aasdf' where [file name] = '47100_4627900_00004.PDF'", con);
            //adap.UpdateCommand.ExecuteNonQuery();
            adap.InsertCommand.ExecuteNonQuery();
            con.Close();

        }
Example #12
0
 public DataTable Resultado(string script, string tabla)
 {
     string sql = "";
     if(script == null) sql = "SELECT * FROM " + tabla;
     else sql = script;
     if (Conectar())
     {
         this.oda = new OleDbDataAdapter(sql, this.conn);
         ds = new DataSet();
         oda.FillSchema(ds, SchemaType.Source, tabla);
         oda.Fill(ds, tabla);
         dt = ds.Tables[tabla];
         Desconectar();
         return dt;
     }
     else { return null; }
 }
        private static void ReadExcelsFromDirectory(string filePath)
        {
            DataTable dt = new DataTable("newtable");

            using (OleDbConnection connection = new OleDbConnection(@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + filePath + ";Extended Properties=Excel 12.0;"))
            {
                connection.Open();
                string selectSql = @"SELECT * FROM [Sales$]";
                using (OleDbDataAdapter adapter = new OleDbDataAdapter(selectSql, connection))
                {
                    adapter.FillSchema(dt, SchemaType.Source);
                    adapter.Fill(dt);
                }
                connection.Close();
            }

            List<SellsReport> repotst = new List<SellsReport>();
            for (int i = 2; i < dt.Rows.Count-2; i++)
            {
                string location = dt.Rows[0][0].ToString();

                SellsReport report = new SellsReport()
                {
                    Location = location,
                    ProductID = int.Parse(dt.Rows[i][0].ToString()),
                    Quantity = int.Parse(dt.Rows[i][1].ToString()),
                    UnitPrice = decimal.Parse(dt.Rows[i][2].ToString())
                };

                repotst.Add(report);
            }

            foreach (var item in repotst)
            {
                Console.WriteLine(item.UnitPrice);
            }

            //foreach (DataRow row in dt.Rows)
            //{
            //    for (int i = 0; i < row.ItemArray.Length; i++)
            //    {
            //        Console.Write(row.ItemArray[i].ToString()+"  ");
            //    }
            //    Console.WriteLine();
            //}
        }
Example #14
0
        //private static readonly int m_maxSheelSize = 65000;
        #region 公用静态方法

        #region 从Excel读数据
        /// <summary>
        /// 从Excel读数据
        /// </summary>
        /// <param name="filePath">excel文档路径</param>
        /// <param name="excelVersion">文档版本</param>
        /// <param name="pHDR">第一行是否标题</param>
        /// <param name="bMerge">
        /// 如果有多页,是否合并数据,合并时必须保证多页的表结构一致
        /// </param>
        /// <returns>DataTable集</returns>
        public static DataTable[] GetExcelData(string filePath, ExcelVersion excelVersion, HeadRowType pHDR, bool bMerge)
        {
            List<DataTable> dtResult = new List<DataTable>();
            string connectionString = string.Format(GetConnectionString(excelVersion, ImportOrExportType.Import),
              filePath, pHDR);
            using (OleDbConnection con = new OleDbConnection(connectionString))
            {
                con.Open();
                string[] sheels = GetExcelWorkSheets(filePath, excelVersion);
                foreach (string sheelName in sheels)
                {
                    try
                    {
                        DataTable dtExcel = new DataTable();
                        OleDbDataAdapter adapter = new OleDbDataAdapter("Select * from [" + sheelName + "$]", con);

                        adapter.FillSchema(dtExcel, SchemaType.Mapped);
                        adapter.Fill(dtExcel);

                        dtExcel.TableName = sheelName;
                        dtResult.Add(dtExcel);
                    }
                    catch
                    {
                        //容错处理:取不到时,不报错,结果集为空即可。
                    }
                }

                //如果需要合并数据,则合并到第一张表
                if (bMerge)
                {
                    for (int i = 1; i < dtResult.Count; i++)
                    {
                        //如果不为空才合并
                        if (dtResult[0].Columns.Count == dtResult[i].Columns.Count &&
                            dtResult[i].Rows.Count > 0)
                        {
                            dtResult[0].Load(dtResult[i].CreateDataReader());
                        }
                    }
                }
            }
            return dtResult.ToArray();
        }
        private static void InsertDataFromXls(string filePath, string dateString)
        {
            DataTable dt = new DataTable("newtable");
            OleDbConnectionStringBuilder csbuilder = new OleDbConnectionStringBuilder();
            csbuilder.Provider = "Microsoft.ACE.OLEDB.12.0";
            csbuilder.DataSource = filePath;
            csbuilder.Add("Extended Properties", "Excel 12.0 Xml;HDR=YES");

            using (OleDbConnection connection = new OleDbConnection(csbuilder.ConnectionString))
            {
                connection.Open();
                string selectSql = @"SELECT * FROM [Sales$]";
                using (OleDbDataAdapter adapter = new OleDbDataAdapter(selectSql, connection))
                {
                    adapter.FillSchema(dt, SchemaType.Source);
                    adapter.Fill(dt);
                }

                connection.Close();
            }

            string[] dateParts = dateString.Split('-');
            int day = int.Parse(dateParts[0]);
            int month = GetMonthAsInt(dateParts[1]);
            int year = int.Parse(dateParts[2]);
            DateTime reportDate = new DateTime(year, month, day);

            int rowsCount = dt.Rows.Count - 1;
            for (int i = 2; i < rowsCount; i++)
            {
                Report report = new Report
                {
                    ProductId = Convert.ToInt32(dt.Rows[i].ItemArray[0]),
                    Quantity = Convert.ToInt32(dt.Rows[i].ItemArray[1]),
                    UnitPrice = Convert.ToDecimal(dt.Rows[i].ItemArray[2]),
                    Sum = Convert.ToDecimal(dt.Rows[i].ItemArray[3]),
                    Date = reportDate
                };

                string locationName = dt.Rows[0].ItemArray[0].ToString();
                InsertInSqlServer(report, locationName);
            }
        }
        public DataTable ExcelToDataTable(string excelFileName)
        {
            System.Configuration.AppSettingsReader app = new System.Configuration.AppSettingsReader();
            String provider = (String)app.GetValue("Provider", typeof(String));
            String extProperties = (String)app.GetValue("Extended Properties", typeof(String));
            
            string connString = "Provider=" + provider + ";Data Source=" + excelFileName + ";Extended Properties='" + extProperties + "'";

            DataTable dtExceldata = new DataTable();

            try
            {
                OleDbCommand excelCommand = new OleDbCommand();
                OleDbDataAdapter excelDataAdapter = new OleDbDataAdapter();

                using (OleDbConnection excelConn = new OleDbConnection(connString))
                {
                    excelConn.Open();

                    String sheetName = "";

                    DataTable dtExcelSheets = new DataTable();
                    dtExcelSheets = excelConn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
                    if (dtExcelSheets.Rows.Count > 0)
                    {
                        sheetName = dtExcelSheets.Rows[0]["TABLE_NAME"].ToString();
                    }
                    OleDbCommand OleCmdSelect = new OleDbCommand("SELECT * FROM [" + sheetName + "]", excelConn);
                    OleDbDataAdapter OleAdapter = new OleDbDataAdapter(OleCmdSelect);

                    OleAdapter.FillSchema(dtExceldata, System.Data.SchemaType.Source);
                    OleAdapter.Fill(dtExceldata);
                    excelConn.Close();
                }

                return dtExceldata;
            }
            catch (Exception ex)
            {
                return null;
            }
        }
Example #17
0
    static void Main()
    {
        DataTable dt = new DataTable("newtable");

        using (OleDbConnection connection = new OleDbConnection(@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=../../../task.xlsx;Extended Properties=Excel 12.0;"))
        {
            connection.Open();
            string selectSql = @"SELECT * FROM [Sheet1$]";
            using (OleDbDataAdapter adapter = new OleDbDataAdapter(selectSql, connection))
            {
                adapter.FillSchema(dt, SchemaType.Source);
                adapter.Fill(dt);
            }
            connection.Close();
        }

        foreach (DataRow row in dt.Rows)
        {
            Console.WriteLine(row.ItemArray[0]+" - " + row.ItemArray[1]);
        }
    }
Example #18
0
        // Загрузка в DataSet
        public static void BuildDataSet(ref OleDbConnection connect, ref DataSet ds, ref OleDbDataAdapter[] adapt)
        {
            OleDbCommand[] commands = new OleDbCommand[3];

            commands[0] = new OleDbCommand("SELECT * from Tovar", connect);
            daTovar = new OleDbDataAdapter(commands[0]);
            daTovar.FillSchema(ds, SchemaType.Source, "Tovar");
            daTovar.Fill(ds, "Tovar");
            adapt[0] = daTovar;

            commands[1] = new OleDbCommand("SELECT * from Pokupatel", connect);
            daPokupatel = new OleDbDataAdapter(commands[1]);
            daPokupatel.FillSchema(ds, SchemaType.Source, "Pokupatel");
            daPokupatel.Fill(ds, "Pokupatel");
            adapt[1] = daPokupatel;

            commands[2] = new OleDbCommand("SELECT * from Zakaz", connect);
            daZakaz = new OleDbDataAdapter(commands[2]);
            daZakaz.FillSchema(ds, SchemaType.Source, "Zakaz");
            daZakaz.Fill(ds, "Zakaz");
            adapt[2] = daZakaz;

        }
Example #19
0
        private void NoIdTeacher()
        {
            SqlHelper help = new SqlHelper();
            DataTable dtClass = help.getDs("select * from Classes_Data","Classes_Data").Tables[0];
            DataRow[] NoIDteacher = dtClass.Select("Teacher_ID='" + "0000000000" + "' ");
            daTeachers = help.adapter("select * from Teachers_Data");
            dtTeachers = new System.Data.DataTable();
            daTeachers.Fill(dtTeachers);
            daTeachers.FillSchema(dtTeachers, SchemaType.Source);
            List<string> teachers = new List<string> { };
            DataTable Idteacher_dt = dtTeachers.Copy();   //  获取Class_Data的架构
            Idteacher_dt.Clear();
            for (int i = 0; i < NoIDteacher.Length; i++)
            {
                teachers.Add(NoIDteacher[i][2].ToString());
            }
            string[] newteachers = teachers.Distinct<string>().ToArray();
            teachers.Clear();
            for (int i = 0; i < dtTeachers.Rows.Count;i++ )
            {
                teachers.Add(dtTeachers.Rows[i][1].ToString());
            }
            string[] oldteachers = teachers.Distinct<string>().ToArray();
            for (int z = 0; z < newteachers.Length; z++)
            {
                for (int i = 0; i < oldteachers.Length;i++ )
                {
                    if (newteachers[z].ToString().Equals(oldteachers[i].ToString()))
                    {
                        newteachers[z] = "";
                    }

                }

            }

            for (int i = 0; i < newteachers.Length;i++ )
            {   if(newteachers[i]!=""){
                DataRow dr_teacher = Idteacher_dt.NewRow();
                dr_teacher[1] = newteachers[i].ToString();
                dr_teacher[0] = newteachers[i].ToString();
                dr_teacher[7] = 0;
                dr_teacher[8] = 0;
                dr_teacher[9] = 0;
                dr_teacher[10] = 0;
                Idteacher_dt.Rows.Add(dr_teacher);
               }
            }

            dtTeachers.Merge(Idteacher_dt, true);
            daTeachers.Update(dtTeachers);
        }
        //=====================================================================
        /// <summary>
        /// Create the data source for the demo
        /// </summary>
        /// <remarks>You can use the designer to create the data source and use strongly typed data sets.  For
        /// this demo, we'll do it by hand.
        /// </remarks>
        private void CreateDataSource()
        {
            // The test database should be in the project folder
            dbConn = new OleDbConnection(@"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=.\TestData.mdb");
            daAddresses = new OleDbDataAdapter();
            daPhones = new OleDbDataAdapter();
            dsAddresses = new DataSet();

            // Set the table name
            daAddresses.TableMappings.Add("Table", "Addresses");

            // In a real application we wouldn't use literal SQL but we will for the demo
            daAddresses.SelectCommand = new OleDbCommand("Select * From Addresses Order By LastName", dbConn);

            daAddresses.DeleteCommand = new OleDbCommand("Delete From Addresses Where ID = @paramID", dbConn);
            daAddresses.DeleteCommand.Parameters.Add(new OleDbParameter("@paramID", OleDbType.Integer, 0,
                ParameterDirection.Input, false, 0, 0, "ID", DataRowVersion.Original, null));

            daAddresses.InsertCommand = new OleDbCommand(
                "INSERT INTO Addresses (FirstName, LastName, Address, City, State, Zip, SumValue, Domestic, " +
                "International, Postal, Parcel, Home, Business, ContactType) " +
                "VALUES (@paramFN, @paramLN, @paramAddress, @paramCity, @paramState, @paramZip, " +
                "@paramSumValue, @paramDomestic, @paramInternational, @paramPostal, @paramParcel, " +
                "@paramHome, @paramBusiness, @paramContactType)", dbConn);
            daAddresses.InsertCommand.Parameters.Add(new OleDbParameter("@paramFirstName", OleDbType.VarWChar,
                20, "FirstName"));
            daAddresses.InsertCommand.Parameters.Add(new OleDbParameter("@paramLastName", OleDbType.VarWChar,
                30, "LastName"));
            daAddresses.InsertCommand.Parameters.Add(new OleDbParameter("@paramAddress", OleDbType.VarWChar,
                50, "Address"));
            daAddresses.InsertCommand.Parameters.Add(new OleDbParameter("@paramCity", OleDbType.VarWChar, 20,
                "City"));
            daAddresses.InsertCommand.Parameters.Add(new OleDbParameter("@paramState", OleDbType.VarWChar, 2,
                "State"));
            daAddresses.InsertCommand.Parameters.Add(new OleDbParameter("@paramZip", OleDbType.VarWChar, 10,
                "Zip"));
            daAddresses.InsertCommand.Parameters.Add(new OleDbParameter("@paramSumValue", OleDbType.Integer, 0,
                "SumValue"));
            daAddresses.InsertCommand.Parameters.Add(new OleDbParameter("@paramDomestic", OleDbType.Boolean, 0,
                "Domestic"));
            daAddresses.InsertCommand.Parameters.Add(new OleDbParameter("@paramInternational", OleDbType.Boolean,
                0, "International"));
            daAddresses.InsertCommand.Parameters.Add(new OleDbParameter("@paramPostal", OleDbType.Boolean, 0,
                "Postal"));
            daAddresses.InsertCommand.Parameters.Add(new OleDbParameter("@paramParcel", OleDbType.Boolean, 0,
                "Parcel"));
            daAddresses.InsertCommand.Parameters.Add(new OleDbParameter("@paramHome", OleDbType.Boolean, 0,
                "Home"));
            daAddresses.InsertCommand.Parameters.Add(new OleDbParameter("@paramBusiness", OleDbType.Boolean, 0,
                "Business"));
            daAddresses.InsertCommand.Parameters.Add(new OleDbParameter("@paramContactType", OleDbType.Char, 1,
                "ContactType"));

            daAddresses.UpdateCommand = new OleDbCommand(
                "UPDATE Addresses SET FirstName = @paramFirstName, LastName = @paramLastName, " +
                "Address = @paramAddress, City = @paramCity, State = @paramState, Zip = @paramZip, " +
                "SumValue = @paramSumValue, Domestic = @paramDomestic, International = @paramInternational, " +
                "Postal = @paramPostal, Parcel = @paramParcel, Home = @paramHome, Business = @paramBusiness, " +
                "ContactType = @paramContactType WHERE ID = @paramID", dbConn);
            daAddresses.UpdateCommand.Parameters.Add(new OleDbParameter("@paramFirstName", OleDbType.VarWChar,
                20, "FirstName"));
            daAddresses.UpdateCommand.Parameters.Add(new OleDbParameter("@paramLastName", OleDbType.VarWChar, 30,
                "LastName"));
            daAddresses.UpdateCommand.Parameters.Add(new OleDbParameter("@paramAddress", OleDbType.VarWChar, 50,
                "Address"));
            daAddresses.UpdateCommand.Parameters.Add(new OleDbParameter("@paramCity", OleDbType.VarWChar, 20,
                "City"));
            daAddresses.UpdateCommand.Parameters.Add(new OleDbParameter("@paramState", OleDbType.VarWChar, 2,
                "State"));
            daAddresses.UpdateCommand.Parameters.Add(new OleDbParameter("@paramZip", OleDbType.VarWChar, 10,
                "Zip"));
            daAddresses.UpdateCommand.Parameters.Add(new OleDbParameter("@paramSumValue", OleDbType.Integer, 0,
                "SumValue"));
            daAddresses.UpdateCommand.Parameters.Add(new OleDbParameter("@paramDomestic", OleDbType.Boolean, 0,
                "Domestic"));
            daAddresses.UpdateCommand.Parameters.Add(new OleDbParameter("@paramInternational", OleDbType.Boolean,
                0, "International"));
            daAddresses.UpdateCommand.Parameters.Add(new OleDbParameter("@paramPostal", OleDbType.Boolean, 0,
                "Postal"));
            daAddresses.UpdateCommand.Parameters.Add(new OleDbParameter("@paramParcel", OleDbType.Boolean, 0,
                "Parcel"));
            daAddresses.UpdateCommand.Parameters.Add(new OleDbParameter("@paramHome", OleDbType.Boolean, 0,
                "Home"));
            daAddresses.UpdateCommand.Parameters.Add(new OleDbParameter("@paramBusiness", OleDbType.Boolean, 0,
                "Business"));
            daAddresses.UpdateCommand.Parameters.Add(new OleDbParameter("@paramContactType", OleDbType.Char, 1,
                "ContactType"));
            daAddresses.UpdateCommand.Parameters.Add(new OleDbParameter("@paramID", OleDbType.Integer, 0,
                ParameterDirection.Input, false, 0, 0, "ID", System.Data.DataRowVersion.Original, null));

            // Fill in the schema for auto-increment etc.
            daAddresses.FillSchema(dsAddresses, SchemaType.Mapped);

            // Bind the controls to the data source.  Since we are using a data set, we need to use fully
            // qualified names.
            txtFName.DataBindings.Add(new Binding("Text", dsAddresses, "Addresses.FirstName"));
            txtLName.DataBindings.Add(new Binding("Text", dsAddresses, "Addresses.LastName"));
            txtAddress.DataBindings.Add(new Binding("Text", dsAddresses, "Addresses.Address"));
            txtCity.DataBindings.Add(new Binding("Text", dsAddresses, "Addresses.City"));
            cboState.DataBindings.Add(new Binding("SelectedValue", dsAddresses, "Addresses.State"));
            txtZip.DataBindings.Add(new Binding("Text", dsAddresses, "Addresses.Zip"));
            lblKey.DataBindings.Add(new Binding("Text", dsAddresses, "Addresses.ID"));

            // Connect the Row Updated event so that we can retrieve the new primary key values as they are
            // identity values.
            daAddresses.RowUpdated += daAddresses_RowUpdated;

            // The checkboxes in the checkbox list can also be bound to members of a different data source.  To
            // do this, set the BindingMembersDataSource and specify the members to which each checkbox is bound
            // in the BindingMembers collection property.  In this demo, the BindingMembers are specified via the
            // designer.  However, we could do it in code as well as shown here:
            //
            // cblAddressTypes.BindingMembers.AddRange(new string[] { "Addresses.Domestic",
            //     "Addresses.International", "Addresses.Postal", "Addresses.Parcel", "Addresses.Home",
            //     "Addresses.Business"});
            //
            // As above, since we are binding to a DataSet, we must specify the fully qualified names.  Also note
            // that there is a BindingMembersBindingContext property that can be set if the binding members data
            // source is in a binding context different than the checkbox list's data source.
            //
            // Note that we could assign a data source for the checkbox list items as well similar to the radio
            // button list data source below but in this case, the list is simple so it's added to the Items
            // collection via the designer.
            cblAddressTypes.BindingMembersDataSource = dsAddresses;

            // Create the data source for the radio button list items
            List<ListItem> contactTypeList = new List<ListItem>();
            contactTypeList.Add(new ListItem("B", "Business"));
            contactTypeList.Add(new ListItem("P", "Personal"));
            contactTypeList.Add(new ListItem("O", "Other"));

            rblContactType.DisplayMember = "Display";
            rblContactType.ValueMember = "Value";
            rblContactType.DataSource = contactTypeList;

            // Bind the radio button list to the ContactType field.  Since it can be null, we'll add a Parse
            // event to default the value to "Business" if it's null.  This wouldn't be needed for fields that
            // are never null (i.e. those with a default value).
            Binding b = new Binding("SelectedValue", dsAddresses, "Addresses.ContactType");
            b.Format += ContactType_Format;
            rblContactType.DataBindings.Add(b);

            // Set up the phone info data adapter
            daPhones.SelectCommand = new OleDbCommand("Select * From Phones Order By ID, PhoneNumber", dbConn);

            daPhones.DeleteCommand = new OleDbCommand("Delete From Phones Where PhoneKey = @paramPhoneKey", dbConn);
            daPhones.DeleteCommand.Parameters.Add(new OleDbParameter("@paramPhoneKey", OleDbType.Integer, 0,
                ParameterDirection.Input, false, 0, 0, "PhoneKey", DataRowVersion.Original, null));

            daPhones.InsertCommand = new OleDbCommand("INSERT INTO Phones (ID, PhoneNumber) VALUES (@paramID, " +
                "@paramPhoneNumber)", dbConn);
            daPhones.InsertCommand.Parameters.Add(new OleDbParameter("@paramID", OleDbType.Integer, 0, "ID"));
            daPhones.InsertCommand.Parameters.Add(new OleDbParameter("@paramPhoneNumber", OleDbType.VarWChar, 20,
                "PhoneNumber"));

            daPhones.UpdateCommand = new OleDbCommand(
                "UPDATE Phones SET PhoneNumber = @paramPhoneNumber WHERE PhoneKey = @paramPhoneKey", dbConn);
            daPhones.UpdateCommand.Parameters.Add(new OleDbParameter("@paramPhoneNumber", OleDbType.VarWChar, 20,
                "PhoneNumber"));
            daPhones.UpdateCommand.Parameters.Add(new OleDbParameter("@paramPhoneKey", OleDbType.Integer, 0,
                ParameterDirection.Input, false, 0, 0, "PhoneKey", DataRowVersion.Original, null));

            // Connect the Row Updated event so that we can retrieve the new primary key values as they are
            // identity values.
            daPhones.RowUpdated += daPhones_RowUpdated;

            // Load the state codes for the row template's shared data source
            OleDbDataAdapter daStates = new OleDbDataAdapter("Select State, StateDesc From States", dbConn);

            DataTable dtStates = new DataTable();
            daStates.Fill(dtStates);

            // Add a blank row to allow no selection
            dtStates.Rows.InsertAt(dtStates.NewRow(), 0);

            cboState.DisplayMember = cboState.ValueMember = "State";
            cboState.DataSource = dtStates.DefaultView;
        }
	public void TestLongSqlExpression()
	{
		BeginCase("Long SQL string cause java.lang.StackOverflowError (Test case for bug #4708)");

		StringBuilder querySb = new StringBuilder();
		querySb.Append("SELECT ");
		querySb.Append("c.CustomerID as ci1, c.CustomerID as ci2, c.CustomerID as ci3, c.CustomerID as ci4, ");
		querySb.Append("c.CompanyName as cn1, c.CompanyName as cn2, c.CompanyName as cn3, c.CompanyName as cn4, ");
		querySb.Append("c.ContactName as cntn1, c.ContactName as cntn2, c.ContactName as cntn3, c.ContactName as cntn4, ");
		querySb.Append("c.ContactTitle as ct1, c.ContactTitle as ct2, c.ContactTitle as ct3, c.ContactTitle as ct4, ");
		querySb.Append("c.Address as ad1, c.Address as ad2, c.Address as ad3, c.Address as ad4, ");
		querySb.Append("c.City as ct1, c.City as ct2, c.City as ct3, c.City as ct4, ");
		querySb.Append("c.Region as rg1, c.Region as rg2, c.Region as rg3, c.Region as rg4, ");
		querySb.Append("c.PostalCode as pc1, c.PostalCode as pc2, c.PostalCode as pc3, c.PostalCode as pc4, ");
		querySb.Append("c.Country as co1, c.Country as co2, c.Country as co3, c.Country as co4, ");
		querySb.Append("c.Phone as ph1, c.Phone as ph2, c.Phone as ph3, c.Phone as ph4, ");
		querySb.Append("c.Fax as fx1, c.Fax as fx2, c.Fax as fx3, c.Fax as fx4 ");
		querySb.Append("FROM Customers c");
		OleDbDataAdapter adapter = null;
		DataSet schemaDs = new DataSet();
		try
		{
			using(adapter = new OleDbDataAdapter(querySb.ToString(), this.connectionString))
			{
				adapter.MissingSchemaAction = MissingSchemaAction.AddWithKey;
				adapter.FillSchema(schemaDs, SchemaType.Source);
				Compare(schemaDs.Tables.Count, 1);
			}
		} 
		catch(Exception ex)
		{
			exp = ex;
		}
		finally
		{
			EndCase(exp);
			exp = null;
		}
	}
Example #22
0
        protected DataTable LoadDataTable()
        {
            RaiseProgressStatus("Reading Xls file");
            var dt = new DataTable("Metadata");

            using (var conn = new OleDbConnection())
            {
                conn.ConnectionString = GetConnectionString(Filename);
                conn.Open();

                string commandText = null;
                if (string.IsNullOrEmpty(SheetRange))
                    commandText = string.Format("SELECT * FROM [{0}$]", SheetName);
                else
                    commandText = string.Format("SELECT * FROM [{0}]${1}", SheetName, string.IsNullOrEmpty(SheetRange) ? "A1:AZ1" : SheetRange);

                using (var cmd = new OleDbCommand(commandText, conn))
                {

                    var adapter = new OleDbDataAdapter();
                    adapter.SelectCommand = cmd;
                    adapter.FillSchema(dt, SchemaType.Source);
                    adapter.Fill(dt);
                }
            }
            return dt;
        }
        public static DataTable ConvertExcelToDataTable(string fileName, string sheetName)
        {
            using (var objConn = new OleDbConnection(@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + fileName + ";Extended Properties='Excel 12.0;HDR=YES;IMEX=1;';"))
            {
                objConn.Open();
                var cmd = new OleDbCommand();
                var dtable = new DataTable();
                var dt = objConn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
                if (dt != null)
                {
                    var tempDataTable = (from dataRow in dt.AsEnumerable()
                                         where !dataRow["TABLE_NAME"].ToString().Contains("FilterDatabase")
                                         select dataRow).CopyToDataTable();
                    dt = tempDataTable;
                    var totalSheet = dt.Rows.Count; //No of sheets on excel file  
                    for (var i = 0; i < totalSheet; i++)
                    {
                        var name = dt.Rows[i]["TABLE_NAME"].ToString();

                        if (name.Contains(sheetName))
                        {
                            sheetName = name;
                            break;
                        }
                    }
                }
                cmd.Connection = objConn;
                cmd.CommandType = CommandType.Text;
                cmd.CommandText = "SELECT * FROM [" + sheetName + "]";

                var oleda = new OleDbDataAdapter(cmd);
                oleda.FillSchema(dtable, SchemaType.Source);

                foreach (DataColumn cl in dtable.Columns)
                {
                    if (cl.DataType == typeof(double))
                        cl.DataType = typeof(decimal);
                    if (cl.ColumnName.ToUpper().Contains("AMT"))
                        cl.DataType = typeof(decimal);
                    if (cl.ColumnName.ToUpper().Contains("DATE"))
                        cl.DataType = typeof(DateTime);
                }

                foreach (DataRow dr in dtable.Rows)
                {
                    foreach (DataColumn dc in dtable.Columns)
                    {
                        if (dc.ColumnName.ToUpper().Contains("DATE"))
                        {
                            dr[dc.ColumnName] = DateTime.ParseExact(dr[dc.ColumnName].ToString(), "dd-MM-yyyy", new CultureInfo(ConfigurationManager.AppSettings["CultureInfo"].ToString()));
                        }
                    }
                }

                oleda.Fill(dtable);
                objConn.Close();
                return dtable;
            }
        }
Example #24
0
        public DataTable GetWorksheet(string worksheet)
        {
            DataTable ws;

            OleDbConnection connection = new OleDbConnection(strConnection);
            OleDbDataAdapter adaptor = new OleDbDataAdapter(String.Format("SELECT * FROM [{0}$]", worksheet), connection);
            ws = new DataTable(worksheet);
            adaptor.FillSchema(ws, SchemaType.Source);
            adaptor.Fill(ws);

            adaptor.Dispose();
            connection.Close();

            return ws;
        }
Example #25
0
        /// <summary>
        ///   Gets an worksheet as a data table.
        /// </summary>
        /// 
        public DataTable GetWorksheet(string worksheet)
        {
            DataTable ws;

            using (OleDbConnection connection = new OleDbConnection(strConnection))
            {
                OleDbCommand command = new OleDbCommand("SELECT * FROM [" + worksheet + "$]", connection);

                using (OleDbDataAdapter adaptor = new OleDbDataAdapter(command))
                {
                    ws = new DataTable(worksheet);
                    ws.Locale = CultureInfo.InvariantCulture;
                    adaptor.FillSchema(ws, SchemaType.Source);
                    adaptor.Fill(ws);
                }
            }

            return ws;
        }
Example #26
0
        public DataTable GetTable(string strTableName)
        {
            try
            {
                //Open and query
                if (_oleConn ==null) Open();
                if (_oleConn.State != ConnectionState.Open)
                    throw new Exception("Connection cannot open error.");
                if (SetSheetQuerySelect()==false) return null;

                //Fill table
                OleDbDataAdapter oleAdapter = new OleDbDataAdapter();
                oleAdapter.SelectCommand = _oleCmdSelect;
                DataTable dt = new DataTable(strTableName);
                oleAdapter.FillSchema(dt,SchemaType.Source);
                oleAdapter.Fill(dt);
                if (this.Headers ==false)
                {
                    if (_strSheetRange.IndexOf(":")>0)
                    {
                        string FirstCol = _strSheetRange.Substring(0,_strSheetRange.IndexOf(":")-1);
                        int intCol = this.ColNumber(FirstCol);
                        for (int intI=0;intI<dt.Columns.Count;intI++)
                        {
                            dt.Columns[intI].Caption =ColName(intCol+intI);
                        }
                    }
                }
                SetPrimaryKey(dt);
                //Cannot delete rows in Excel workbook
                dt.DefaultView.AllowDelete =false;

                //Clean up
                _oleCmdSelect.Dispose();
                _oleCmdSelect=null;
                oleAdapter.Dispose();
                oleAdapter=null;
                if (KeepConnectionOpen==false) Close();
                return dt;

            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
Example #27
0
        /// <summary>
        /// Update one row
        /// </summary>
        /// <param name="pFileString">File String</param>
        /// <param name="pUpdateSQL">Update SQL Statement</param>
        /// <param name="pCommand">Command</param>
        /// <returns>bool</returns>
        public static bool Update_1_Row(string pFileString, string pSelectSQL, string pKeyString, System.Data.Common.DbDataReader pDataReader)
        {
            bool result = false;

            System.Data.SqlClient.SqlConnection Conn = null;
            System.Data.OleDb.OleDbConnection ConnOle = null;
            System.Data.SqlClient.SqlDataAdapter Adapter = null;
            System.Data.OleDb.OleDbDataAdapter AdapterOle = null;
            System.Data.SqlClient.SqlCommandBuilder builderSQL = null;
            System.Data.OleDb.OleDbCommandBuilder builderOLE = null;
            System.Data.SqlClient.SqlCommand cmdSqL = null;
            System.Data.Common.DbCommand cmdOle = null;

            DataSet dataSet = new DataSet();
            DataTable Temp = new DataTable();

            string ConnectionString = DBReadExecute.ParseConnectionString(pFileString);
            StringBuilder UpdateSQL = new StringBuilder();

            try
            {
            Type SQLServerType = Type.GetType("Epi.Data.SqlServer.SqlDBFactory, Epi.Data.SqlServer");
            if (DBReadExecute.DataSource.GetType().AssemblyQualifiedName == SQLServerType.AssemblyQualifiedName)
            {

                //case DBReadExecute.enumDataSouce.SQLServer:
                Conn = new System.Data.SqlClient.SqlConnection(ConnectionString);
                Adapter = new System.Data.SqlClient.SqlDataAdapter(pSelectSQL, Conn);
                //Adapter.FillSchema(dataSet, SchemaType.Source, pDestinationTableName);
                Adapter.FillSchema(dataSet, SchemaType.Source);
                builderSQL = new System.Data.SqlClient.SqlCommandBuilder(Adapter);
                Conn.Open();

                cmdSqL = Conn.CreateCommand();
                cmdSqL = builderSQL.GetInsertCommand();
                cmdSqL.CommandTimeout = 1500;

                UpdateSQL.Append("Update ");
                UpdateSQL.Append(pSelectSQL.Replace("Select * From ", ""));
                UpdateSQL.Append(" Set ");
                foreach (System.Data.SqlClient.SqlParameter param in cmdSqL.Parameters)
                {
                    //string FieldName = param.ParameterName.TrimStart(new char[] { '@' });
                    string FieldName = param.SourceColumn;
                    try
                    {

                        StringBuilder TUpdateSQL = new StringBuilder();

                        if (pDataReader[FieldName] != DBNull.Value && !string.IsNullOrEmpty(pDataReader[FieldName].ToString()))
                        {
                            TUpdateSQL.Append("[");
                            TUpdateSQL.Append(FieldName);
                            TUpdateSQL.Append("]=");

                            switch (pDataReader[FieldName].GetType().ToString())
                            {
                                case "System.Boolean":
                                    if (Convert.ToBoolean(pDataReader[FieldName]) == false)
                                    {
                                        TUpdateSQL.Append("0");
                                    }
                                    else
                                    {
                                        TUpdateSQL.Append("1");
                                    }
                                    break;
                                case "System.Int32":
                                case "System.Decimal":
                                case "System.Double":
                                case "System.Single":
                                case "System.Byte":
                                    TUpdateSQL.Append(pDataReader[FieldName].ToString().Replace("'", "''"));
                                    break;
                                default:
                                    TUpdateSQL.Append("'");
                                    TUpdateSQL.Append(pDataReader[FieldName].ToString().Replace("'", "''"));
                                    TUpdateSQL.Append("'");
                                    break;
                            }
                            TUpdateSQL.Append(",");
                        }

                        UpdateSQL.Append(TUpdateSQL);
                    }
                    catch (Exception ex)
                    {
                        // do nothing
                    }

                }
                UpdateSQL.Length = UpdateSQL.Length - 1;
                UpdateSQL.Append(" Where ");
                UpdateSQL.Append(pKeyString);
                //builderOLE = null;
                cmdSqL = null;
                cmdSqL = Conn.CreateCommand();
                cmdSqL.CommandText = UpdateSQL.ToString();
                cmdSqL.ExecuteNonQuery();
                //break;
            }
            else
            {

                    //case DBReadExecute.enumDataSouce.MSAccess:
                    //case DBReadExecute.enumDataSouce.MSAccess2007:
                    //case DBReadExecute.enumDataSouce.MSExcel:
                    //case DBReadExecute.enumDataSouce.MSExcel2007:
                        ConnOle = new System.Data.OleDb.OleDbConnection(ConnectionString);
                        AdapterOle = new System.Data.OleDb.OleDbDataAdapter(pSelectSQL, ConnOle);
                        //Adapter.FillSchema(dataSet, SchemaType.Source, pDestinationTableName);
                        AdapterOle.FillSchema(dataSet, SchemaType.Source);
                        AdapterOle.Fill(Temp);
                        builderOLE = new System.Data.OleDb.OleDbCommandBuilder();
                        builderOLE.DataAdapter = AdapterOle;

                        ConnOle.Open();
                        cmdOle = ConnOle.CreateCommand();
                        cmdOle = builderOLE.GetInsertCommand();
                        cmdOle.CommandTimeout = 1500;

                        UpdateSQL.Append("Update ");
                        UpdateSQL.Append(pSelectSQL.Replace("Select * From ", ""));
                        UpdateSQL.Append(" Set ");
                        foreach (System.Data.OleDb.OleDbParameter param in cmdOle.Parameters)
                        {
                            //string FieldName = param.ParameterName.TrimStart(new char[] { '@' });
                            string FieldName = param.SourceColumn;
                            try
                            {

                                StringBuilder TUpdateSQL = new StringBuilder();

                                if (pDataReader[FieldName] != DBNull.Value && !string.IsNullOrEmpty(pDataReader[FieldName].ToString()))
                                {
                                    TUpdateSQL.Append("[");
                                    TUpdateSQL.Append(FieldName);
                                    TUpdateSQL.Append("]=");

                                    switch (pDataReader[FieldName].GetType().ToString())
                                    {

                                        case "System.Int32":
                                        case "System.Decimal":
                                        case "System.Boolean":
                                        case "System.Double":
                                        case "System.Single":
                                        case "System.Byte":
                                            TUpdateSQL.Append(pDataReader[FieldName].ToString().Replace("'", "''"));
                                            break;
                                        default:
                                            TUpdateSQL.Append("'");
                                            TUpdateSQL.Append(pDataReader[FieldName].ToString().Replace("'", "''"));
                                            TUpdateSQL.Append("'");
                                            break;
                                    }
                                    TUpdateSQL.Append(",");
                                }

                                UpdateSQL.Append(TUpdateSQL);

                            }
                            catch (Exception ex)
                            {
                                // do nothing
                            }
                        }
                        UpdateSQL.Length = UpdateSQL.Length - 1;
                        UpdateSQL.Append(" Where ");
                        UpdateSQL.Append(pKeyString);
                        builderOLE = null;
                        cmdOle = null;
                        cmdOle = ConnOle.CreateCommand();
                        cmdOle.CommandText = UpdateSQL.ToString();

                        //DBReadExecute.ExecuteSQL(pFileString, InsertSQL.ToString());

                        cmdOle.ExecuteNonQuery();
                        //break;
                }

            }
            catch (System.Exception ex)
            {
                Logger.Log(DateTime.Now + ":  " + ex.Message);
            }
            finally
            {
                if (Conn != null)
                {
                    Conn.Close();
                }

                if (ConnOle != null)
                {
                    ConnOle.Close();
                }

            }

            result = true;
            return result;
        }
Example #28
0
    protected void PKHOpenDatabase(string filename)
    {
      string tableName = "Species";

      OleDbConnection conn = new OleDbConnection(@"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + filename);
      conn.Open();
      //Putting things in Try {} Catch{} is the worst form of flow control, or so they say.
      //But there seems no other option
      DataTable t = new DataTable();
      OleDbDataAdapter da = new OleDbDataAdapter("SELECT * FROM " + tableName, conn);
      da.FillSchema(t, SchemaType.Source);

      m_SpecieDataTable.PrimaryKey = new DataColumn[0];

      m_SpecieDataAdapter = new OleDbDataAdapter("SELECT * FROM " + tableName, conn);
      m_SpecieDataTable.Clear();
      m_SpecieDataAdapter.FillSchema(m_SpecieDataTable, SchemaType.Source);
      m_SpecieDataAdapter.Fill(m_SpecieDataTable);

      new OleDbCommandBuilder(m_SpecieDataAdapter);

      UpdateFilter();

      conn.Close();

      //Create a dummy row to prevent any sort of errors...
      if (m_SpecieDataTable.Rows.Count == 0)
      {
        CreateSpecies();
        //tcSpecies.SelectedTab = tabDatabase;
      }

      //specieDatabaseControl1.DatabaseChanged();
    }
Example #29
0
    protected void OldOpenDatabase(string filename)
    {
      string tableName = "Species";

      OleDbConnection conn = new OleDbConnection(@"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + filename);
      conn.Open();
      //Putting things in Try {} Catch{} is the worst form of flow control, or so they say.
      //But there seems no other option
      DataTable t = new DataTable();
      OleDbDataAdapter da = new OleDbDataAdapter("SELECT * FROM " + tableName, conn);
      da.FillSchema(t, SchemaType.Source);

      #region Change column layout to what is expected:
      Dictionary<string, string> Remapping = new Dictionary<string, string>(); //<To, From>
      List<string> NewColumns = new List<string>();

      //Check for any columns that must be renamed:
      foreach (DataColumn c in t.Columns)
        if (c.ColumnName == "Rho")
          Remapping.Add("Density", "Rho");
        else if (c.ColumnName == "dHf")
          Remapping.Add("Hf25", "dHf");
        else if (c.ColumnName == "S°298")
          Remapping.Add("S25", "S°298");
      //Check for any columns that must be added
      foreach (string s in ReqColumnList)
        if (!t.Columns.Contains(s) && !Remapping.ContainsKey(s))
          NewColumns.Add(s);


      OleDbCommand cmd = new OleDbCommand();
      cmd.Connection = conn;
      if (NewColumns.Count > 0)
      {
        for (int i = 0; i < NewColumns.Count; i++)
        {
          if (NewColumns[i] == "Ts" || NewColumns[i] == "Te")
            cmd.CommandText = "ALTER TABLE " + tableName + " ADD " + NewColumns[i] + " single;";
          else
            cmd.CommandText = "ALTER TABLE " + tableName + " ADD " + NewColumns[i] + " varchar;";
          cmd.ExecuteNonQuery();
        }
      }
      if (Remapping.Count > 0)
      {
        foreach (KeyValuePair<string, string> kvp in Remapping)
        {
          cmd.CommandText = "ALTER TABLE " + tableName + " ADD COLUMN " + kvp.Key + " varchar;";
          cmd.ExecuteNonQuery();
        }
        StringBuilder sb = new StringBuilder("UPDATE " + tableName + " SET ");
        int j = 0;
        foreach (KeyValuePair<string, string> kvp in Remapping)
        {
          sb.Append(kvp.Key + " = " + kvp.Value);
          if (j++ != Remapping.Count - 1)
            sb.Append(", ");
        }
        sb.AppendLine(";");
        cmd.CommandText = sb.ToString();
        cmd.ExecuteNonQuery();
        /*foreach (KeyValuePair<string, string> kvp in Remapping)
        {
            cmd.CommandText = "UPDATE " + tableName + " SET " + kvp.Key + " = " + kvp.Value;
            cmd.ExecuteNonQuery();
        }*/

        foreach (KeyValuePair<string, string> kvp in Remapping)
        {
          cmd.CommandText = "ALTER TABLE " + tableName + " DROP COLUMN " + kvp.Value + ";";
          cmd.ExecuteNonQuery();
        }
      }
      #endregion Column Layout

      #region Check Constraints:
      List<DataColumn> PK = new List<DataColumn>(t.PrimaryKey);
      if (t.PrimaryKey.Length == 0) //If we need to remove or change the primary key...
      {
        cmd.CommandText = "ALTER TABLE " + tableName + " ADD CONSTRAINT pk PRIMARY KEY(Compound, Phase, Ts, Te)";
        cmd.ExecuteNonQuery();
      }

      foreach (DataColumn c in t.Columns)
        if (c.DataType == typeof(string) && !Remapping.ContainsValue(c.ColumnName))
        {
          cmd.CommandText = "ALTER TABLE " + tableName + " ALTER COLUMN " + c.ColumnName + " varchar(255)";
          cmd.ExecuteNonQuery();
        }

      /*Dictionary<string, string> RequiredConstraints = new Dictionary<string, string>();
      //RequiredConstraints.Add("Occ_gls", "CHECK (Occurence IN ('g', 'l', 's', 'G', 'L', 'S'))");
      RequiredConstraints.Add("Tgt0", "CHECK (Ts > 0 and Te > 0)"); //We're not going to force Te > Ts, but rather allow the user to make the change...
      foreach (string s in RequiredConstraints.Keys)
          if (!t.Constraints.Contains(s))
          {
              cmd.CommandText = "ALTER TABLE " + tableName + " ADD CONSTRAINT " + s + " " + RequiredConstraints[s];
              cmd.ExecuteNonQuery();
          }*/
      //It seems that it really doesn't like these constraints for an unkown reason.


      #endregion Check Constraints

      m_SpecieDataAdapter = new OleDbDataAdapter("SELECT * FROM " + tableName, conn);

      m_SpecieDataTable.Clear();
      m_SpecieDataAdapter.FillSchema(m_SpecieDataTable, SchemaType.Source);
      m_SpecieDataAdapter.Fill(m_SpecieDataTable);

      new OleDbCommandBuilder(m_SpecieDataAdapter);

      #region Commented Out Code
      //Alternative (2-table) method:
      /*
      try
      {
          m_SpecieDataAdapter = new OleDbDataAdapter("SELECT * FROM Species2", conn);

          m_SpecieDataAdapter.Fill(m_SpecieDataTable);

          new OleDbCommandBuilder(m_SpecieDataAdapter);
      }
      catch
      {
          m_SpecieDataAdapter = new OleDbDataAdapter("SELECT * FROM " + tableName, conn);

          DataTableMapping mapping = m_SpecieDataAdapter.TableMappings.Add("Table", "" + tableName);
          mapping.ColumnMappings.Add("Rho", "Density");
          mapping.ColumnMappings.Add("dHf", "Hf25");
          mapping.ColumnMappings.Add("S°298", "S25");

          OleDbCommandBuilder cb = new OleDbCommandBuilder(m_SpecieDataAdapter);

          DataTable intermediate = m_SpecieDataTable.Clone();
          m_SpecieDataAdapter.Fill(intermediate);
          foreach (DataRow r in intermediate.Rows)
              m_SpecieDataTable.ImportRow(r);

          MergeRows();

          CreateNewTable(conn);

          m_SpecieDataAdapter.SelectCommand = new OleDbCommand("SELECT * FROM Species2", conn);

          SaveDatabase();
      }*/
      #endregion Commented Out Code

      MergeRows();

      SaveDatabase();

      //After we have merged the variables, we no longer need to have a 4 column primary key (Ts and Te can be removed).
      m_SpecieDataTable.PrimaryKey = new DataColumn[] { m_SpecieDataTable.Columns["Compound"], m_SpecieDataTable.Columns["Phase"] };
      cmd.CommandText = "ALTER TABLE " + tableName + " DROP CONSTRAINT pk"; cmd.ExecuteNonQuery();
      cmd.CommandText = "ALTER TABLE " + tableName + " ADD CONSTRAINT pk PRIMARY KEY(Compound, Phase)"; cmd.ExecuteNonQuery();

      UpdateFilter();
      conn.Close();

      //Create a dummy row to prevent any sort of errors...
      if (m_SpecieDataTable.Rows.Count == 0)
      {
        CreateSpecies();
        //tcSpecies.SelectedTab = tabDatabase;
      }

      //specieDatabaseControl1.DatabaseChanged();
    }
        /// <summary>
        /// Runs when the Upload Button is clicked.
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        protected void btnUpload_Click(object sender, EventArgs e)
        {
            //Checking file name if file is uploaded.
            if (fuQuestions.HasFile)
            {
                FileInfo fileInfo = new FileInfo(fuQuestions.PostedFile.FileName);

                //If file does not have the extension .csv an error message is displayed.
                if (fileInfo.Name.Contains(".csv"))
                {
                    string fileName = fileInfo.Name.Replace(".csv", "").ToString();
                    string csvFilePath = Server.MapPath("UploadedCSVFiles") + "\\" + fileInfo.Name;

                    //Save the CSV file in the Server inside 'UploadedCSVFiles'
                    fuQuestions.SaveAs(csvFilePath);

                    //Fetch the location of CSV file
                    string filePath = Server.MapPath("UploadedCSVFiles") + "\\";
                    string strSql = "SELECT * FROM [" + fileInfo.Name + "]";
                    string strCSVConnString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + filePath + ";" + "Extended Properties='text;HDR=YES;'";

                    //The file is written based on the schema in the writeSchema method of the Question Class.
                    Question writeQuestion = new Question();
                    string temp = writeQuestion.writeSchema(filePath, fileInfo.Name);
                    if (temp != "OK")
                    {
                        setLabel(temp, "red");
                    }

                    // load the data from CSV to DataTable
                    OleDbDataAdapter adapter = new OleDbDataAdapter(strSql, strCSVConnString);
                    DataTable dtCSV = new DataTable();
                    DataTable dtSchema = new DataTable();
                    adapter.FillSchema(dtCSV, SchemaType.Mapped);
                    adapter.Fill(dtCSV);

                    //Checks of the DataTable has at least one row.
                    if (dtCSV.Rows.Count > 0)
                    {
                        //Checks for Row Headers.
                        if (dtCSV.Rows[0][0].ToString() == "UploadCatName")
                        {
                            dtCSV.Rows[0].Delete();
                            dtCSV.AcceptChanges();
                        }
                        GridView1.DataSource = dtCSV;
                        GridView1.DataBind();

                        //The insertCategory, insertQuestion, insertAnswer and insertExplanation methods are all housed
                        //in the Question class file. Category and Question IDs must be returned for the Answer and Explanation
                        //insert methods to run since they require foreign keys to insert. If a -1 is returned for either
                        //of the two, an error message is displayed.
                        for (int i = 0; i < dtCSV.Rows.Count; i++)
                        {
                            int catID = writeQuestion.insertCategory(dtCSV.Rows[i][0].ToString(), dtCSV.Rows[i][1].ToString());
                            if (catID != -1)
                            {
                                User currentUser = (User)Session["User"];
                                int questionID = writeQuestion.insertQuestion(catID, currentUser.UserName, dtCSV.Rows[i][2].ToString());

                                if (questionID != -1)
                                {
                                    temp = writeQuestion.insertAnswer(questionID, dtCSV.Rows[i][3].ToString(), dtCSV.Rows[i][4].ToString(), dtCSV.Rows[i][5].ToString(), dtCSV.Rows[i][6].ToString(), dtCSV.Rows[i][7].ToString(), dtCSV.Rows[i][8].ToString());
                                    if (temp == "OK")
                                    {
                                        temp = writeQuestion.insertExplanation(questionID, dtCSV.Rows[i][9].ToString());
                                        if (temp !="OK")
                                            setLabel(temp, "#F72862");
                                    }
                                    else
                                        setLabel(temp, "#F72862");
                                }
                                else
                                    setLabel("An Error has Occurred.", "#F72862");
                            }
                            else
                                setLabel("An Error has Occurred.", "#F72862");
                        }
                        setLabel(string.Format("({0}) record(s) have been loaded to the database.", dtCSV.Rows.Count), "#15E626");
                    }
                    else
                        setLabel("File is empty.", "#F72862");
                }
                else
                    setLabel("Unable to recognize file.", "#F72862");
            }
        }