private void button1_Click(object sender, EventArgs e) { try { string sql = null; string data = null; int i = 0; int j = 0; Exce.Application xlApp; Exce.Workbook xlWorkBook; Exce.Worksheet xlWorkSheet; object misValue = System.Reflection.Missing.Value; xlApp = new Exce.Application(); xlWorkBook = xlApp.Workbooks.Add(misValue); xlWorkSheet = (Exce.Worksheet)xlWorkBook.Worksheets.get_Item(1); connection.Open(); sql = "select re_no, re_date, c_name, payment_type, invoice_type, payment_mode, ref_no, ref_date, in_no, in_date, total_amount, due_amount, receive_amount, notes, total_receive from payment_receipt where (due_amount <> '0')"; OleDbDataAdapter dscmd = new OleDbDataAdapter(sql, connection); DataSet ds = new DataSet(); dscmd.Fill(ds); xlWorkSheet.Cells[1, 1] = "Receipt No"; xlWorkSheet.Cells[1, 2] = "Receipt Date"; xlWorkSheet.Cells[1, 3] = "Customer Name"; xlWorkSheet.Cells[1, 4] = "Payment Type"; xlWorkSheet.Cells[1, 5] = "Invoice Type"; xlWorkSheet.Cells[1, 6] = "Payment Mode"; xlWorkSheet.Cells[1, 7] = "Reference No"; xlWorkSheet.Cells[1, 8] = "Reference Date"; xlWorkSheet.Cells[1, 9] = "Invoice No"; xlWorkSheet.Cells[1, 10] = "Invoice Date"; xlWorkSheet.Cells[1, 11] = "Total Amount"; xlWorkSheet.Cells[1, 12] = "Due Amount"; xlWorkSheet.Cells[1, 13] = "Receive Amount"; xlWorkSheet.Cells[1, 14] = "Notes"; xlWorkSheet.Cells[1, 15] = "Total Receive Amount"; for (i = 0; i <= ds.Tables[0].Rows.Count - 1; i++) { for (j = 0; j <= ds.Tables[0].Columns.Count - 1; j++) { data = ds.Tables[0].Rows[i].ItemArray[j].ToString(); xlWorkSheet.Cells[i + 2, j + 1] = data; } } xlWorkBook.SaveAs("Payment Due Report.xls", Exce.XlFileFormat.xlWorkbookNormal, misValue, misValue, misValue, misValue, Exce.XlSaveAsAccessMode.xlExclusive, misValue, misValue, misValue, misValue, misValue); xlWorkBook.Close(true, misValue, misValue); xlApp.Quit(); releaseObject(xlWorkSheet); releaseObject(xlWorkBook); releaseObject(xlApp); MessageBox.Show("Excel file created , you can find the file C:\\Users\\User\\Documents. Payment Due Report.xls"); } catch (Exception) { } finally { connection.Close(); } }
/// <summary> /// 将Excel数据导入到List表链中 /// </summary> /// <param name="xlstable">Excel文件名</param> /// <param name="xlssheet">工作簿</param> /// <returns></returns> protected static bool ImportExcelToList(string ExcelFileName, string xlssheet) { try { //打开Excel表,并写入DataSet中。 //string xlsfile = ExcelFileName; //string strConn = "Provider=Microsoft.ACE.OLEDB.12.0;" + "data source=" + xlsfile // + ";Extended Properties='Excel 8.0; HDR=YES; IMEX=1'"; string strConn = "Provider=Microsoft.Jet.OleDb.4.0;" + "data source=" + ExcelFileName + ";Extended Properties='Excel 8.0; HDR=YES; IMEX=1'"; OleDbConnection conn = new OleDbConnection(strConn); conn.Open(); DataSet ds = new DataSet(); OleDbDataAdapter odda = new OleDbDataAdapter("select * from [" + xlssheet + "$]", conn); odda.Fill(ds, "table1"); conn.Close(); //读取DataSet数据集 ltExcelTags.Clear(); //////////////////////////////////////////////////////////////// System.Data.DataTable dt = ds.Tables[0]; int nAll = dt.Rows.Count; int nValid = 0; foreach (System.Data.DataRow dr in dt.Rows) { if (dr["TagID"].ToString() != "") { WS_ExcelTag exlstag = new WS_ExcelTag(); exlstag.TagID = dr["TagID"].ToString().Trim(); exlstag.UnitCode = dr["UnitCode"].ToString().Trim(); exlstag.TagCode = dr["TagCode"].ToString().Trim(); exlstag.TagDesc = dr["TagDesc"].ToString().Trim(); exlstag.TagType = dr["TagType"].ToString().Trim(); exlstag.TagEngunit = dr["TagEngunit"].ToString().Trim(); exlstag.TagIsValid = dr["TagIsValid"].ToString().Trim(); exlstag.TagRunExp = dr["TagRunExp"].ToString().ToUpper().Trim(); exlstag.TagShiftExp = dr["TagShiftExp"].ToString().ToUpper().Trim(); exlstag.TagPeriodExp = dr["TagPeriodExp"].ToString().ToUpper().Trim(); exlstag.TagMWExp = dr["TagMWExp"].ToString().ToUpper().Trim(); exlstag.TagSnapExp = dr["TagSnapExp"].ToString().ToUpper().Trim(); exlstag.TagTargetExp = dr["TagTargetExp"].ToString().ToUpper().Trim(); exlstag.TagScoreExp = dr["TagScoreExp"].ToString().ToUpper().Trim(); exlstag.TagPeriodExp = dr["TagPeriodExp"].ToString().ToUpper().Trim(); //sub tag ltExcelTags.Add(exlstag); nValid += 1; } } string strInfor = "Excel表总数为:{0}个, 【ID不为空】有效点为:{1}个。"; strInfor = string.Format(strInfor, nAll, nValid); LogUtil.LogMessage(strInfor); return(true); } catch (Exception ex) { LogUtil.LogMessage("Excel表读取失败:" + ex.ToString()); return(false); } }
/// <summary> /// 获取Excel数据 /// </summary> /// <param name="sheetName"></param> /// <param name="fileName"></param> /// <returns></returns> public static DataTable GetExcelTable(string sheetName, string fileName) { if (string.IsNullOrEmpty(sheetName)) { string[] names = GetExcelSheetNames(fileName); if (names == null) { sheetName = "sheet1"; } else { sheetName = names[0]; } } if (!sheetName.EndsWith("$")) { sheetName = sheetName + "$"; } DataSet ds = null; string connString = string.Empty; string sql = ""; connString = GetExcelConnString(fileName); sql = "SELECT * FROM [" + sheetName + "]"; OleDbConnection conn = null; OleDbDataAdapter cmd = null; try { conn = new OleDbConnection(connString); conn.Open(); cmd = new OleDbDataAdapter(sql, connString); ds = new DataSet(); cmd.Fill(ds, "TABLE1"); } catch (Exception ex) { string msg = "GetExcelTable:读取Excel出现错误\r\n"; if (ex.Message.IndexOf("外部表不是预期的格式") > 0) { msg += "非Excel2003格式\r\n"; } throw new ApplicationException(msg + ex.Message); } finally { if (conn != null) { conn.Close(); conn.Dispose(); } } if (ds != null && ds.Tables.Count > 0) { return(ds.Tables[0]); } return(null); }
/// <summary> /// 从指定(绝对路径)Excel文件读取相应的数据到DataTable中 /// Excel文件不能加密 /// </summary> /// <param name="excelFile">Excel文件名(绝对路径)</param> /// <param name="SelectCols">拟读取的列名,默认为*</param> /// <param name="where">过滤条件</param> /// <param name="orderby">排序字段</param> /// <returns></returns> public static DataTable GetDataFromExcel(string excelFile, string SelectCols = "", string where = "", string orderby = "") { DataTable result = new DataTable(); try { FileInfo file = new FileInfo(excelFile); if (!file.Exists) { throw new Exception(excelFile + " 文件不存在"); } string extension = file.Extension; string cnnString = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + excelFile + ";Extended Properties='Excel 12.0 XML;HDR=YES;IMEX=1;';"; if (extension == ".xls") { cnnString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + excelFile + ";Extended Properties='Excel 8.0;HDR=Yes;IMEX=1;'"; } OleDbConnection dbCnn = new OleDbConnection(cnnString); dbCnn.Open(); result = dbCnn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null); if (result != null && result.Rows.Count > 0) { result.TableName = result.Rows[0]["TABLE_NAME"].ToString(); } string sql = SelectCols; if (sql.Trim().Length == 0) { sql = "Select * "; } else { sql = "Select " + sql; } sql = sql + " from [" + result + "A:BU] "; if (where.Trim().Length > 0) { sql = sql + " Where " + where; } if (orderby.Trim().Length > 0) { sql = sql + " Order by " + orderby; } OleDbCommand cmd = new OleDbCommand(sql, dbCnn); System.Data.OleDb.OleDbDataAdapter da = new OleDbDataAdapter(cmd); result = new DataTable(); da.Fill(result); dbCnn.Close(); dbCnn = null; GC.Collect(); return(result); } catch (Exception err) { throw err; } }
public DataTable GetDataTable(string SQLQuery) { DataTable dt = new DataTable(); if (SQLQuery == "") { dt = null; } else { switch (_DBType) { case 1: case 2: using (SqlConnection sqlConn = new SqlConnection(SystemInfo.ConnStr)) { sqlConn.Open(); SqlDataAdapter sqlDA = new SqlDataAdapter(SQLQuery, sqlConn); if (sqlDA.SelectCommand != null) { sqlDA.SelectCommand.CommandTimeout = CommandTimeout; } if (sqlDA.DeleteCommand != null) { sqlDA.DeleteCommand.CommandTimeout = CommandTimeout; } if (sqlDA.UpdateCommand != null) { sqlDA.UpdateCommand.CommandTimeout = CommandTimeout; } sqlDA.Fill(dt); sqlDA.Dispose(); sqlDA = null; } break; case 255: using (OleDbConnection oleConn = new OleDbConnection(SystemInfo.ConnStr)) { oleConn.Open(); OleDbDataAdapter oleDA = new OleDbDataAdapter(SQLQuery, oleConn); if (oleDA.SelectCommand != null) { oleDA.SelectCommand.CommandTimeout = CommandTimeout; } if (oleDA.DeleteCommand != null) { oleDA.DeleteCommand.CommandTimeout = CommandTimeout; } if (oleDA.UpdateCommand != null) { oleDA.UpdateCommand.CommandTimeout = CommandTimeout; } oleDA.Fill(dt); oleDA.Dispose(); oleDA = null; } break; } } return(dt); }
public override void RunCommand(object sender) { //create engine, instance, query var engine = (AutomationEngineInstance)sender; var query = v_Query.ConvertUserVariableToString(engine); //define connection var databaseConnection = (OleDbConnection)v_InstanceName.GetAppInstance(engine); var queryExecutionType = v_QueryType.ConvertUserVariableToString(engine); //define commad var oleCommand = new OleDbCommand(query, databaseConnection); //add parameters foreach (DataRow rw in v_QueryParameters.Rows) { var parameterName = rw.Field <string>("Parameter Name").ConvertUserVariableToString(engine); var parameterValue = rw.Field <string>("Parameter Value").ConvertUserVariableToString(engine); var parameterType = rw.Field <string>("Parameter Type").ConvertUserVariableToString(engine); object convertedValue = null; //"STRING", "BOOLEAN", "DECIMAL", "INT16", "INT32", "INT64", "DATETIME", "DOUBLE", "SINGLE", "GUID", "BYTE", "BYTE[]" switch (parameterType) { case "STRING": convertedValue = parameterValue; break; case "BOOLEAN": convertedValue = Convert.ToBoolean(parameterValue); break; case "DECIMAL": convertedValue = Convert.ToDecimal(parameterValue); break; case "INT16": convertedValue = Convert.ToInt16(parameterValue); break; case "INT32": convertedValue = Convert.ToInt32(parameterValue); break; case "INT64": convertedValue = Convert.ToInt64(parameterValue); break; case "DATETIME": convertedValue = Convert.ToDateTime(parameterValue); break; case "DOUBLE": convertedValue = Convert.ToDouble(parameterValue); break; case "SINGLE": convertedValue = Convert.ToSingle(parameterValue); break; case "GUID": convertedValue = Guid.Parse(parameterValue); break; case "BYTE": convertedValue = Convert.ToByte(parameterValue); break; case "BYTE[]": convertedValue = System.Text.Encoding.UTF8.GetBytes(parameterValue); break; default: throw new NotImplementedException($"Parameter Type '{parameterType}' not implemented!"); } oleCommand.Parameters.AddWithValue(parameterName, convertedValue); } if (queryExecutionType == "Return Dataset") { DataTable dataTable = new DataTable(); OleDbDataAdapter adapter = new OleDbDataAdapter(oleCommand); adapter.SelectCommand = oleCommand; databaseConnection.Open(); adapter.Fill(dataTable); databaseConnection.Close(); dataTable.TableName = v_OutputUserVariableName; engine.DataTables.Add(dataTable); dataTable.StoreInUserVariable(engine, v_OutputUserVariableName); } else if (queryExecutionType == "Execute NonQuery") { databaseConnection.Open(); var result = oleCommand.ExecuteNonQuery(); databaseConnection.Close(); result.ToString().StoreInUserVariable(engine, v_OutputUserVariableName); } else if (queryExecutionType == "Execute Stored Procedure") { oleCommand.CommandType = CommandType.StoredProcedure; databaseConnection.Open(); var result = oleCommand.ExecuteNonQuery(); databaseConnection.Close(); result.ToString().StoreInUserVariable(engine, v_OutputUserVariableName); } else { throw new NotImplementedException($"Query Execution Type '{queryExecutionType}' not implemented."); } }
protected void btnSave_Click(object sender, EventArgs e) { string dirURL = "~/files/file/Stock/"; string conn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source='" + MapPath(dirURL) + uplaodFile() + "';Extended Properties='Excel 12.0;HDR=Yes';"; // string conn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source='G:/Anam/Projects/Mavrick/gentlepark/Offline Version/Code/HO/V5/POS/Stock" + uplaodFile() + "';Extended Properties='Excel 12.0;HDR=Yes';"; OleDbConnection objConn = new OleDbConnection(conn); objConn.Open(); OleDbCommand objCmdSelect = new OleDbCommand("SELECT * FROM [Sheet1$]", objConn); OleDbDataAdapter objAdapter = new OleDbDataAdapter(); objAdapter.SelectCommand = objCmdSelect; DataSet objDataset = new DataSet(); objAdapter.Fill(objDataset); objConn.Close(); GridView1.DataSource = objDataset.Tables[0]; GridView1.DataBind(); string sql = @" IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Table_1]') AND type in (N'U')) DROP TABLE [dbo].[Table_1] CREATE TABLE [dbo].[Table_1]( [ProductName] [nvarchar](256) NULL, [Barcode] [nvarchar](50) NULL, [Style] [nvarchar](50) NULL, [Size] [nvarchar](50) NULL, [Qty] [decimal](18, 2) NULL, [Price] [decimal](18, 2) NULL ) delete Table_1; "; string ex = ""; decimal total = 0; CommonManager.SQLExec(sql); foreach (GridViewRow gvr in GridView1.Rows) { try { if (int.Parse(gvr.Cells[4].Text) > 0) { sql = @" INSERT INTO [GentlePark].[dbo].[Table_1] ([ProductName] ,[Barcode] ,[Style] ,[Size] ,[Qty] ,[Price]) VALUES ('" + gvr.Cells[1].Text + @"'--<ProductName, nvarchar(256),> ,'" + gvr.Cells[0].Text + @"'--<Barcode, nvarchar(50),> ,'" + gvr.Cells[2].Text + @"'--<Style, nvarchar(50),> ,'" + gvr.Cells[3].Text + @"'--<Size, nvarchar(50),> ," + gvr.Cells[4].Text + @"--<Qry, decimal(18,2),> ," + decimal.Parse(gvr.Cells[5].Text).ToString("0.00") + @"--<price, decimal(18,2),> ); "; total += (decimal.Parse(gvr.Cells[4].Text) * decimal.Parse(gvr.Cells[5].Text)); CommonManager.SQLExec(sql); } } catch (Exception exep) { ex += gvr.Cells[0].Text + ","; } } #region Brnach try { sql = "/*" + ex + total + @"*/ USE GentlePark Declare @ProductName nvarchar(256) Declare @ProductID int Declare @Pos_ProductID int Declare @BarCode nvarchar(256) Declare @Style nvarchar(256) Declare @Size nvarchar(256) Declare @Pos_SizeID int Declare @ExtraField1 nvarchar(256) declare @Price decimal(10,2) Declare @WorkStationID int set @WorkStationID=" + ddlShowRoom.SelectedValue + @" Declare @Pos_TransactionMasterID int Set @Pos_TransactionMasterID=" + int.Parse(ddlShowRoom.SelectedItem.Text.Split('-')[1]).ToString() + @" DECLARE product_cursor CURSOR FOR SELECT ProductName,Barcode,Qty,Price,Style,Size FROM Table_1 --where cast(Qty as int) >0 OPEN product_cursor FETCH NEXT FROM product_cursor INTO @ProductName,@BarCode,@ExtraField1,@Price,@Style,@Size WHILE @@FETCH_STATUS = 0 BEGIN Set @ProductID = (Select Count(*) from ACC_ChartOfAccountLabel4 where ChartOfAccountLabel4Text=@ProductName and ACC_HeadTypeID=3) if @ProductID = 0 BEGIN --Create Product Name INSERT INTO [ACC_ChartOfAccountLabel4] ([Code] ,[ACC_HeadTypeID] ,[ChartOfAccountLabel4Text] ,[ExtraField1] ,[ExtraField2] ,[ExtraField3] ,[AddedBy] ,[AddedDate] ,[UpdatedBy] ,[UpdatedDate] ,[RowStatusID]) VALUES ('' ,3 ,@ProductName ,SUBSTRING(@BarCode,1,5) ,'' ,'@' ,1 ,GETDATE() ,1 ,GETDATE() ,1) END Set @ProductID = (select top 1 ACC_ChartOfAccountLabel4ID from ACC_ChartOfAccountLabel4 where ChartOfAccountLabel4Text=@ProductName and ACC_HeadTypeID=3) --add Product Set @Pos_ProductID = (Select Count(*) from Pos_Product where BarCode=@BarCode) if @Pos_ProductID = 0 BEGIN if(select Count(Pos_SizeID) from Pos_Size where SizeName =@Size) =0 BEGIN set @Pos_SizeID= 46 END ELSE BEGIN set @Pos_SizeID =(select top 1 Pos_SizeID from Pos_Size where SizeName =@Size) END INSERT INTO [Pos_Product] ([ProductID] ,[ReferenceID] ,[Pos_ProductTypeID] ,[Inv_UtilizationDetailsIDs] ,[ProductStatusID] ,[ProductName] ,[DesignCode] ,[Pos_SizeID] ,[Pos_BrandID] ,[Inv_QuantityUnitID] ,[FabricsCost] ,[AccesoriesCost] ,[Overhead] ,[OthersCost] ,[PurchasePrice] ,[SalePrice] ,[OldSalePrice] ,[Note] ,[BarCode] ,[Pos_ColorID] ,[Pos_FabricsTypeID] ,[StyleCode] ,[Pic1] ,[Pic2] ,[Pic3] ,[VatPercentage] ,[IsVatExclusive] ,[DiscountPercentage] ,[DiscountAmount] ,[FabricsNo] ,[ExtraField1] ,[ExtraField2] ,[ExtraField3] ,[ExtraField4] ,[ExtraField5] ,[ExtraField6] ,[ExtraField7] ,[ExtraField8] ,[ExtraField9] ,[ExtraField10] ,[AddedBy] ,[AddedDate] ,[UpdatedBy] ,[UpdatedDate] ,[RowStatusID]) VALUES (@ProductID ,1 ,1 ,'' ,1 ,@ProductName ,'' ,@Pos_SizeID ,1 ,3 ,0 ,0 ,0 ,0 ,0 ,@Price ,0 ,'Stock entry-BR' ,@BarCode ,1 ,1 ,@Style ,'' ,'' ,'' ,0 ,1 ,0 ,0 ,'' ,'0' ,'' ,'' ,'' ,'' ,'' ,'' ,'' ,'' ,'' ,1 ,GETDATE() ,1 ,GETDATE() ,1); Set @Pos_ProductID = (Select top 1 Pos_ProductID from Pos_Product where BarCode=@BarCode) END Set @Pos_ProductID = (Select top 1 Pos_ProductID from Pos_Product where BarCode=@BarCode) --Production Transaction INSERT INTO [Pos_Transaction] ([Pos_ProductID] ,[Quantity] ,[Pos_ProductTrasactionTypeID] ,[Pos_ProductTransactionMasterID] ,[WorkStationID] ,[ExtraField1] ,[ExtraField2] ,[ExtraField3] ,[ExtraField4] ,[ExtraField5] ,[AddedBy] ,[AddedDate] ,[UpdatedBy] ,[UpdatedDate] ,[RowStatusID]) VALUES (@Pos_ProductID ,Cast(@ExtraField1 as decimal(10,2)) ,1 ,21 ,1 ,'' ,'' ,'' ,'' ,'' ,1 ,GETDATE() ,1 ,GETDATE() ,1) --accounts need to update --Issue Transaction INSERT INTO [Pos_Transaction] ([Pos_ProductID] ,[Quantity] ,[Pos_ProductTrasactionTypeID] ,[Pos_ProductTransactionMasterID] ,[WorkStationID] ,[ExtraField1] ,[ExtraField2] ,[ExtraField3] ,[ExtraField4] ,[ExtraField5] ,[AddedBy] ,[AddedDate] ,[UpdatedBy] ,[UpdatedDate] ,[RowStatusID]) VALUES (@Pos_ProductID ,Cast(@ExtraField1 as decimal(10,2)) ,9 ,@Pos_TransactionMasterID ,@WorkStationID ,'' ,'' ,'' ,'' ,'' ,1 ,GETDATE() ,1 ,GETDATE() ,1) Declare @Count int set @Count= ( select COUNT(*) from Pos_WorkStationStock where ProductID=@Pos_ProductID and WorkStationID=@WorkStationID ) if @Count = 0 BEGIN INSERT INTO [Pos_WorkStationStock] ([WorkStationID] ,[ProductID] ,[Stock]) VALUES(@WorkStationID,@Pos_ProductID ,CAST(@ExtraField1 as Decimal(10,2))); END ELSE BEGIN Update Pos_WorkStationStock set Stock += CAST(@ExtraField1 as Decimal(10,2)) where ProductID=@Pos_ProductID and WorkStationID=@WorkStationID; END FETCH NEXT FROM product_cursor INTO @ProductName,@BarCode,@ExtraField1,@Price,@Style,@Size END CLOSE product_cursor DEALLOCATE product_cursor "; } catch (Exception ex3) { } #endregion #region Centeral string sql_HeadOffice; sql_HeadOffice = "/*" + ex + total + @"*/ USE GentlePark Declare @ProductName nvarchar(256) Declare @ProductID int Declare @Pos_ProductID int Declare @BarCode nvarchar(256) Declare @Style nvarchar(256) Declare @Size nvarchar(256) Declare @Pos_SizeID int Declare @ExtraField1 nvarchar(256) declare @Price decimal(10,2) Declare @WorkStationID int set @WorkStationID=1 Declare @Pos_TransactionMasterID int Set @Pos_TransactionMasterID=20 DECLARE product_cursor CURSOR FOR SELECT ProductName,Barcode,Qty,Price,Style,Size FROM Table_1 --where cast(Qty as int) >0 OPEN product_cursor FETCH NEXT FROM product_cursor INTO @ProductName,@BarCode,@ExtraField1,@Price,@Style,@Size WHILE @@FETCH_STATUS = 0 BEGIN Set @ProductID = (Select Count(*) from ACC_ChartOfAccountLabel4 where ChartOfAccountLabel4Text=@ProductName and ACC_HeadTypeID=3) if @ProductID = 0 BEGIN --Create Product Name INSERT INTO [ACC_ChartOfAccountLabel4] ([Code] ,[ACC_HeadTypeID] ,[ChartOfAccountLabel4Text] ,[ExtraField1] ,[ExtraField2] ,[ExtraField3] ,[AddedBy] ,[AddedDate] ,[UpdatedBy] ,[UpdatedDate] ,[RowStatusID]) VALUES ('' ,3 ,@ProductName ,SUBSTRING(@BarCode,1,5) ,'' ,'@' ,1 ,GETDATE() ,1 ,GETDATE() ,1) END Set @ProductID = (select top 1 ACC_ChartOfAccountLabel4ID from ACC_ChartOfAccountLabel4 where ChartOfAccountLabel4Text=@ProductName and ACC_HeadTypeID=3) --add Product Set @Pos_ProductID = (Select Count(*) from Pos_Product where BarCode=@BarCode) if @Pos_ProductID = 0 BEGIN if(select Count(Pos_SizeID) from Pos_Size where SizeName =@Size) =0 BEGIN set @Pos_SizeID= 46 END ELSE BEGIN set @Pos_SizeID =(select top 1 Pos_SizeID from Pos_Size where SizeName =@Size) END INSERT INTO [Pos_Product] ([ProductID] ,[ReferenceID] ,[Pos_ProductTypeID] ,[Inv_UtilizationDetailsIDs] ,[ProductStatusID] ,[ProductName] ,[DesignCode] ,[Pos_SizeID] ,[Pos_BrandID] ,[Inv_QuantityUnitID] ,[FabricsCost] ,[AccesoriesCost] ,[Overhead] ,[OthersCost] ,[PurchasePrice] ,[SalePrice] ,[OldSalePrice] ,[Note] ,[BarCode] ,[Pos_ColorID] ,[Pos_FabricsTypeID] ,[StyleCode] ,[Pic1] ,[Pic2] ,[Pic3] ,[VatPercentage] ,[IsVatExclusive] ,[DiscountPercentage] ,[DiscountAmount] ,[FabricsNo] ,[ExtraField1] ,[ExtraField2] ,[ExtraField3] ,[ExtraField4] ,[ExtraField5] ,[ExtraField6] ,[ExtraField7] ,[ExtraField8] ,[ExtraField9] ,[ExtraField10] ,[AddedBy] ,[AddedDate] ,[UpdatedBy] ,[UpdatedDate] ,[RowStatusID]) VALUES (@ProductID ,1 ,1 ,'' ,1 ,@ProductName ,'' ,@Pos_SizeID ,1 ,3 ,0 ,0 ,0 ,0 ,0 ,@Price ,0 ,'Stock entry-BR' ,@BarCode ,1 ,1 ,@Style ,'' ,'' ,'' ,0 ,1 ,0 ,0 ,'' ,@ExtraField1 ,'' ,'' ,'' ,'' ,'' ,'' ,'' ,'' ,'' ,1 ,GETDATE() ,1 ,GETDATE() ,1); Set @Pos_ProductID = (Select top 1 Pos_ProductID from Pos_Product where BarCode=@BarCode) --Transaction INSERT INTO [Pos_Transaction] ([Pos_ProductID] ,[Quantity] ,[Pos_ProductTrasactionTypeID] ,[Pos_ProductTransactionMasterID] ,[WorkStationID] ,[ExtraField1] ,[ExtraField2] ,[ExtraField3] ,[ExtraField4] ,[ExtraField5] ,[AddedBy] ,[AddedDate] ,[UpdatedBy] ,[UpdatedDate] ,[RowStatusID]) VALUES (@Pos_ProductID ,Cast(@ExtraField1 as decimal(10,2)) ,1 ,@Pos_TransactionMasterID ,1 ,'' ,'' ,'' ,'' ,'' ,1 ,GETDATE() ,1 ,GETDATE() ,1) --accounts need to update END Set @Pos_ProductID = (Select top 1 Pos_ProductID from Pos_Product where BarCode=@BarCode) update Pos_Product set ExtraField1=@ExtraField1 where Pos_ProductID=@Pos_ProductID FETCH NEXT FROM product_cursor INTO @ProductName,@BarCode,@ExtraField1,@Price,@Style,@Size END CLOSE product_cursor DEALLOCATE product_cursor "; #endregion txtCursor.Text = sql; CommonManager.SQLExec(sql_HeadOffice); //CommonManager.SQLExec(sql); //DataSet ds = MSSQL.SQLExec(sql + "; select Count(*) from Mem_Fees where Comn_PaymentByID=2"); //Label1.Text = ds.Tables[0].Rows.Count + " Record(s) Added Successfully"; //btnSave.Visible = false; }
public static (ExpensePerOperation exp, bool success) ReadData(string samplingGuid, bool readComplete = false) { ExpensePerOperation exp = new ExpensePerOperation(samplingGuid); bool success = false; string sql = $"Select * from tblFishingExpense where SamplingGuid ={{{samplingGuid}}}"; using (var con = new OleDbConnection(global.ConnectionString)) { using (var dt = new DataTable()) { con.Open(); var adapter = new OleDbDataAdapter(sql, con); adapter.Fill(dt); if (dt.Rows.Count > 0) { DataRow dr = dt.Rows[0]; if (double.TryParse(dr["CostOfFishing"].ToString(), out double costOfFishing)) { SamplingExpenses = $"Cost of fishing: {costOfFishing.ToString()}\r\n"; exp.CostOfFishing = costOfFishing; } else { SamplingExpenses = $"Cost of fishing: -\r\n"; } if (double.TryParse(dr["ReturnOfInvestment"].ToString(), out double roi)) { exp.ReturnOfInvestment = roi; SamplingExpenses += $"Return of investment: {roi.ToString()}\r\n"; } else { SamplingExpenses += $"Return of investment: -\r\n"; } if (double.TryParse(dr["IncomeFromFishSold"].ToString(), out double income)) { exp.IncomeFromFishSale = income; SamplingExpenses += $"Income from fish sales: {income.ToString()}\r\n"; } else { SamplingExpenses += $"Income from fish sales: -\r\n"; } if (double.TryParse(dr["FishWeightForConsumption"].ToString(), out double weightFishConsumed)) { exp.WeightFishConsumed = weightFishConsumed; SamplingExpenses += $"Weight of fish consumed: {weightFishConsumed.ToString()}"; } else { SamplingExpenses += $"Weight of fish consumed: -"; } success = true; } else { SamplingExpenses = "Cost of fishing: -\r\nReturn of investment: -\r\nIncome from fish sales: -\r\nWeight of fish consumed: -"; } } if (success) { Dictionary <string, FishingExpenseItemsPerOperation> expenseItemsList = new Dictionary <string, FishingExpenseItemsPerOperation>(); sql = $"Select * from tblFishingExpenseItems where SamplingGuid={{{samplingGuid}}}"; //using (var con1 = new OleDbConnection(global.ConnectionString)) //{ using (var dt = new DataTable()) { //con1.Open(); //var adapter = new OleDbDataAdapter(sql, con1); var adapter = new OleDbDataAdapter(sql, con); adapter.Fill(dt); if (dt.Rows.Count > 0) { SamplingExpenses += "\r\n\r\nCost items:\r\n"; foreach (DataRow dr in dt.Rows) { string key = dr["ExpenseRow"].ToString(); string item = dr["ExpenseItem"].ToString(); double cost = (double)dr["Cost"]; string unit = dr["Unit"].ToString(); double?unitQuantity = null; if (double.TryParse(dr["UnitQuantity"].ToString(), out double v)) { unitQuantity = v; } FishingExpenseItemsPerOperation expenseItem = new FishingExpenseItemsPerOperation(key, item, cost, unit, unitQuantity, fad3DataStatus.statusFromDB); SamplingExpenses += $"{item}: {cost.ToString()}\r\n"; exp.AddExpenseItem(key, expenseItem); } } } //} } return(exp, success); } }
public DataSet fetch(string query) { OleDbTransaction trans = this.conn.BeginTransaction(); OleDbCommand comm = new OleDbCommand(); comm.CommandText = query; comm.Connection = this.conn; comm.Transaction = trans; OleDbDataAdapter adapter = new OleDbDataAdapter(); adapter.SelectCommand = comm; DataSet ds = new DataSet(); adapter.Fill(ds, "tbl_0"); trans.Commit(); return ds; }
private void button1_Click(object sender, EventArgs e) { try { //BoxCash double Deposit = 0; double Withdraw = 0; double BoxCash = 0; OleDbDataAdapter da = new OleDbDataAdapter("select Deposit,Withdraw from BoxInfo", con); DataTable dt = new DataTable(); da.Fill(dt); for (int i = 0; i < dt.Rows.Count; i++) { Deposit += double.Parse(dt.Rows[i][0].ToString()); Withdraw += double.Parse(dt.Rows[i][1].ToString()); } BoxCash = Deposit - Withdraw; //********************************************************** //CusAcount double Cus_Am_Rem = 0; double C_A_Pay = 0; double CusAcount = 0; OleDbDataAdapter da1 = new OleDbDataAdapter("select Cus_Am_Rem from Cus_Account", con); DataTable dt1 = new DataTable(); da1.Fill(dt1); OleDbDataAdapter da2 = new OleDbDataAdapter("select C_A_Pay from Cus_Pay", con); DataTable dt2 = new DataTable(); da2.Fill(dt2); for (int i1 = 0; i1 < dt1.Rows.Count; i1++) { Cus_Am_Rem += double.Parse(dt1.Rows[i1][0].ToString()); } for (int i2 = 0; i2 < dt2.Rows.Count; i2++) { C_A_Pay += double.Parse(dt2.Rows[i2][0].ToString()); } CusAcount = Cus_Am_Rem - C_A_Pay; //***************************************************************** //ProValue double cash = 0; OleDbDataAdapter da3 = new OleDbDataAdapter("select Qty,Buy_Price,Sell_Price from Product", con); DataTable dt3 = new DataTable(); da3.Fill(dt3); for (int i3 = 0; i3 < dt3.Rows.Count; i3++) { if (cmbType.Text.Equals("سعر الشراء")) { cash += double.Parse(dt3.Rows[i3][0].ToString()) * double.Parse(dt3.Rows[i3][1].ToString()); } else { cash += double.Parse(dt3.Rows[i3][0].ToString()) * double.Parse(dt3.Rows[i3][2].ToString()); } } //******************************************************************* //SupAcount double Sup_A_Am = 0; double Sup_Pay = 0; double SupAcount = 0; OleDbDataAdapter da4 = new OleDbDataAdapter("select Sup_A_Am from Sup_Account", con); DataTable dt4 = new DataTable(); da4.Fill(dt4); OleDbDataAdapter da5 = new OleDbDataAdapter("select Sup_Pay from Sup_Pay", con); DataTable dt5 = new DataTable(); da5.Fill(dt5); for (int i4 = 0; i4 < dt4.Rows.Count; i4++) { Sup_A_Am += double.Parse(dt4.Rows[i4][0].ToString()); } for (int i5 = 0; i5 < dt5.Rows.Count; i5++) { Sup_Pay += double.Parse(dt5.Rows[i5][0].ToString()); } SupAcount = Sup_A_Am - Sup_Pay; //*************************************************************** txtAmount.Text = ((BoxCash + CusAcount + cash) - SupAcount).ToString(); } catch (Exception ex) { MessageBox.Show("0"); } }
//************************************************************************** /// <Description> /// This method uses to get data for C1TrueDBGrid /// </Description> /// <Inputs> /// MasterID, DetailID /// </Inputs> /// <Outputs> /// DataTable /// </Outputs> /// <Returns> /// DataTable /// </Returns> /// <Authors> /// DungLA /// </Authors> /// <History> /// 06-Jan-2005 /// 12/Oct/2005 Thachnn: fix bug injection /// </History> /// <Notes> /// </Notes> //************************************************************************** public DataTable GetDataForTrueDBGrid(string pstrMasterID, string pstrDetailID, out bool oblnIsEdit) { const string METHOD_NAME = THIS + ".GetDataForTrueDBGrid()"; string strSql = string.Empty; // create new table with 4 columns to store data and return to caller DataTable dtblSource = new DataTable(sys_ReportDrillDownTable.TABLE_NAME); dtblSource.Columns.Add(sys_ReportDrillDownTable.MASTERPARA_FLD); dtblSource.Columns.Add(sys_ReportDrillDownTable.DETAILPARA_FLD); dtblSource.Columns.Add(sys_ReportParaTable.DATATYPE_FLD); dtblSource.Columns.Add(sys_ReportDrillDownTable.FROMCOLUMN_FLD); dtblSource.Columns.Add(sys_ReportDrillDownTable.PARAORDER_FLD); // set data type for FromColumn column dtblSource.Columns[sys_ReportDrillDownTable.FROMCOLUMN_FLD].DataType = typeof(bool); // set default value for FromColumn dtblSource.Columns[sys_ReportDrillDownTable.FROMCOLUMN_FLD].DefaultValue = false; DataSet dstSource = new DataSet(); OleDbConnection oconPCS = null; OleDbCommand ocmdPCS = null; #region Existed record in sys_ReportDrillDown table // first we check for existing record in sys_ReportDrillDown table strSql = "SELECT " + sys_ReportDrillDownTable.MASTERREPORTID_FLD + "," + sys_ReportDrillDownTable.DETAILREPORTID_FLD + "," + sys_ReportDrillDownTable.MASTERPARA_FLD + "," + sys_ReportDrillDownTable.DETAILPARA_FLD + "," + sys_ReportDrillDownTable.FROMCOLUMN_FLD + "," + sys_ReportDrillDownTable.PARAORDER_FLD + " FROM " + sys_ReportDrillDownTable.TABLE_NAME + " WHERE " + sys_ReportDrillDownTable.MASTERREPORTID_FLD + "= ? " //+ pstrMasterID + "'" + " AND " + sys_ReportDrillDownTable.DETAILREPORTID_FLD + "= ? "; // + pstrDetailID + "'"; try { // if already existed, then get current data Utils utils = new Utils(); oconPCS = new OleDbConnection(Utils.Instance.OleDbConnectionString); ocmdPCS = new OleDbCommand(strSql, oconPCS); ocmdPCS.Parameters.Add(new OleDbParameter(sys_ReportDrillDownTable.MASTERREPORTID_FLD, OleDbType.VarWChar)); ocmdPCS.Parameters[sys_ReportDrillDownTable.MASTERREPORTID_FLD].Value = pstrMasterID; ocmdPCS.Parameters.Add(new OleDbParameter(sys_ReportDrillDownTable.DETAILREPORTID_FLD, OleDbType.VarWChar)); ocmdPCS.Parameters[sys_ReportDrillDownTable.DETAILREPORTID_FLD].Value = pstrDetailID; ocmdPCS.Connection.Open(); OleDbDataReader odrdPCS = ocmdPCS.ExecuteReader(); if (odrdPCS.HasRows) { while (odrdPCS.Read()) { DataRow drow = dtblSource.NewRow(); drow[sys_ReportDrillDownTable.MASTERPARA_FLD] = odrdPCS[sys_ReportDrillDownTable.MASTERPARA_FLD]; drow[sys_ReportDrillDownTable.DETAILPARA_FLD] = odrdPCS[sys_ReportDrillDownTable.DETAILPARA_FLD]; drow[sys_ReportDrillDownTable.FROMCOLUMN_FLD] = odrdPCS[sys_ReportDrillDownTable.FROMCOLUMN_FLD]; drow[sys_ReportDrillDownTable.PARAORDER_FLD] = odrdPCS[sys_ReportDrillDownTable.PARAORDER_FLD]; dtblSource.Rows.Add(drow); } sys_ReportParaDS dsSysReportPara = new sys_ReportParaDS(); foreach (DataRow drow in dtblSource.Rows) { drow[sys_ReportParaTable.DATATYPE_FLD] = dsSysReportPara.GetDataType(pstrDetailID, drow[sys_ReportDrillDownTable.DETAILPARA_FLD].ToString().Trim()); } // return oblnIsEdit = true; return(dtblSource); } } catch (OleDbException ex) { throw new PCSDBException(ErrorCode.ERROR_DB, METHOD_NAME, ex); } catch (Exception ex) { throw new PCSDBException(ErrorCode.OTHER_ERROR, METHOD_NAME, ex); } finally { if (oconPCS != null) { if (oconPCS.State != ConnectionState.Closed) { oconPCS.Close(); } } } #endregion oblnIsEdit = false; #region Not existed, make new data and return to user /// TODO: Bro.DungLA. Thachnn says: I feel bug here, when creating strSql, but I don't know why and how to fix. /// The old code is really fusion with continuing plus and plus SQL string. /// Please comeback to this point when there are any errors. try { Utils utils = new Utils(); oconPCS.ConnectionString = Utils.Instance.OleDbConnectionString; string strSqlOrder = " ORDER BY " + sys_ReportParaTable.PARAORDER_FLD + " ASC"; string strSqlMaster = "SELECT " + sys_ReportParaTable.PARAORDER_FLD + "," + sys_ReportParaTable.PARANAME_FLD + "," + sys_ReportParaTable.DATATYPE_FLD + " FROM " + sys_ReportParaTable.TABLE_NAME + " WHERE " + sys_ReportParaTable.REPORTID_FLD + "= ? " + strSqlOrder; OleDbCommand ocmdMaster = new OleDbCommand(strSqlMaster, oconPCS); ocmdMaster.Parameters.Add(new OleDbParameter(sys_ReportParaTable.REPORTID_FLD, OleDbType.VarWChar)); ocmdMaster.Parameters[sys_ReportParaTable.REPORTID_FLD].Value = pstrMasterID; // fill master para into dataset with new table OleDbDataAdapter odadMaster = new OleDbDataAdapter(ocmdMaster); odadMaster.Fill(dstSource, sys_ReportDrillDownTable.MASTERPARA_FLD); string strSqlDetail = "SELECT " + sys_ReportParaTable.PARAORDER_FLD + "," + sys_ReportParaTable.PARANAME_FLD + "," + sys_ReportParaTable.DATATYPE_FLD + " FROM " + sys_ReportParaTable.TABLE_NAME + " WHERE " + sys_ReportParaTable.REPORTID_FLD + "= ? " + strSqlOrder; OleDbCommand ocmdDetail = new OleDbCommand(strSqlDetail, oconPCS); ocmdDetail.Parameters.Add(new OleDbParameter(sys_ReportParaTable.REPORTID_FLD, OleDbType.VarWChar)); ocmdDetail.Parameters[sys_ReportParaTable.REPORTID_FLD].Value = pstrDetailID; // fill detail para into dataset with new table OleDbDataAdapter odadDetail = new OleDbDataAdapter(ocmdDetail); odadDetail.Fill(dstSource, sys_ReportDrillDownTable.DETAILPARA_FLD); // get all rows DataRowCollection MasterRows = dstSource.Tables[sys_ReportDrillDownTable.MASTERPARA_FLD].Rows; DataRowCollection DetailRows = dstSource.Tables[sys_ReportDrillDownTable.DETAILPARA_FLD].Rows; // if number of master para is bigger than number of detail para // then the detail para is basic for filling parameter values if (MasterRows.Count > DetailRows.Count) { for (int i = 0; i < MasterRows.Count; i++) { DataRow drow = dtblSource.NewRow(); drow[sys_ReportDrillDownTable.MASTERPARA_FLD] = MasterRows[i][sys_ReportParaTable.PARANAME_FLD]; drow[sys_ReportDrillDownTable.PARAORDER_FLD] = i + 1; if (i < DetailRows.Count) { drow[sys_ReportDrillDownTable.DETAILPARA_FLD] = DetailRows[i][sys_ReportParaTable.PARANAME_FLD]; drow[sys_ReportParaTable.DATATYPE_FLD] = DetailRows[i][sys_ReportParaTable.DATATYPE_FLD]; } else { break; } dtblSource.Rows.Add(drow); } } // if number of detail para is bigger than number of master para // then the master para is basic for filling parameter values else { for (int i = 0; i < DetailRows.Count; i++) { DataRow drow = dtblSource.NewRow(); drow[sys_ReportDrillDownTable.DETAILPARA_FLD] = DetailRows[i][sys_ReportParaTable.PARANAME_FLD]; drow[sys_ReportDrillDownTable.PARAORDER_FLD] = i + 1; if (i < MasterRows.Count) { drow[sys_ReportDrillDownTable.MASTERPARA_FLD] = MasterRows[i][sys_ReportParaTable.PARANAME_FLD]; drow[sys_ReportParaTable.DATATYPE_FLD] = MasterRows[i][sys_ReportParaTable.DATATYPE_FLD]; } else { drow[sys_ReportDrillDownTable.MASTERPARA_FLD] = null; drow[sys_ReportParaTable.DATATYPE_FLD] = DetailRows[i][sys_ReportParaTable.DATATYPE_FLD]; } dtblSource.Rows.Add(drow); } } oblnIsEdit = true; return(dtblSource); } catch (OleDbException ex) { throw new PCSDBException(ErrorCode.ERROR_DB, METHOD_NAME, ex); } catch (Exception ex) { throw new PCSDBException(ErrorCode.OTHER_ERROR, METHOD_NAME, ex); } finally { if (oconPCS != null) { if (oconPCS.State != ConnectionState.Closed) { oconPCS.Close(); } } } #endregion }
public void Convert(IList <string> destinationFolderPaths) { var dictionaryConfigurations = GetDictionaryConfigurations(); var connnectionString = String.Format( @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties=""Excel 12.0;HDR=No;IMEX=1"";", _sourceFilePath); using (var connection = new OleDbConnection(connnectionString)) { try { connection.Open(); } catch (Exception) { throw new ConversionException { SourceFilePath = _sourceFilePath }; } if (connection.State == ConnectionState.Open) { { var dataAdapter = new OleDbDataAdapter(String.Format("SELECT * FROM [{0}$]", SourceSheetName), connection); var dataTable = new DataTable(); try { dataAdapter.Fill(dataTable); var columnsCount = dataTable.Columns.Count; if (columnsCount > 0) { var processReading = false; var dictionaryName = String.Empty; foreach (DataRow row in dataTable.Rows) { var rowValue = row[0]?.ToString().Trim() ?? String.Empty; if (String.IsNullOrEmpty(rowValue)) { break; } if (rowValue.StartsWith("*", StringComparison.OrdinalIgnoreCase)) { processReading = true; dictionaryName = rowValue.Replace("*", ""); continue; } if (!processReading) { continue; } var dictionaryConfiguration = dictionaryConfigurations.FirstOrDefault(configItem => String.Equals(configItem.InputTagName, dictionaryName)); if (dictionaryConfiguration == null) { continue; } var listDataItem = new ListDataItem { Value = rowValue, IsDefault = String.Equals(row[1]?.ToString().Trim(), "D", StringComparison.OrdinalIgnoreCase), IsPlaceholder = String.Equals(row[1]?.ToString().Trim(), "PH", StringComparison.OrdinalIgnoreCase) }; dictionaryConfiguration.ListItems.Add(listDataItem); } } } catch { throw new ConversionException { SourceFilePath = _sourceFilePath }; } finally { dataAdapter.Dispose(); dataTable.Dispose(); } } } else { throw new ConversionException { SourceFilePath = _sourceFilePath } }; connection.Close(); } var xml = new StringBuilder(); xml.AppendLine(String.Format("<{0}>", OutputRootNodeName)); foreach (var dictionaryConfiguration in dictionaryConfigurations) { foreach (var slideHeader in dictionaryConfiguration.ListItems) { xml.Append(String.Format("<{0} ", dictionaryConfiguration.OutputNodeName)); xml.Append("Value = \"" + slideHeader.Value.Replace(@"&", "&").Replace("\"", """) + "\" "); xml.Append("IsDefault = \"" + slideHeader.IsDefault + "\" "); xml.Append("IsPlaceholder = \"" + slideHeader.IsPlaceholder + "\" "); xml.AppendLine(@"/>"); } } foreach (var externalLine in ConvertorExtensions.GetExternalOutputLines( Path.Combine(destinationFolderPaths.First(), OutputFileName), OutputRootNodeName, dictionaryConfigurations.Select(configItem => configItem.OutputNodeName).ToArray())) { xml.AppendLine(externalLine); } xml.AppendLine(String.Format("</{0}>", OutputRootNodeName)); foreach (var folderPath in destinationFolderPaths) { var xmlPath = Path.Combine(folderPath, OutputFileName); using (var sw = new StreamWriter(xmlPath, false)) { sw.Write(xml.ToString()); sw.Flush(); } } } }
public JsonResult updateExcel(StudentReg studentreg, HttpPostedFileBase FileUpload) { List <string> data = new List <string>(); if (FileUpload != null) { // tdata.ExecuteCommand("truncate table OtherCompanyAssets"); if (FileUpload.ContentType == "application/vnd.ms-excel" || FileUpload.ContentType == "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet") { string filename = FileUpload.FileName; string targetpath = Server.MapPath("/DetailFormatInExcel/"); FileUpload.SaveAs(targetpath + filename); string pathToExcelFile = targetpath + filename; var connectionString = ""; if (filename.EndsWith(".xls")) { connectionString = string.Format("Provider=Microsoft.Jet.OLEDB.4.0; data source={0}; Extended Properties=Excel 8.0;", pathToExcelFile); } else if (filename.EndsWith(".xlsx")) { connectionString = string.Format("Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties=\"Excel 12.0 Xml;HDR=YES;IMEX=1\";", pathToExcelFile); } var adapter = new OleDbDataAdapter("SELECT * FROM [Sheet1$]", connectionString); var ds = new DataSet(); adapter.Fill(ds, "ExcelTable"); DataTable dtable = ds.Tables["ExcelTable"]; string sheetName = "Sheet1"; var excelFile = new ExcelQueryFactory(pathToExcelFile); var artistAlbums = from a in excelFile.Worksheet <StudentReg>(sheetName) select a; foreach (DataRow a in dtable.Rows) { try { if (a["Student Name"] != "" && a["Address"] != "" && a["Contact"] != "") { StudentReg TU = new StudentReg(); StudentReg studentreg1 = db.StudentRegs.FirstOrDefault(); if (studentreg1 == null) { TU.RollNo = 0001; } else { var ab = db.StudentRegs.Max(x => x.RollNo); TU.RollNo = Convert.ToInt32(ab) + 1; } //TU.RollNo = Regno(rollno); //TU.RollNo = a.RollNo; TU.StudentName = a["Student Name"].ToString(); TU.FatherName = a["Father Name"].ToString(); TU.Address = a["Address"].ToString(); TU.Contact = a["Contact"].ToString(); TU.Laststudy = a["Last study"].ToString(); TU.Medical = a["Medical Done"].ToString(); TU.Refusal = a["Refusal"].ToString(); TU.Email = a["STUDENT E-MAIL ID"].ToString(); TU.Password = a["Password"].ToString(); TU.Fileno = "Jan/19"; db.StudentRegs.Add(TU); db.SaveChanges(); fees fee = new fees(); fee.studentid = TU.Fileno + "" + TU.RollNo; fee.Package = Convert.ToInt32(a["Package"]); fee.Advance = Convert.ToInt32(a["Advance"]); fee.pay = Convert.ToInt32(a["Advance"]); fee.balance = fee.Package - fee.pay; db.fees.Add(fee); db.SaveChanges(); // a.RollNo, a.StudentName, a.FatherName, a.Address, a.Contact, a.Laststudy, a.Medical, a.Refusal, a.Email, a.Password } else { //data.Add("<ul>"); //if (a.StudentName == "" || a.StudentName == null) data.Add("<li> name is required</li>"); //if (a.FatherName == "" || a.FatherName == null) data.Add("<li> Father Name is required</li>"); //if (a.Address == "" || a.Address == null) data.Add("<li> Address is required</li>"); //if (a.Contact == "" || a.Contact == null) data.Add("<li>ContactNo is required</li>"); data.Add("</ul>"); data.ToArray(); return(Json(data, JsonRequestBehavior.AllowGet)); } } catch (DbEntityValidationException ex) { foreach (var entityValidationErrors in ex.EntityValidationErrors) { foreach (var validationError in entityValidationErrors.ValidationErrors) { Response.Write("Property: " + validationError.PropertyName + " Error: " + validationError.ErrorMessage); } } } } //deleting excel file from folder if ((System.IO.File.Exists(pathToExcelFile))) { System.IO.File.Delete(pathToExcelFile); } // return RedirectToAction("Index"); return(Json("success", JsonRequestBehavior.AllowGet)); // return RedirectToAction("Index"); // Response.Redirect("Index"); } else { //alert message for invalid file format data.Add("<ul>"); data.Add("<li>Only Excel file format is allowed</li>"); data.Add("</ul>"); data.ToArray(); return(Json(data, JsonRequestBehavior.AllowGet)); } } else { data.Add("<ul>"); if (FileUpload == null) { data.Add("<li>Please choose Excel file</li>"); } data.Add("</ul>"); data.ToArray(); return(Json(data, JsonRequestBehavior.AllowGet)); } }
public void WriteData() { string sql = ""; string ConnString = GetConnectionString(); string result = System.Text.RegularExpressions.Regex.Replace(GetInputParamCalculatedValue("ColMappingRules"), @",(?=[^']*'(?:[^']*'[^']*')*[^']*$)", "~^GINGER-EXCEL-COMMA-REPLACE^~"); string[] varColMaps = result.Split(','); string sSetDataUsed = ""; using (OleDbConnection Conn = new OleDbConnection(ConnString)) { try { Conn.Open(); } catch (Exception ex) { System.Threading.Thread.Sleep(3000); Conn.Open(); Reporter.ToLog(eLogLevel.ERROR, $"Method - {MethodBase.GetCurrentMethod().Name}, Error - {ex.StackTrace}", ex); } OleDbCommand Cmd = new OleDbCommand(); Cmd.Connection = Conn; string SheetName = GetInputParamCalculatedValue("SheetName").Trim(); if (String.IsNullOrEmpty(SheetName)) { this.Error += "Sheet Name is empty or not selected. Please Select correct sheet name on action configurations"; Conn.Close(); return; } if (!SheetName.EndsWith("$")) { SheetName += "$"; } if (SelectAllRows == false) { sql = "Select TOP 1 * from [" + SheetName + "]"; } else { sql = "Select * from [" + SheetName + "]"; } string where = GetInputParamCalculatedValue("SelectRowsWhere"); if (!string.IsNullOrEmpty(where)) { sql += " WHERE " + where; } Cmd.CommandText = sql; DataTable dt = new DataTable(); OleDbDataAdapter da = new OleDbDataAdapter(); da.SelectCommand = Cmd; string updateSQL = ""; try { da.Fill(dt); if (!string.IsNullOrEmpty(GetInputParamCalculatedValue("SetDataUsed"))) { sSetDataUsed = @", " + GetInputParamCalculatedValue("SetDataUsed"); } // we expect only 1 record if (dt.Rows.Count == 1 && SelectAllRows == false) { DataRow r = dt.Rows[0]; //Read data to variables foreach (string vc in varColMaps) { string strPrimaryKeyColumn = GetInputParamCalculatedValue("PrimaryKeyColumn"); if (strPrimaryKeyColumn.Contains("`")) { strPrimaryKeyColumn = strPrimaryKeyColumn.Replace("`", ""); } string rowKey = r[strPrimaryKeyColumn].ToString(); int res; int.TryParse(rowKey, out res); if (res == 0 || r[strPrimaryKeyColumn].GetType() == typeof(System.String)) { rowKey = "'" + rowKey + "'"; } //TODO: fix me in OO Style //Do mapping string ColName = vc.Split('=')[0]; string Value = vc.Split('=')[1]; Value = Value.Replace("~^GINGER-EXCEL-COMMA-REPLACE^~", ","); string txt = Value; //keeping the translation of vars to support previous implementation VariableBase var = RunOnBusinessFlow.GetHierarchyVariableByName(Value); if (var != null) { var.Value = ValueExpression.Calculate(var.Value); txt = var.Value; } //remove '' from value txt = txt.TrimStart(new char[] { '\'' }); txt = txt.TrimEnd(new char[] { '\'' }); //TODO: create one long SQL to do the update in one time and not for each var updateSQL = @"UPDATE [" + GetInputParamCalculatedValue("SheetName") + "$] SET " + ColName + " = '" + txt + "'" + sSetDataUsed + " WHERE " + GetInputParamCalculatedValue("PrimaryKeyColumn") + "=" + rowKey + ";"; this.ExInfo += updateSQL + Environment.NewLine; OleDbCommand myCommand = new OleDbCommand(); myCommand.Connection = Conn; myCommand.CommandText = updateSQL; myCommand.ExecuteNonQuery(); } // Do the update that row is used } else if (dt.Rows.Count > 0 && SelectAllRows == true) { updateSQL = @"UPDATE [" + GetInputParamCalculatedValue("SheetName") + "$] SET "; foreach (string vc in varColMaps) { //TODO: fix me in OO Style //Do mapping string ColName = vc.Split('=')[0]; string Value = vc.Split('=')[1]; Value = Value.Replace("~^GINGER-EXCEL-COMMA-REPLACE^~", ","); string txt = Value; //keeping the translation of vars to support previous implementation VariableBase var = RunOnBusinessFlow.GetHierarchyVariableByName(Value); if (var != null) { var.Value = ValueExpression.Calculate(var.Value); if (var != null) { txt = var.Value; } else { txt = Value; } } //remove '' from value txt = txt.TrimStart(new char[] { '\'' }); txt = txt.TrimEnd(new char[] { '\'' }); //TODO: create one long SQL to do the update in one time and not for each var updateSQL = updateSQL + ColName + " = '" + txt + "',"; } updateSQL = updateSQL.Substring(0, updateSQL.Length - 1); updateSQL = updateSQL + sSetDataUsed; if (!string.IsNullOrEmpty(where)) { updateSQL += " WHERE " + where + ";"; } this.ExInfo += updateSQL + Environment.NewLine; OleDbCommand myCommand = new OleDbCommand(); myCommand.Connection = Conn; myCommand.CommandText = updateSQL; myCommand.ExecuteNonQuery(); } else if (dt.Rows.Count == 0) { this.ExInfo = "No Rows updated with given criteria"; } } catch (Exception ex) { // Reporter.ToLog(eAppReporterLogLevel.ERROR, "Writing into excel got error " + ex.Message); this.Error = "Error when trying to update the excel: " + ex.Message + Environment.NewLine + "UpdateSQL=" + updateSQL; } finally { Conn.Close(); } // then show a message if needed if (dt.Rows.Count == 0) { //TODO: reporter // Reporter.ToUser("No rows found in excel file matching criteria - " + sql); // throw new Exception("No rows found in excel file matching criteria - " + sql); } } }
public static void Inserir_Pedido(int id, string nome, string telefone, string endereço, string email) { string strConnection = "Data Source=.\\SQLEXPRESS;Initial Catalog=Loja;User ID=sa;Password=#lecoteco1975 ;Provider=SQLOLEDB"; //define a instrução SQL para somar Quantidade e agrupar resultados String strSQL = "Inserir_Pedido"; //cria a conexão com o banco de dados OleDbConnection dbConnection = new OleDbConnection(strConnection); //cria a conexão com o banco de dados OleDbConnection con = new OleDbConnection(strConnection); //cria o objeto command para executar a instruçao sql OleDbCommand cmd = new OleDbCommand(strSQL, con); nome = nome.Replace("-", ""); nome = nome.Replace("%", ""); nome = nome.Replace("(", ""); nome = nome.Replace(")", ""); nome = nome.Replace("/", ""); nome = nome.Replace("\\", ""); nome = nome.Replace("&", ""); nome = nome.Replace("=", ""); nome = nome.Replace("@", ""); nome = nome.Replace("{", ""); nome = nome.Replace("}", ""); nome = nome.Replace("+", ""); nome = nome.Replace("|", ""); endereço = endereço.Replace("-", ""); endereço = endereço.Replace("%", ""); endereço = endereço.Replace("(", ""); endereço = endereço.Replace(")", ""); endereço = endereço.Replace("/", ""); endereço = endereço.Replace("\\", ""); endereço = endereço.Replace("&", ""); endereço = endereço.Replace("=", ""); endereço = endereço.Replace("@", ""); endereço = endereço.Replace("{", ""); endereço = endereço.Replace("}", ""); endereço = endereço.Replace("+", ""); endereço = endereço.Replace("|", ""); email = email.Replace("-", ""); email = email.Replace("%", ""); email = email.Replace("(", ""); email = email.Replace(")", ""); email = email.Replace("/", ""); email = email.Replace("\\", ""); email = email.Replace("&", ""); email = email.Replace("=", ""); email = email.Replace("@", ""); email = email.Replace("{", ""); email = email.Replace("}", ""); email = email.Replace("+", ""); email = email.Replace("|", ""); //abre a conexao con.Open(); cmd.CommandType = CommandType.StoredProcedure; cmd.Parameters.AddWithValue("@id", id); cmd.Parameters.AddWithValue("@Nome", nome); cmd.Parameters.AddWithValue("@Telefone", telefone); cmd.Parameters.AddWithValue("@Endereço", endereço); cmd.Parameters.AddWithValue("@Email", email); //cria um dataadapter OleDbDataAdapter da = new OleDbDataAdapter(cmd); //cria um objeto datatable DataTable clientes = new DataTable(); //preenche o datatable via dataadapter da.Fill(clientes); con.Dispose(); con.Close(); cmd.Dispose(); dbConnection.Dispose(); dbConnection.Close(); }
override internal void LoadAll() { try { string schema = ""; if (-1 == this.Procedure.Schema.IndexOf(".")) { schema = this.Procedure.Schema + "."; } string select = "SET FMTONLY ON EXEC [" + this.Procedure.Database.Name + "]." + schema + this.Procedure.Name + " "; int paramCount = this.Procedure.Parameters.Count; if (paramCount > 0) { IParameters parameters = this.Procedure.Parameters; IParameter param = null; int c = parameters.Count; for (int i = 0; i < c; i++) { param = parameters[i]; if (param.Direction == ParamDirection.ReturnValue) { paramCount--; } } } for (int i = 0; i < paramCount; i++) { if (i > 0) { select += ","; } select += "null"; } OleDbDataAdapter adapter = new OleDbDataAdapter(select, this.dbRoot.ConnectionString); DataTable metaData = new DataTable(); adapter.Fill(metaData); AdvantageResultColumn resultColumn = null; int count = metaData.Columns.Count; for (int i = 0; i < count; i++) { resultColumn = this.dbRoot.ClassFactory.CreateResultColumn() as Advantage.AdvantageResultColumn; resultColumn.dbRoot = this.dbRoot; resultColumn.ResultColumns = this; resultColumn._column = metaData.Columns[i]; this._array.Add(resultColumn); } } catch {} }
// https://vscode.ru/prog-lessons/ms-access-i-c-sharp-rabotaem-s-bd.html // Запись в БД private void button4_Click(object sender, EventArgs e) { // Запись в таблицу Оборудование string t1 = textBox3.Text; // ИнвН string t2 = (comboBox1.SelectedIndex + 1).ToString(); // ид модель string t3 = textBox4.Text; // серийный string t4 = textBox2.Text; // дата string t5 = textBox7.Text; // цена string t6 = textBox6.Text; // дата списания string t7 = (comboBox3.SelectedIndex + 1).ToString(); ; // ид поставщик string t8 = (comboBox2.SelectedIndex + 1).ToString(); ; // ид тип string query = "INSERT INTO [Оборудование] ([ИнвN], [Id_модель], [СерийныйN], [Дата], [Цена], [Дата списания], [id_поставщик], [id_тип]) " + "VALUES ("+ "'" + t1+"',"+ t2+", '"+ t3 + "','" + t4 + "', " + t5 + ",'" + t6 + "'," + t7 + "," + t8 + ")"; // создаем объект OleDbCommand для выполнения запроса к БД MS Access OleDbCommand command = new OleDbCommand(query, connection); // выполняем запрос к MS Access command.ExecuteNonQuery(); MessageBox.Show("Паспорт успешно сохранен в БД"); // обновить таблицу на форме //dataGridView1.Cl string sql = "SELECT * FROM [Оборудование]"; adapter = new OleDbDataAdapter(sql, connection); ds = new DataSet(); adapter.Fill(ds); dataGridView1.SelectionMode = DataGridViewSelectionMode.FullRowSelect; dataGridView1.AllowUserToAddRows = false; dataGridView1.DataSource = ds.Tables[0]; label9.Text = DateTime.Now.ToString(); /* // Получить код query = "SELECT [Код_заказ] FROM [Заказы]WHERE [Дата_создания]= "+ "'" + t4 + "'"; //query = "SELECT * FROM [Заказы]";// WHERE [Дата_создания]= "+ //command = new OleDbCommand(query, connection); command.CommandText = query; object o = command.ExecuteScalar(); string kod_zak = o.ToString(); // Запись таблицы //object o; string v1, v2, v3, v4; for (int i = 0; i < dataGridView1.Rows.Count-1; ++i) { //o = dataGridView2[j, i].Value; v1 = kod_zak; v2 = dataGridView1[1, i].Value.ToString(); v3 = dataGridView1[4, i].Value.ToString(); v4 = dataGridView1[6, i].Value.ToString(); query = "INSERT INTO [Комплекты] ([Код_заказа], [Код_материала], [Количество], [Стоимость]) " + "VALUES (" + v1 + "," + v2 + "," + v3 + "," + v4 + ")"; command = new OleDbCommand(query, connection); command.ExecuteNonQuery(); MessageBox.Show("Заказ успешно сохранен в БД"); }; */ }
private void loadPreview() { using (WaitDialogForm form = new WaitDialogForm()) { //determine what to load either excle or csv if (this.radioGroup1.SelectedIndex == 0) //excle file { if (this.radioGroup2.SelectedIndex == 0) //open excel file 2003 { //Connection for MS Excel 2003 .xls format MyConnection = new OleDbConnection(String.Format("provider=Microsoft.Jet.OLEDB.4.0; Data Source='{0}';Extended Properties=Excel 8.0;", txtFiletoLoad.Text)); } else { //Connection for .xslx 2007 format MyConnection = new OleDbConnection(String.Format("Provider=Microsoft.ACE.OLEDB.12.0;Data Source='{0}';Extended Properties=Excel 12.0;", txtFiletoLoad.Text)); } //Select your Excel file //ExcelExport (1) //[Sheet1$] MyCommand = new System.Data.OleDb.OleDbDataAdapter("select * from [Sheet1$]", MyConnection); DtSet = new System.Data.DataSet(); //Bind all excel data in to data set MyCommand.Fill(DtSet, "[Sheet1$]"); Dt = DtSet.Tables[0]; MyConnection.Close(); //Check datatable have records //check if record id for paydirect or checque update if (CheuqeOption.ToString() == "0001" && this.radioGroup1.SelectedIndex == 0) { //Dt = LoadCSV().Tables[0]; Dt.Columns.Add("Cheque_Status", typeof(System.String)); //Dt.Columns.Add("DateDiff", typeof(System.String)); //Dt.Columns.Add("DateValidateAgainst", typeof(System.DateTime)); Dt.Columns.Add("Cheque_ValueDate", typeof(System.String)); foreach (DataRow row in Dt.Rows) { #region //split = row["payment_ref_num"].ToString().Split(new char[] { '|' }); //string[] splitDate = split[2].Split(new char[] { '-' }); //string[] splitTime = row["payment_log_date"].ToString().Split(new char[] { ' ' }); //DateTime dtPaymentDate = new DateTime(Convert.ToInt32(splitDate[2]), Convert.ToInt32(splitDate[1]), Convert.ToInt32(splitDate[0])); //row["payment_log_date"] = dtPaymentDate.ToString("MM/dd/yyyy"); //if (splitDate.Count() > 2 && splitTime.Count() > 0) //{ // //CultureInfo culture = new CultureInfo("en-US"); // dtPaymentDate = Convert.ToDateTime(String.Format("{0} {1} {2}", dtPaymentDate.ToString("dd-MM-yyyy"), splitTime[1], splitTime[2])); // int dateDiff = dtPaymentDate.Subtract(dateTimePicker1.Value).Days; // row["payment_log_date"] = dtPaymentDate.ToString("MM/dd/yyyy"); // row["DateDiff"] = Math.Abs(dateDiff); // row["DateValidateAgainst"] = dateTimePicker1.Value; //} #endregion if (row["payment_method_name"].ToString() == "Other Bank Cheque" || row["payment_method_name"].ToString() == "Other Bank Cheque/Dr") { //Own Bank Cheque row["Cheque_Status"] = "Pending"; } else { row["Cheque_Status"] = "Cleared"; row["Cheque_ValueDate"] = row["payment_log_date"]; } } Dt.AcceptChanges(); //label4.Text = Dt.Rows.Count + " Rows to be Imported "; ReformatAmount2(Dt); Dt.DefaultView.Sort = "revenue_code ASC"; gridControl1.DataSource = Dt; gridView1.BestFitColumns(); gridView1.GroupFooterShowMode = DevExpress.XtraGrid.Views.Grid.GroupFooterShowMode.VisibleAlways; //GridGroupSummaryItem item = new GridGroupSummaryItem(); //item.FieldName = "payment_ref_num"; //item.SummaryType = DevExpress.Data.SummaryItemType.Count; //gridView1.GroupSummary.Add(item); GridGroupSummaryItem item2 = new GridGroupSummaryItem() { FieldName = "Cheque_Status", SummaryType = DevExpress.Data.SummaryItemType.Count }; gridView1.GroupSummary.Add(item2); } else { if (Dt.Rows.Count > 0) { RemoveColumn(Dt, "F1"); RemoveColumn(Dt, "F14"); RemoveColumn(Dt, "F15"); ReformatAmount(Dt); gridControl1.DataSource = Dt.DefaultView; gridView1.OptionsBehavior.Editable = false; gridView1.BestFitColumns(); gridView1.Columns["Amount"].DisplayFormat.FormatType = FormatType.Numeric; gridView1.Columns["Amount"].DisplayFormat.FormatString = "n2"; } } //if (Dt.Rows.Count > 0) //{ // //RemoveColumn(Dt, "F1"); // //ReformatAmount(Dt); // gridControl1.DataSource = Dt.DefaultView; // gridView1.OptionsBehavior.Editable = false; // gridView1.BestFitColumns(); // //gridView1.Columns["Amount"].DisplayFormat.FormatType = FormatType.Numeric; // //gridView1.Columns["Amount"].DisplayFormat.FormatString = "n2"; //} //Dt.AcceptChanges(); label4.Text = Dt.Rows.Count + " Rows of Records to be Imported "; } else//csv file { try { // select format, encoding, an write the schema file Format(); Encoding(); writeSchema(); Dt = LoadCSV().Tables[0]; Dt.Columns.Add("Cheque_Status", typeof(System.String)); //Dt.Columns.Add("DateDiff", typeof(System.String)); //Dt.Columns.Add("DateValidateAgainst", typeof(System.DateTime)); Dt.Columns.Add("Cheque_ValueDate", typeof(System.String)); foreach (DataRow row in Dt.Rows) { //split = row["payment_ref_num"].ToString().Split(new char[] { '|' }); //string[] splitDate = split[2].ToString().Split(new char[] { '-' }); //string[] splitTime = row["payment_log_date"].ToString().Split(new char[] { ' ' }); //DateTime dtPaymentDate = new DateTime(Convert.ToInt32(splitDate[2]), Convert.ToInt32(splitDate[1]), Convert.ToInt32(splitDate[0])); //row["payment_log_date"] = dtPaymentDate.ToString("MM/dd/yyyy"); //if (splitDate.Count() > 2 && splitTime.Count() > 0) //{ // //CultureInfo culture = new CultureInfo("en-US"); // dtPaymentDate = Convert.ToDateTime(String.Format("{0} {1} {2}", dtPaymentDate.ToString("dd-MM-yyyy"), splitTime[1], splitTime[2])); // int dateDiff = dtPaymentDate.Subtract(dateTimePicker1.Value).Days; // row["payment_log_date"] = dtPaymentDate.ToString("MM/dd/yyyy"); // row["DateDiff"] = Math.Abs(dateDiff); // row["DateValidateAgainst"] = dateTimePicker1.Value; //} if (row["payment_method_name"].ToString() == "Other Bank Cheque" || row["payment_method_name"].ToString() == "Other Bank Cheque/Dr") { //Own Bank Cheque row["Cheque_Status"] = "Pending"; } else { row["Cheque_Status"] = "Cleared"; row["Cheque_ValueDate"] = row["payment_log_date"]; } } Dt.AcceptChanges(); label4.Text = Dt.Rows.Count + " Rows to be Imported "; Dt.DefaultView.Sort = "revenue_code ASC"; gridControl1.DataSource = Dt; gridView1.BestFitColumns(); gridView1.GroupFooterShowMode = DevExpress.XtraGrid.Views.Grid.GroupFooterShowMode.VisibleAlways; //GridGroupSummaryItem item = new GridGroupSummaryItem(); //item.FieldName = "payment_ref_num"; //item.SummaryType = DevExpress.Data.SummaryItemType.Count; //gridView1.GroupSummary.Add(item); GridGroupSummaryItem item2 = new GridGroupSummaryItem() { FieldName = "Cheque_Status", SummaryType = DevExpress.Data.SummaryItemType.Count }; gridView1.GroupSummary.Add(item2); } catch (Exception e) { MessageBox.Show(e.Message, "Error - loadPreview", MessageBoxButtons.OK, MessageBoxIcon.Error); } } if (Dt.Rows.Count > 0) { bttnPreview.Enabled = false; bttnImport.Enabled = true; bttnBrowse.Enabled = false; } else { bttnPreview.Enabled = true; bttnImport.Enabled = false; bttnBrowse.Enabled = false; } } }
/// <summary> /// Provide the required columns data from Excel /// </summary> /// <param name="fileName">Full path of the excel of format .xlsx</param> /// <param name="sheetName">Name of the sheet fo which you want to read the data in Excel</param> /// <param name="colKey">Column1, which you want to read from Excel, It will be the key for output data</param> /// <param name="colValue">Column2, which you want to read from Excel, It will be the value for output data</param> /// <returns>Dictionary with key as #<paramref name="colKey"/> and value as #<paramref name="colValue"/></returns> public static Dictionary <string, string> ReadExcel(string fileName, string sheetName, string colKey, string colValue) { string conString = string.Empty; DataTable dtexcel = new DataTable(); if (fileName.Substring(fileName.LastIndexOf(".")).CompareTo(".xlsx") != 0) { // conString = "provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + fileName + ";Extended Properties='Excel 8.0;HRD=Yes;IMEX=1';"; //for below excel 2007 Console.WriteLine("Expected format is .xlsx"); return(null); } else { conString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + fileName + ";Extended Properties='Excel 12.0;HRD=Yes';"; //for above excel 2007 } using (OleDbConnection Connection = new OleDbConnection(conString)) { try { Connection.Open(); dtexcel = Connection.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null); //If you apply below commented region below lines should go down.. var query = String.Format("select * from [{0}]", sheetName + "$"); OleDbDataAdapter oleAdpt = new OleDbDataAdapter(query, Connection); //here we read data from sheet1 oleAdpt.Fill(dtexcel); //fill excel data into dataTable } catch (Exception ex) { Console.WriteLine("Exception occurred ", ex); return(null); } finally { Connection.Close(); } } Dictionary <string, string> dataMap = new Dictionary <string, string>(); foreach (DataRow dtRow in dtexcel.Rows) { dataMap.Add(dtRow[colKey].ToString(), dtRow[colValue].ToString()); } return(dataMap); #region To read more Sheets in single excel.. //var itemsOfWorksheet = new List<SelectListItem>(); //if (dtexcel != null) //{ // //validate worksheet name. // string worksheetName; // for (int cnt = 0; cnt < dtexcel.Rows.Count; cnt++) // { // worksheetName = dtexcel.Rows[cnt]["TABLE_NAME"].ToString(); // if (worksheetName.Contains('\'')) // { // worksheetName = worksheetName.Replace('\'', ' ').Trim(); // } // if (worksheetName.Trim().EndsWith("$")) // itemsOfWorksheet.Add(new SelectListItem { Text = worksheetName.TrimEnd('$'), Value = worksheetName }); // } //} #endregion }
public static void UpdateExcel(string originalFilePath) { DataSet ds = new DataSet(); string connectionString = GetConnectionString(originalFilePath); using (OleDbConnection conn = new OleDbConnection(connectionString)) { conn.Open(); OleDbCommand cmd = new OleDbCommand(); cmd.Connection = conn; // Get all Sheets in Excel File DataTable dtSheet = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null); // Loop through all Sheets to get data foreach (DataRow dr in dtSheet.Rows) { string sheetName = dr["TABLE_NAME"].ToString(); if (!sheetName.EndsWith("$")) { continue; } // Get all rows from the Sheet cmd.CommandText = "SELECT * FROM [" + sheetName + "]"; DataTable dt = new DataTable(); dt.TableName = sheetName; OleDbDataAdapter da = new OleDbDataAdapter(cmd); da.Fill(dt); //here table has data now,so we do it //列项 foreach (DataRow row in dt.Rows) { var regCode = row["注册代码"].ToString(); var startTime = row["故障发生时间"].ToString(); var endTime = row["故障恢复时间"].ToString(); var etor_bianhao = DbHelper.GetCannyEtorBianhaoByRegCodeFromLocalDb(regCode); var ret = DbHelper.GetDtuStatusFromCannyDbByDtuBianhao(etor_bianhao, startTime); if (ret != null) { var isTrap = ret.Item1; var solution = ret.Item2; //var regCodeInt = int.Parse(regCode.Substring(14)); //string trap = (0 == regCodeInt % 2) ? "Y" : "N"; cmd.CommandText = $"Update [{sheetName}] set 是否困人 = '{isTrap}',处置信息='{solution}' where 注册代码='{regCode}'"; cmd.ExecuteNonQuery(); } } ds.Tables.Add(dt); } cmd = null; conn.Close(); } }
private void Form1_Load(object sender, EventArgs e) { using (OleDbConnection conn = new OleDbConnection(connStr)) { string sql = "Select b.姓名,s.成绩 from [学生成绩表] s left join [基本情况] b on b.学号 = s.学号"; DataTable dt = new DataTable(); OleDbDataAdapter da = new OleDbDataAdapter(sql, conn); da.Fill(dt); //dataGridView1.DataSource = dt; var lstRawRows = dt.Rows.OfType <DataRow>() .Select(r => new RawRow(r[0].ToString(), Convert.ToInt32(r[1]))); var lstStudents = lstRawRows.GroupBy(x => x.Name) .Select(g => new Student( g.Key, g.Average(x => x.score), g.Min(x => x.score))).ToList(); dataGridView1.DataSource = lstStudents; string[] xval = null; double[] yval = null; int[] yval1 = null; //加时间限制,统计当天,下同 //.Where(x=>Convert.ToDateTime(x["st"]).Date<=DateTime.Now.Date //&& Convert.ToDateTime(x["et"]).Date >= DateTime.Now.Date); xval = lstStudents .Select(x => x.Name) .Distinct().ToArray(); yval = lstStudents .Select(x => x.Ave) .ToArray(); yval1 = lstStudents .Select(x => x.Min) .ToArray(); Chart chart = new Chart(); panel1.Controls.Add(chart); chart.Dock = DockStyle.Fill; chart.ChartAreas.Add(new ChartArea()); chart.Series.Add(new Series("Ave")); chart.Series["Ave"].ChartType = SeriesChartType.Column; chart.Series["Ave"]["PieLabelStyle"] = "Outside"; chart.Series["Ave"]["PieLineColor"] = "Black"; chart.Series["Ave"].IsValueShownAsLabel = true; chart.Series["Ave"].Points.DataBindXY(xval, yval); chart.Series["Ave"].LegendText = "平均成绩"; chart.Series.Add(new Series("Min")); chart.Series["Min"].ChartType = SeriesChartType.Column; chart.Series["Min"]["PieLabelStyle"] = "Outside"; chart.Series["Min"]["PieLineColor"] = "Black"; chart.Series["Min"].IsValueShownAsLabel = true; chart.Series["Min"].Points.DataBindXY(xval, yval1); chart.Series["Min"].LegendText = "最低成绩"; Legend leg = new Legend(); leg.Docking = Docking.Right; chart.Legends.Add(leg); } // get records from the table }
private void Connect_Read_Raw(string strConnection) { // Create and open the connection in a using block. This ensures that all resources // will be closed and disposed when the code exits. Console.WriteLine("(raw)"); using (OleDbConnection connection = new OleDbConnection(strConnection)) { // Open the connection in a try/catch block try { if (m_eDbReadTechnology == DatabaseReadTechnology.eRbRead_DataReader) { // Using System.Data.OleDb.OleDbDataReader : IDataReader Console.WriteLine("(OleDb.OleDbDataReader)"); // Create and open the connection in a using block. This ensures that all resources // will be closed and disposed when the code exits. // Note: // * "DbConnection" is the base class for OleDbConnection, OdbcConnection // * "DbCommand" is the base class for OleDbCommand, OdbcCommand // * "DbParameterCollection" is the base class for OleDbParameterCollection, OdbcParameterCollection // * "DbDataReader" is the base class for OleDbDataReader, OdbcDataReader // We could write a generic function to access the database with OleDB or ODBC, but it // is simpler to write an unique method for each technology. // Create and execute the DataReader, writing the result to the console window int recordsRead = 0; Console.WriteLine("\t{0}{1}{2}", Northwind_Products.colProductID.PadRight(Northwind_Products.colProductIDWidth), Northwind_Products.colUnitPrice.PadRight(Northwind_Products.colUnitPriceWidth), Northwind_Products.colProductName); connection.Open(); OleDbCommand command = new OleDbCommand(m_cfgDatabase.querySELECT, connection); command.Parameters.AddWithValue("@pricePoint", m_cfgDatabase.paramValue); // This also works: command.Parameters.AddWithValue(string.Empty, paramValue); OleDbDataReader reader = command.ExecuteReader(); while (reader.Read()) { recordsRead++; Console.WriteLine("\t{0}{1}{2}", ((int)reader[Northwind_Products.colProductID]).ToString().PadRight(Northwind_Products.colProductIDWidth), ((decimal)reader[Northwind_Products.colUnitPrice]).ToString("0.00").PadRight(Northwind_Products.colUnitPriceWidth), (string)reader[Northwind_Products.colProductName]); } reader.Close(); Console.WriteLine(" ({0} records)", recordsRead); } else if (m_eDbReadTechnology == DatabaseReadTechnology.eRbRead_DataAdapter) { // This version uses: // * System.Data.DataSet // * System.Data.OleDb.OleDbDataAdapter : IDbDataAdapter // * System.Data.DataRow Console.WriteLine("(DataSet, OleDb.OleDbDataAdapter and DataRow)"); // Create and fill the DataSet, writing the result to the console window int recordsRead = 0; Console.WriteLine("\t{0}{1}{2}", Northwind_Products.colProductID.PadRight(Northwind_Products.colProductIDWidth), Northwind_Products.colUnitPrice.PadRight(Northwind_Products.colUnitPriceWidth), Northwind_Products.colProductName); connection.Open(); DataSet ds = new DataSet(); OleDbDataAdapter adapter = new OleDbDataAdapter(m_cfgDatabase.querySELECT, connection); adapter.SelectCommand.Parameters.Add("@pricePoint", OleDbType.Integer).Value = m_cfgDatabase.paramValue; adapter.Fill(ds); foreach (DataRow row in ds.Tables[0].Rows) { recordsRead++; Console.WriteLine("\t{0}{1}{2}", ((int)row[Northwind_Products.colProductID]).ToString().PadRight(Northwind_Products.colProductIDWidth), ((decimal)row[Northwind_Products.colUnitPrice]).ToString("0.00").PadRight(Northwind_Products.colUnitPriceWidth), (string)row[Northwind_Products.colProductName]); } Console.WriteLine(" ({0} records)", recordsRead); } } catch (Exception ex) { Console.WriteLine(UtilitiesGeneral.FormatException( this.ToString(), System.Reflection.MethodBase.GetCurrentMethod().Name, ex.Message)); } } Console.WriteLine(); }
public static DataTable ImportExcel(String filePath) { string Excel03ConString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};Extended Properties='Excel 8.0;HDR={1}'"; string Excel07ConString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties='Excel 8.0;HDR={1}'"; if (filePath != null && !filePath.Equals("")) { string extension = System.IO.Path.GetExtension(filePath); string header = "YES"; string connectionString, sheetName; connectionString = string.Empty; switch (extension) { case ".xls": //Excel 97-03 connectionString = string.Format(Excel03ConString, filePath, header); break; case ".xlsx": //Excel 07 connectionString = string.Format(Excel07ConString, filePath, header); break; default: { MessageBoxEX.DialogMessageBox("Thông báo", "File chọn không hợp lệ. Vui lòng chọn file Excel", "Bạn hãy kiểm tra lại file chọn cho phù hợp"); return(null); } } //Get the name of the First Sheet. using (OleDbConnection con = new OleDbConnection(connectionString)) { using (OleDbCommand cmd = new OleDbCommand()) { cmd.Connection = con; con.Open(); DataTable dtExcelSchema = con.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null); sheetName = dtExcelSchema.Rows[0]["TABLE_NAME"].ToString(); con.Close(); } } using (OleDbConnection con = new OleDbConnection(connectionString)) { using (OleDbCommand cmd = new OleDbCommand()) { using (OleDbDataAdapter oda = new OleDbDataAdapter()) { DataTable dt = new DataTable(); cmd.CommandText = "SELECT * From [" + sheetName + "]"; cmd.Connection = con; con.Open(); oda.SelectCommand = cmd; oda.Fill(dt); con.Close(); return(dt); } } } } else { MessageBoxEX.DialogMessageBox("Thông báo", " Vui lòng chọn file Excel", "Bạn hãy kiểm tra lại file chọn cho phù hợp"); return(null); } }
private void LufkinBooks_Load(object sender, EventArgs e) { #region Connect to DB try { connection = new OleDbConnection(); connection.ConnectionString = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=..\\..\\LufkinLib.V2.accdb; Persist Security Info = False;"; } catch (Exception ex) { MessageBox.Show(String.Format("Error: Failed to create a database connection. \n{0}", ex.Message)); return; } #endregion #region Load Books for Staff if (LufkinBooks.ViewBooks == "Staff") { try { //Set the command text to query DB with command.CommandText = "SELECT * FROM Media"; dataSet = new DataSet(); connection.Open(); command.Connection = connection; adapter = new OleDbDataAdapter(command); adapter.Fill(dataSet, "Media"); rowCollection = dataSet.Tables["Media"].Rows; table = dataSet.Tables["Media"]; int count = table.Rows.Count; if (count >= 1) { dgvBooks.DataSource = dataSet.Tables["Media"]; } else { MessageBox.Show("No books found!"); } connection.Close(); } catch (Exception ex) { MessageBox.Show(String.Format("Error: Failed to retrieve the required data from the DataBase.\n{0}", ex)); return; } finally { if (connection != null) { connection.Close(); } } } #endregion #region Load Books for Member if (LufkinBooks.ViewBooks == "Member") { try { //Set the command text to query DB with command.CommandText = "SELECT * FROM Media"; dataSet = new DataSet(); connection.Open(); command.Connection = connection; adapter = new OleDbDataAdapter(command); adapter.Fill(dataSet, "Media"); rowCollection = dataSet.Tables["Media"].Rows; table = dataSet.Tables["Media"]; int count = table.Rows.Count; if (count >= 1) { dgvBooks.DataSource = dataSet.Tables["Media"]; dgvBooks.Columns.Remove("ID"); dgvBooks.Columns.Remove("CheckedOutBy"); dgvBooks.Columns.Remove("CheckOutDate"); dgvBooks.Columns.Remove("CheckedInBy"); dgvBooks.Columns.Remove("CheckInDate"); dgvBooks.Columns.Remove("InPossesionOf"); dgvBooks.Columns.Remove("NextForBook"); } else { MessageBox.Show("No books found!"); } connection.Close(); } catch (Exception ex) { MessageBox.Show(String.Format("Error: Failed to retrieve the required data from the DataBase.\n{0}", ex)); return; } finally { if (connection != null) { connection.Close(); } } } #endregion #region Load Books for Anyone if (LufkinBooks.ViewBooks == "Anyone") { try { //Set the command text to query DB with command.CommandText = "SELECT * FROM Media"; dataSet = new DataSet(); connection.Open(); command.Connection = connection; adapter = new OleDbDataAdapter(command); adapter.Fill(dataSet, "Media"); rowCollection = dataSet.Tables["Media"].Rows; table = dataSet.Tables["Media"]; int count = table.Rows.Count; if (count >= 1) { dgvBooks.DataSource = dataSet.Tables["Media"]; dgvBooks.Columns.Remove("ID"); dgvBooks.Columns.Remove("ISBN"); dgvBooks.Columns.Remove("Section"); dgvBooks.Columns.Remove("Unavailable"); dgvBooks.Columns.Remove("CheckedOutBy"); dgvBooks.Columns.Remove("CheckOutDate"); dgvBooks.Columns.Remove("CheckedInBy"); dgvBooks.Columns.Remove("CheckInDate"); dgvBooks.Columns.Remove("InPossesionOf"); dgvBooks.Columns.Remove("NextForBook"); } else { MessageBox.Show("No books found!"); } connection.Close(); } catch (Exception ex) { MessageBox.Show(String.Format("Error: Failed to retrieve the required data from the DataBase.\n{0}", ex)); return; } finally { if (connection != null) { connection.Close(); } } } #endregion }
private void button2_Click(object sender, EventArgs e) { SaveFileDialog sfd = new SaveFileDialog(); sfd.Filter = "Excel | *.xls"; if (sfd.ShowDialog() == DialogResult.OK) { string fileName = sfd.FileName; Microsoft.Office.Interop.Excel.Workbook workBook = Main.ExcelApp.Workbooks.Add(); // 워크북 추가 Microsoft.Office.Interop.Excel.Worksheet workSheet; for (int i = 0; i < lsvPay.Items.Count; i++) { string name = lsvPay.Items[i].SubItems[0].Text; if (workBook.Worksheets.Count <= i) { workBook.Worksheets.Add(); } workSheet = workBook.Worksheets.get_Item(1) as Microsoft.Office.Interop.Excel.Worksheet; workSheet.Name = name; string query = "SELECT contract.ID, contract.inputdate, contract.recevicedocument, contract.shopname, contract.type, contract.registrationnumber, contract.phonenumber, contract.address, contract.representative, contract.salerpersion, contract.storeid, contract.offerdb, contract.dbmanager, contract.iscontract, contract.content FROM contract, paytable where contract.storeid = paytable.storeid and paytable.avablecalc = 'O' and contract.salerpersion = '" + name + "'"; DataSet ds = new DataSet(); OleDbDataAdapter adp = new OleDbDataAdapter(query, Main.conn); adp.Fill(ds); workSheet.Cells[1, 1] = lsvPay.Columns[0].Text; workSheet.Cells[1, 2] = lsvPay.Columns[1].Text; workSheet.Cells[1, 3] = lsvPay.Columns[2].Text; workSheet.Cells[1, 4] = lsvPay.Columns[3].Text; workSheet.Cells[2, 1] = lsvPay.Items[i].SubItems[0].Text; workSheet.Cells[2, 2] = lsvPay.Items[i].SubItems[1].Text; workSheet.Cells[2, 3] = lsvPay.Items[i].SubItems[2].Text; workSheet.Cells[2, 4] = lsvPay.Items[i].SubItems[3].Text; int colIndex = 1; foreach (ColumnHeader col in lsvPayList.Columns) { workSheet.Cells[3, colIndex] = col.Text; colIndex++; } int rowIndex = 4; foreach (DataRow row in ds.Tables[0].Rows) { colIndex = 1; foreach (object colItem in row.ItemArray) { string str = colItem.ToString(); workSheet.Cells[rowIndex, colIndex] = str; colIndex++; } rowIndex++; } } workBook.SaveAs(fileName, Microsoft.Office.Interop.Excel.XlFileFormat.xlWorkbookNormal); workBook.Close(true); MessageBox.Show("저장 완료"); } }
private void TreeView1_NodeMouseClick_1(object sender, TreeNodeMouseClickEventArgs e) { name = e.Node.Text.ToString(); if (name == "运行时分") { String ConStr = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + Application.StartupPath + "\\行车记录.mdb"; OleDbConnection conn = new OleDbConnection(ConStr); conn.Open(); DataTable dtt = new DataTable(); OleDbDataAdapter ada = new OleDbDataAdapter("select*from 京沪高铁列车时刻表190317 ", conn); ada.Fill(dtt); //以上连接数据库 dataGridView2.DataSource = dtt; //将数据库内容储存,记录行数(列车数) } if (name == "车站布置") { String ConStr = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + Application.StartupPath + "\\行车记录.mdb"; OleDbConnection conn = new OleDbConnection(ConStr); conn.Open(); DataTable dtt = new DataTable(); OleDbDataAdapter ada = new OleDbDataAdapter("select*from 车站 ", conn); ada.Fill(dtt); //以上连接数据库 dataGridView2.DataSource = dtt; //将数据库内容储存,记录行数(列车数) } if (name == "道岔设置") { String ConStr = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + Application.StartupPath + "\\行车记录.mdb"; OleDbConnection conn = new OleDbConnection(ConStr); conn.Open(); DataTable dtt = new DataTable(); OleDbDataAdapter ada = new OleDbDataAdapter("select*from 道岔标准2", conn); ada.Fill(dtt); //以上连接数据库 dataGridView2.DataSource = dtt; //将数据库内容储存,记录行数(列车数) } if (name == "动车组要素") { String ConStr = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + Application.StartupPath + "\\行车记录.mdb"; OleDbConnection conn = new OleDbConnection(ConStr); conn.Open(); DataTable dtt = new DataTable(); OleDbDataAdapter ada = new OleDbDataAdapter("select*from 动车组", conn); ada.Fill(dtt); //以上连接数据库 dataGridView2.DataSource = dtt; //将数据库内容储存,记录行数(列车数) } if (name == "运用模式") { System.Diagnostics.Process.Start(Application.StartupPath + "\\中国动车组交路查询.exe").WaitForExit(); } if (name == "区间") { String ConStr = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + Application.StartupPath + "\\行车记录.mdb"; OleDbConnection conn = new OleDbConnection(ConStr); conn.Open(); DataTable dtt = new DataTable(); OleDbDataAdapter ada = new OleDbDataAdapter("select*from 京沪高铁线路区间数据", conn); ada.Fill(dtt); //以上连接数据库 dataGridView2.DataSource = dtt; //将数据库内容储存,记录行数(列车数) } /* if (name == "联锁方式") * { * String ConStr = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + Application.StartupPath + "\\行车记录.mdb"; * OleDbConnection conn = new OleDbConnection(ConStr); * conn.Open(); * DataTable dtt = new DataTable(); * * OleDbDataAdapter ada = new OleDbDataAdapter("select*from 固定设备-轨道电路", conn); * ada.Fill(dtt); //以上连接数据库 * dataGridView2.DataSource = dtt; //将数据库内容储存,记录行数(列车数) * } * if (name == "信号制式") * if (name == "信号制式") * { * String ConStr = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + Application.StartupPath + "\\行车记录.mdb"; * OleDbConnection conn = new OleDbConnection(ConStr); * conn.Open(); * DataTable dtt = new DataTable(); * * OleDbDataAdapter ada = new OleDbDataAdapter("select*from 固定设备-信号机", conn); * ada.Fill(dtt); //以上连接数据库 * dataGridView2.DataSource = dtt; //将数据库内容储存,记录行数(列车数) * }*/ if (name == "设备参数") { String ConStr = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + Application.StartupPath + "\\行车记录.mdb"; OleDbConnection conn = new OleDbConnection(ConStr); conn.Open(); DataTable dtt = new DataTable(); OleDbDataAdapter ada = new OleDbDataAdapter("select*from 道岔标准", conn); ada.Fill(dtt); //以上连接数据库 dataGridView2.DataSource = dtt; //将数据库内容储存,记录行数(列车数) } if (name == "分界点") { String ConStr = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + Application.StartupPath + "\\行车记录.mdb"; OleDbConnection conn = new OleDbConnection(ConStr); conn.Open(); DataTable dtt = new DataTable(); OleDbDataAdapter ada = new OleDbDataAdapter("select*from 线路2", conn); ada.Fill(dtt); //以上连接数据库 dataGridView2.DataSource = dtt; //将数据库内容储存,记录行数(列车数) } }
//Populates the form, given a round and group. Assumes isValidGroup(round, group) == true private void populateData(int round, int group, int nextround, int nextgroup) { //populate curr and next labelCurrRound.Text = (round + 1).ToString(); labelCurrGroup.Text = (group + 1).ToString(); textBoxNextRound.Text = (nextround + 1).ToString(); textBoxNextGroup.Text = (nextgroup + 1).ToString(); //populate Player names GroupOfFour g = theEvent.lstRounds[round].lstGroups[group]; labelAPlayer.Text = g.playerA.displayName.PadRight(21).Substring(0, 21); labelBPlayer.Text = g.playerB.displayName.PadRight(21).Substring(0, 21); labelCPlayer.Text = g.playerC.displayName.PadRight(21).Substring(0, 21); labelDPlayer.Text = g.playerD.displayName.PadRight(21).Substring(0, 21); //Get scores from db for current round/group and populate scores/subs if they exist. Else populate with zeroes. if (!doScoresExistFor(theEvent.nID, (round + 1), (group + 1))) { textBoxAPutts.Text = "0"; textBoxBPutts.Text = "0"; textBoxCPutts.Text = "0"; textBoxDPutts.Text = "0"; textBoxAScore.Text = "0"; textBoxBScore.Text = "0"; textBoxCScore.Text = "0"; textBoxDScore.Text = "0"; checkBoxASub.Checked = false; checkBoxBSub.Checked = false; checkBoxCSub.Checked = false; checkBoxDSub.Checked = false; } else { string strCmd = "SELECT * FROM Scores WHERE eventID = " + theEvent.nID; strCmd += " AND roundNumber = " + (round + 1) + " AND groupNumber = " + (group + 1); strCmd += " ORDER BY rowID ASC"; OleDbCommand dbCmd = new OleDbCommand(strCmd, Globals.g_dbConnection); OleDbDataAdapter dbAdapter = new OleDbDataAdapter(dbCmd); DataSet dataSet = new DataSet(); try { dbAdapter.Fill(dataSet, "Scores"); } catch (Exception ex) { MessageBox.Show("ERROR 1013: Could not write to database.\n" + ex.Message); return; } int nRow = 0; foreach (DataRow r in dataSet.Tables["Scores"].Rows) { if (nRow == 0) //0th element { textBoxAPutts.Text = r["puttScore"].ToString(); textBoxAScore.Text = r["pointScore"].ToString(); checkBoxASub.Checked = Convert.ToBoolean(r["isSubstitution"].ToString()); } else if (nRow == 1) { textBoxBScore.Text = r["pointScore"].ToString(); textBoxBPutts.Text = r["puttScore"].ToString(); checkBoxBSub.Checked = Convert.ToBoolean(r["isSubstitution"].ToString()); } else if (nRow == 2) { textBoxCPutts.Text = r["puttScore"].ToString(); textBoxCScore.Text = r["pointScore"].ToString(); checkBoxCSub.Checked = Convert.ToBoolean(r["isSubstitution"].ToString()); } else if (nRow == 3) { textBoxDPutts.Text = r["puttScore"].ToString(); textBoxDScore.Text = r["pointScore"].ToString(); checkBoxDSub.Checked = Convert.ToBoolean(r["isSubstitution"].ToString()); } nRow++; } } }
public ICollection <ErrorHandler> BulkInsert(string argExcelPath, string argQuery, string strTableName, string argFileExt, string argUserName, string argClientCode) { DataTable dtExcel = null; ValuationClass ObjValuationClass = null; string xConnStr = ""; string strSheetName = ""; DataSet dsExcel = new DataSet(); DataTable dtTableSchema = new DataTable(); OleDbConnection objXConn = null; OleDbDataAdapter objDataAdapter = new OleDbDataAdapter(); List <ErrorHandler> lstErr = new List <ErrorHandler>(); if (argFileExt.ToString() == ".xls") { xConnStr = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + argExcelPath.Trim() + ";" + "Extended Properties=Excel 8.0"; } else { xConnStr = "Provider=Microsoft.ACE.OLEDB.12.0;" + "Data Source=" + argExcelPath.Trim() + ";" + "Extended Properties=Excel 12.0"; } try { objXConn = new OleDbConnection(xConnStr); objXConn.Open(); dtTableSchema = objXConn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null); if (argFileExt.ToString() == ".xls") { strSheetName = Convert.ToString(dtTableSchema.Rows[0]["TABLE_NAME"]); } else { strSheetName = Convert.ToString(dtTableSchema.Rows[0]["TABLE_NAME"]); if (strSheetName.IndexOf(@"_xlnm#_FilterDatabase") >= 0) { strSheetName = Convert.ToString(dtTableSchema.Rows[1]["TABLE_NAME"]); } } argQuery = argQuery + " [" + strSheetName + "]"; OleDbCommand objCommand = new OleDbCommand(argQuery, objXConn); objDataAdapter.SelectCommand = objCommand; objDataAdapter.Fill(dsExcel); dtExcel = dsExcel.Tables[0]; /*****************************************/ DataAccess da = new DataAccess(); da.Open_Connection(); da.BEGIN_TRANSACTION(); try { foreach (DataRow drExcel in dtExcel.Rows) { ObjValuationClass = new ValuationClass(); ObjValuationClass.ValClassType = Convert.ToString(drExcel["ValClassType"]).Trim(); ObjValuationClass.ValClassDesc = Convert.ToString(drExcel["ValClassDesc"]).Trim(); ObjValuationClass.ItemCategoryCode = Convert.ToString(drExcel["ItemCategoryCode"]).Trim(); ObjValuationClass.MaterialTypeCode = Convert.ToString(drExcel["MaterialTypeCode"]).Trim(); ObjValuationClass.CreatedBy = Convert.ToString(argUserName); ObjValuationClass.ModifiedBy = Convert.ToString(argUserName); ObjValuationClass.ClientCode = Convert.ToString(argClientCode); SaveValuationClass(ObjValuationClass, da, lstErr); foreach (ErrorHandler objerr in lstErr) { if (objerr.Type == "E") { da.ROLLBACK_TRANSACTION(); break; } } } da.COMMIT_TRANSACTION(); } catch (Exception ex) { if (da != null) { da.ROLLBACK_TRANSACTION(); } objErrorHandler.Type = ErrorConstant.strAboartType; objErrorHandler.MsgId = 0; objErrorHandler.Module = ErrorConstant.strInsertModule; objErrorHandler.ModulePart = ErrorConstant.strMasterModule; objErrorHandler.Message = ex.Message.ToString(); objErrorHandler.RowNo = 0; objErrorHandler.FieldName = ""; objErrorHandler.LogCode = ""; lstErr.Add(objErrorHandler); } finally { if (da != null) { da.Close_Connection(); da = null; } } } catch (Exception ex) { throw ex; } finally { objXConn.Close(); } return(lstErr); }
public Dictionary <Category, List <Player> > ReadPlayerList(string path, ExcelType extension) { Connect(path, extension); OleDbDataAdapter objDA = new OleDbDataAdapter("select * from [Sheet1$]", _connection); DataSet excelDataSet = new DataSet(); objDA.Fill(excelDataSet); Disconnect(); var result = excelDataSet.Tables[0]; var goldPlayers = new Dictionary <string, Player>(); var silverPlayers = new Dictionary <string, Player>(); var bronzeAPlayers = new Dictionary <string, Player>(); var bronzeBPlayers = new Dictionary <string, Player>(); foreach (DataRow row in result.Rows) { for (int i = 0; i < result.Columns.Count; i++) { var column = result.Columns[i]; var columnName = column.ToString(); var player = new Player(); var cellValue = row[result.Columns[i]].ToString(); if (cellValue != "") { switch (columnName) { case "Gold": player.Category = Category.Gold; player.FullName = cellValue; player.Points = Convert.ToInt32(row[result.Columns[i + 1]].ToString()); goldPlayers.Add(player.FullName, player); break; case "Silver": player.Category = Category.Silver; player.FullName = cellValue; player.Points = Convert.ToInt32(row[result.Columns[i + 1]].ToString()); silverPlayers.Add(player.FullName, player); break; case "Bronze A": player.Category = Category.Bronze_A; player.FullName = cellValue; player.Points = Convert.ToInt32(row[result.Columns[i + 1]].ToString()); bronzeAPlayers.Add(player.FullName, player); break; case "Bronze B": player.Category = Category.Bronze_B; player.FullName = cellValue; player.Points = Convert.ToInt32(row[result.Columns[i + 1]].ToString()); bronzeBPlayers.Add(player.FullName, player); break; } } } } var categories = new Dictionary <Category, List <Player> > { { Category.Gold, goldPlayers.Values.ToList() }, { Category.Silver, silverPlayers.Values.ToList() }, { Category.Bronze_A, bronzeAPlayers.Values.ToList() }, { Category.Bronze_B, bronzeBPlayers.Values.ToList() }, }; return(categories); }
private void button1_Click(object sender, EventArgs e) { try { string sql = null; string data = null; int i = 0; int j = 0; Exce.Application xlApp; Exce.Workbook xlWorkBook; Exce.Worksheet xlWorkSheet; object misValue = System.Reflection.Missing.Value; xlApp = new Exce.Application(); xlWorkBook = xlApp.Workbooks.Add(misValue); xlWorkSheet = (Exce.Worksheet)xlWorkBook.Worksheets.get_Item(1); connection.Open(); sql = "SELECT city_name, zip_code, state, country, area FROM city"; OleDbDataAdapter dscmd = new OleDbDataAdapter(sql, connection); DataSet ds = new DataSet(); dscmd.Fill(ds); xlWorkSheet.Cells[1, 1] = "City Name"; xlWorkSheet.Cells[1, 2] = "Zip Code"; xlWorkSheet.Cells[1, 3] = "State"; xlWorkSheet.Cells[1, 4] = "Country"; xlWorkSheet.Cells[1, 5] = "Area"; for (i = 0; i <= ds.Tables[0].Rows.Count - 1; i++) { for (j = 0; j <= ds.Tables[0].Columns.Count - 1; j++) { data = ds.Tables[0].Rows[i].ItemArray[j].ToString(); xlWorkSheet.Cells[i + 2, j + 1] = data; } } xlWorkBook.SaveAs("City Report.xls", Exce.XlFileFormat.xlWorkbookNormal, misValue, misValue, misValue, misValue, Exce.XlSaveAsAccessMode.xlExclusive, misValue, misValue, misValue, misValue, misValue); xlWorkBook.Close(true, misValue, misValue); xlApp.Quit(); releaseObject(xlWorkSheet); releaseObject(xlWorkBook); releaseObject(xlApp); MessageBox.Show("Excel file created , you can find the file C:\\Users\\User\\Documents. City Report.xls"); } catch (Exception) { } finally { connection.Close(); } }
public void ReadData() { //TODO: check what is required on the machine and maybe support for other versions string ConnString = GetConnectionString(); string sql = ""; using (OleDbConnection Conn = new OleDbConnection(ConnString)) { try { Conn.Open(); } catch (Exception ex) { System.Threading.Thread.Sleep(3000); Conn.Open(); Reporter.ToLog(eLogLevel.ERROR, $"Method - {MethodBase.GetCurrentMethod().Name}, Error - {ex.StackTrace}", ex); } OleDbCommand Cmd = new OleDbCommand(); Cmd.Connection = Conn; string SheetName = GetInputParamCalculatedValue("SheetName").Trim(); if (!SheetName.EndsWith("$")) { SheetName += "$"; } if (SelectAllRows == false) { sql = "Select TOP 1 * from [" + SheetName + "]"; } else { sql = "Select * from [" + SheetName + "]"; } string where = GetInputParamCalculatedValue("SelectRowsWhere"); if (!string.IsNullOrEmpty(where)) { sql += " WHERE " + where; } Cmd.CommandText = sql; DataTable dt = new DataTable(); OleDbDataAdapter da = new OleDbDataAdapter(); da.SelectCommand = Cmd; try { da.Fill(dt); // we expect only 1 record if (dt.Rows.Count == 1 && SelectAllRows == false) { DataRow r = dt.Rows[0]; for (int i = 0; i < dt.Columns.Count; i++) { AddOrUpdateReturnParamActual(dt.Columns[i].ColumnName, ((object)r[i]).ToString()); } if (!String.IsNullOrEmpty(GetInputParamCalculatedValue("SetDataUsed"))) { string rowKey = r[GetInputParamCalculatedValue("PrimaryKeyColumn")].ToString(); string updateSQL = @"UPDATE [" + GetInputParamCalculatedValue("SheetName") + "$] SET " + GetInputParamCalculatedValue("SetDataUsed") + " WHERE " + GetInputParamCalculatedValue("PrimaryKeyColumn") + "=" + rowKey + ";"; OleDbCommand myCommand = new OleDbCommand(); myCommand.Connection = Conn; myCommand.CommandText = updateSQL; myCommand.ExecuteNonQuery(); } } else if (dt.Rows.Count > 0 && SelectAllRows == true) { for (int j = 0; j < dt.Rows.Count; j++) { DataRow r = dt.Rows[j]; for (int i = 0; i < dt.Columns.Count; i++) { AddOrUpdateReturnParamActualWithPath(dt.Columns[i].ColumnName, ((object)r[i]).ToString(), "" + (j + 1).ToString()); } } if (!String.IsNullOrEmpty(GetInputParamCalculatedValue("SetDataUsed"))) { string updateSQL = @"UPDATE [" + GetInputParamCalculatedValue("SheetName") + "$] SET " + GetInputParamCalculatedValue("SetDataUsed"); if (!string.IsNullOrEmpty(where)) { updateSQL += " WHERE " + where + ";"; } OleDbCommand myCommand = new OleDbCommand(); myCommand.Connection = Conn; myCommand.CommandText = updateSQL; myCommand.ExecuteNonQuery(); } } else if (dt.Rows.Count != 1 && SelectAllRows == false) { Status = Amdocs.Ginger.CoreNET.Execution.eRunStatus.Failed; Error = "Excel Query should return only one row" + Environment.NewLine + sql + Environment.NewLine + "Returned: " + dt.Rows.Count + " Records"; } } catch (Exception ex) { this.Status = Amdocs.Ginger.CoreNET.Execution.eRunStatus.Failed; Error = ex.Message; } finally { Conn.Close(); } if (dt.Rows.Count == 0) { this.Status = Amdocs.Ginger.CoreNET.Execution.eRunStatus.Failed; Error = "No rows found in excel file matching criteria - " + sql; } } }