private static void Main() { var dataTable = new DataTable("newtable"); using (var connection = new OleDbConnection(@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=../../../task.xlsx;Extended Properties=Excel 12.0;")) { connection.Open(); const string SelectSql = @"SELECT * FROM [Sheet1$]"; using (var adapter = new OleDbDataAdapter(SelectSql, connection)) { adapter.FillSchema(dataTable, SchemaType.Source); adapter.Fill(dataTable); } connection.Close(); } foreach (DataRow row in dataTable.Rows) { Console.WriteLine(row.ItemArray[0] + " - " + row.ItemArray[1]); } }
// Загрузка в DataSet public static void BuildDataSet(ref OleDbConnection connect, ref DataSet ds, ref OleDbDataAdapter[] adapt) { OleDbCommand[] commands = new OleDbCommand[3]; commands[0] = new OleDbCommand("SELECT * from Tovar", connect); daTovar = new OleDbDataAdapter(commands[0]); daTovar.FillSchema(ds, SchemaType.Source, "Tovar"); daTovar.Fill(ds, "Tovar"); adapt[0] = daTovar; commands[1] = new OleDbCommand("SELECT * from Pokupatel", connect); daPokupatel = new OleDbDataAdapter(commands[1]); daPokupatel.FillSchema(ds, SchemaType.Source, "Pokupatel"); daPokupatel.Fill(ds, "Pokupatel"); adapt[1] = daPokupatel; commands[2] = new OleDbCommand("SELECT * from Zakaz", connect); daZakaz = new OleDbDataAdapter(commands[2]); daZakaz.FillSchema(ds, SchemaType.Source, "Zakaz"); daZakaz.Fill(ds, "Zakaz"); adapt[2] = daZakaz; }
public static String getLastOrderInProduction() { DataSet ds = new DataSet(); using (OleDbConnection conn = new OleDbConnection(Properties.Settings.Default.ordersdb.ToString())) { conn.Open(); OleDbCommand cmd = new OleDbCommand("SELECT * FROM orders WHERE status=4", conn); OleDbDataAdapter da = new OleDbDataAdapter(cmd); da.Fill(ds); da.FillSchema(ds, SchemaType.Source); conn.Close(); } if (ds.Tables[0].Rows.Count > 0) { return(ds.Tables[0].Rows[0]["ID"].ToString()); } else { return(""); } }
//public void getTables() //{ // OleDbCommand command; // OleDbDataReader reader; // DataGridView tgrid = null; // DataTable tmpdt; // for (int i = 0; i < treeTables.Nodes.Count; i++) // { // tmpdt = new DataTable(); // // Add new Tab page // TabPage tbtmp = new TabPage(treeTables.Nodes[i].Text); // tbtmp.Width = tabTables.Width; // tbtmp.Height = tabTables.Height; // //New Data Grid // tgrid = new DataGridView(); // tgrid.DataSource = tmpdt; // tgrid.Width = tabTables.Width; // tgrid.Height = tabTables.Height; // tgrid.ScrollBars = ScrollBars.Both; // tgrid.AutoSizeColumnsMode = DataGridViewAutoSizeColumnsMode.Fill; // tgrid.Anchor = ((AnchorStyles)((((AnchorStyles.Top | AnchorStyles.Bottom) // | AnchorStyles.Left) // | AnchorStyles.Right))); // //Get Recordsets of Tables // command = new OleDbCommand("SELECT * FROM " + treeTables.Nodes[i].Text, mycon); // reader = command.ExecuteReader(); // tmpdt.Load(reader); // // Insert Data Grid into Tab Page // tbtmp.Controls.Add(tgrid); // tabTables.Controls.Add(tbtmp); // } //} public void getTablesWithDataSet() { DataTable tmpdt; DataGridView tgrid = null; for (int i = 0; i < treeTables.Nodes.Count; i++) { // Create a Data adapter to get each table daDB = new OleDbDataAdapter("SELECT * FROM " + treeTables.Nodes[i].Text, mycon); // Fill Data Set Schema daDB.FillSchema(dSetDB, SchemaType.Source, treeTables.Nodes[i].Text); // Fill Data Set Tables daDB.Fill(dSetDB, treeTables.Nodes[i].Text); // Instance the Object Data Table to work with Data grid view tmpdt = dSetDB.Tables[treeTables.Nodes[i].Text]; // Add new Tab page TabPage tbtmp = new TabPage(treeTables.Nodes[i].Text); tbtmp.Width = tabTables.Width; tbtmp.Height = tabTables.Height; //New Data Grid tgrid = new DataGridView(); tgrid.DataSource = tmpdt; tgrid.Width = tabTables.Width; tgrid.Height = tabTables.Height; tgrid.ScrollBars = ScrollBars.Both; tgrid.AutoSizeColumnsMode = DataGridViewAutoSizeColumnsMode.Fill; tgrid.Anchor = ((AnchorStyles)((((AnchorStyles.Top | AnchorStyles.Bottom) | AnchorStyles.Left) | AnchorStyles.Right))); // Insert Data Grid into Tab Page tbtmp.Controls.Add(tgrid); tabTables.Controls.Add(tbtmp); } }
private void FillDataSet(DsDwTableMap_M aDsDwTableMap) { try { string strCommand = "Select * from " + aDsDwTableMap.DwSchemaTableName + " WHERE 1=0"; OleDbDataAdapter objEmpData = new OleDbDataAdapter(strCommand, _CBOleConnection); DataTable[] dataTables = objEmpData.FillSchema(_CbDataSet, SchemaType.Mapped, aDsDwTableMap.DwTableName); DataTable dataTable = dataTables[0]; dataTable.ExtendedProperties.Add("TableType", "Table"); dataTable.ExtendedProperties.Add("DbSchemaName", aDsDwTableMap.DsSchemaName); dataTable.ExtendedProperties.Add("DbTableName", aDsDwTableMap.DwTableName); dataTable.ExtendedProperties.Add("FriendlyName", aDsDwTableMap.DwTableName); dataTable = null; dataTables = null; objEmpData = null; } catch (Exception ex) { AppendLogLine("Error in Creating a DataSourceView - FillDataSet. Error Message -> " + ex.Message); throw; } }
private void FillDataTable(String selectCommand) { try { myConnection.Open(); myAdapter.SelectCommand.CommandText = selectCommand; // Get the schema for the Users table // Stops certain update errors occurring myAdapter.FillSchema(myDataTable, SchemaType.Source); // Fill the datatable with the rows reurned by the select command myAdapter.Fill(myDataTable); myConnection.Close(); } catch (OleDbException) { Button1.Text = "Please choose a different username. This one already exists."; if (myConnection.State == ConnectionState.Open) { myConnection.Close(); } } }
public static DataSet FillData() { using (OleDbConnection connection = new OleDbConnection()) { connection.ConnectionString = conectionString; OleDbDataAdapter da = new OleDbDataAdapter(); DataSet myData = new DataSet(); List <string> tableNames = new List <string>() { "AN148_" + TrudoyomkostSettings.DepNum.ToString(), "AN158_" + TrudoyomkostSettings.DepNum.ToString(), "AN178_" + TrudoyomkostSettings.DepNum.ToString() }; try { foreach (var item in tableNames) { var cmd = connection.CreateCommand(); cmd.CommandText = "SELECT *FROM " + item; da.SelectCommand = cmd; da.FillSchema(myData, SchemaType.Source, item); da.Fill(myData, item); } } catch (OleDbException ex) { return(myData); connection.Close(); } return(myData); connection.Close(); } }
private void buttonArtikellesen_Click(object sender, EventArgs e) { adapterArtikel.FillSchema(ds, SchemaType.Source, "Artikel"); ds.Tables["Artikel"].Columns[0].AutoIncrement = true; adapterArtikel.Fill(ds, "Artikel"); dataGridViewAusgabe.DataSource = ds; dataGridViewAusgabe.DataMember = "Artikel"; DataTableReader reader = ds.Tables["Artikel"].CreateDataReader(); while (reader.Read()) { DisplayArtikel ds = new DisplayArtikel(); ds.ArtikelOid = reader.GetInt32(0); ds.ArtNr = reader.GetString(1); ds.Bezeichnung = reader.GetString(3); ds.Meldebestand = reader.GetInt16(5); ds.ArtGruppe = GetArtGruppe(reader.GetInt32(2)); ds.Bestand = reader.GetByte(4); lsArt.Add(ds); } dataGridViewAusgabe.DataSource = lsArt; }
public DataTable GetSchemaStruct(string sqlstr, params IDbDataParameter[] args) { error = null; using (OleDbConnection myConn = new OleDbConnection(this.ConnStr)) { OleDbCommand myCmd = new OleDbCommand(sqlstr, myConn); myCmd.CommandTimeout = CommandTimeout; try { DataTable dt = new DataTable(); myConn.Open(); foreach (IDbDataParameter arg in args) { if (myCmd.CommandType != CommandType.StoredProcedure) { myCmd.CommandType = CommandType.StoredProcedure; } myCmd.Parameters.Add(arg); } OleDbDataAdapter myAdapter = new OleDbDataAdapter(myCmd); myAdapter.MissingMappingAction = MissingMappingAction.Passthrough; myAdapter.MissingSchemaAction = MissingSchemaAction.AddWithKey; myAdapter.FillSchema(dt, SchemaType.Mapped); myAdapter.Dispose(); return(dt); } catch (OleDbException ex) { error = ex; return(null); } finally { myConn.Close(); myCmd.Dispose(); myConn.Dispose(); } } }
public static void InserToExcel(string productName) { OleDbConnectionStringBuilder csbuilder = new OleDbConnectionStringBuilder(); csbuilder.Provider = "Microsoft.ACE.OLEDB.12.0"; csbuilder.DataSource = @"..\..\Products-Total-Report.xlsx"; csbuilder.Add("Extended Properties", "Excel 12.0 Xml;HDR=YES"); DataTable dt = new DataTable("datatable"); using (OleDbConnection connection = new OleDbConnection(csbuilder.ConnectionString)) { connection.Open(); string selectSql = @"INSERT INTO [Sheet1$] VALUES('" + productName + "')"; using (OleDbDataAdapter adapter = new OleDbDataAdapter(selectSql, connection)) { adapter.FillSchema(dt, SchemaType.Source); adapter.Fill(dt); } connection.Close(); } }
private DataSet fillSchema(string strSql) { OleDbConnection conn; try { conn = new OleDbConnection("Provider=Microsoft.ACE.OLEDB.10.0;Data Source=" + dbPathm); conn.Open(); } catch { try { conn = new OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + dbPathm); conn.Open(); } catch { try { conn = new OleDbConnection("Provider=Microsoft.ACE.OLEDB.14.0;Data Source=" + dbPathm); conn.Open(); } catch { conn = new OleDbConnection("Provider=Microsoft.ACE.OLEDB.15.0;Data Source=" + dbPathm); conn.Open(); } } } DataSet oDs1 = new DataSet(); OleDbDataAdapter oOrdersDataAdapter = new OleDbDataAdapter(new OleDbCommand(strSql, conn)); OleDbCommandBuilder oOrdersCmdBuilder = new OleDbCommandBuilder(oOrdersDataAdapter); oOrdersDataAdapter.FillSchema(oDs1, SchemaType.Source); return(oDs1); }
private void GetTables( OleDbConnection conn) { DataTable dt = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null); foreach (DataRow dr in dt.Rows) { string type = dr["TABLE_TYPE"].ToString(); if (string.Compare(type, "TABLE", true) == 0 || string.Compare(type, "VIEW", true) == 0) { // create new table string tableName = (string)dr["TABLE_NAME"]; DataTable table = new DataTable(tableName); // try getting the table schema (this can fail) try { // add brackets now // so we can work with tables called 'index' for example. // get table schema OleDbDataAdapter da = new OleDbDataAdapter(string.Format("select * from [{0}]", tableName), conn); da.FillSchema(table, SchemaType.Mapped); // save table type (table or view) table.ExtendedProperties["TABLE_TYPE"] = type; // add new table to dataset Tables.Add(table); } catch { // error happened when reading schema for this table // ignore and continue... } } } }
private bool IsRealAutoGrow(SqlHelper sqlHelper, string tableName, string columnName) { var con = new OleDbConnection(sqlHelper.DbConnectionString); con.Open(); var adapter = new OleDbDataAdapter("select * from [" + tableName + "] where false", con); DataTable dtSchema = adapter.FillSchema(new DataTable(), SchemaType.Source); con.Close(); con.Dispose(); if (dtSchema == null) { return(false); } for (int i = 0; i < dtSchema.Columns.Count; i++) { if (dtSchema.Columns[i].AutoIncrement && dtSchema.Columns[i].ColumnName.ToUpper() == columnName.ToUpper()) { return(true); } } return(false); }
private void GetTables(OleDbConnection conn) { // add tables var dt = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null); foreach (DataRow dr in dt.Rows) { // get type (table/view) var type = (string)dr[TABLE_TYPE]; if (type != TABLE && type != VIEW && type != LINK) { continue; } // create table var name = (string)dr[TABLE_NAME]; var table = new DataTable(name); table.ExtendedProperties[TABLE_TYPE] = type; // save definition in extended properties foreach (DataColumn col in dt.Columns) { table.ExtendedProperties[col.ColumnName] = dr[col]; } // get table schema and add to collection try { var select = GetSelectStatement(table); var da = new OleDbDataAdapter(select, conn); da.FillSchema(table, SchemaType.Mapped); Tables.Add(table); } catch { } } }
void TryPreview() { try { lbName.Items.Clear(); string s = "provider = Microsoft.Jet.OLEDB.4.0;" + "data source = " + tbFile.Text + ";" + "extended properties = Excel 8.0;"; OleDbConnection conn = new OleDbConnection(s); string sheet = tbSheet.Text.Replace('.', '#'); s = string.Format("SELECT * FROM [{0}${1}]", sheet, tbRange.Text); OleDbDataAdapter da = new OleDbDataAdapter(s, conn); conn.Open(); DataTable dtData = new DataTable(); da.FillSchema(dtData, SchemaType.Source); conn.Close(); foreach (DataColumn c in dtData.Columns) { lbName.Items.Add((string)c.ColumnName); } } catch { } }
public void AddComputedColumn(DataSourceView dsv, OleDbConnection connection, String tableName, String computedColumnName, String expression) { DataSet tmpDataSet = new DataSet(); tmpDataSet.Locale = CultureInfo.CurrentCulture; OleDbDataAdapter adapter = new OleDbDataAdapter("SELECT (" + expression + ") AS [" + computedColumnName + "] FROM [dbo].[" + tableName + "] WHERE 1=0", connection); DataTable[] dataTables = adapter.FillSchema(tmpDataSet, SchemaType.Mapped, tableName); DataTable dataTable = dataTables[0]; DataColumn dataColumn = dataTable.Columns[computedColumnName]; dataTable.Constraints.Clear(); dataTable.Columns.Remove(dataColumn); dataColumn.ExtendedProperties.Add("DbColumnName", computedColumnName); dataColumn.ExtendedProperties.Add("ComputedColumnExpression", expression); dataColumn.ExtendedProperties.Add("IsLogical", "True"); dsv.Schema.Tables[tableName].Columns.Add(dataColumn); }
internal DataSet AccessImport() { //DataSet Object that will contain contents of file //DataSet FileContents = new DataSet(); RawData = new DataSet(); DateTime LookBackDate = new DateTime(); OleDbConnection conn = new OleDbConnection(dataProp.SourceConnectionString); OleDbCommand comm = new OleDbCommand(); comm.Connection = conn; string commandText = "Select * from " + dataProp.SourceTableName; if (dataProp.UseLookBack) { commandText += " WHERE " + dataProp.LookBackColumnName + " >= @LookBackPeroid"; //Set the look back date to be one second prior to midnight on the date of the look back period. LookBackDate = DateTime.Now.AddDays(-dataProp.LookBackPeriod - 1).AddHours(-DateTime.Now.Hour + 23).AddMinutes(-DateTime.Now.Minute + 59).AddSeconds(-DateTime.Now.Second + 59); comm.Parameters.AddWithValue("@LookBackPeroid", LookBackDate); comm.Parameters["@LookBackPeroid"].OleDbType = OleDbType.DBDate; } comm.CommandText = commandText; OleDbDataAdapter Adapter = new OleDbDataAdapter(comm); Adapter.FillSchema(RawData, SchemaType.Source, dataProp.SourceTableName); Adapter.Fill(RawData, dataProp.SourceTableName); rowImportedFromSource = RawData.Tables[0].Rows.Count; return(RawData); }
private void cmdProcess_Click(object sender, EventArgs e) { DateTime curDate = new DateTime(); if (txtXmlPath.Text == "" || txtExcelPath.Text == "") { MessageBox.Show("Vous devez sélectionner un fichier Excel source et un fichier XML destination !", m_DialogTitle); return; } radStep5.Enabled = false; prgBarProcess.Value = 0; prgBarProcess.Enabled = false; Cursor = Cursors.WaitCursor; // open oledb connection on excel file OleDbConnection oleConn = new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + txtExcelPath.Text + ";Extended Properties=\"Excel 8.0;HDR=Yes;IMEX=1;\""); oleConn.Open(); // select all in sheet OleDbCommand oleCmdSelect = new OleDbCommand(@"SELECT * FROM [" + "Feuil1$" + "]", oleConn); radStep2.Enabled = true; radStep2.Checked = true; // get the data OleDbDataAdapter oleAdapter = new OleDbDataAdapter(); oleAdapter.SelectCommand = oleCmdSelect; DataTable dt = new DataTable("test"); oleAdapter.FillSchema(dt, SchemaType.Source); oleAdapter.Fill(dt); // open the XML Writer XmlTextWriter writer; string xmlFilename = txtXmlPath.Text; writer = new XmlTextWriter(xmlFilename, System.Text.Encoding.UTF8); // indent the XML document for readability writer.Formatting = System.Xml.Formatting.Indented; // call WriteStartDocument to write XML declaration writer.WriteStartDocument(); // write the root element writer.WriteStartElement("root"); prgBarProcess.Minimum = 0; prgBarProcess.Maximum = dt.Rows.Count; System.Threading.Thread.Sleep(1000); radStep2.Enabled = false; radStep3.Enabled = true; radStep3.Checked = true; prgBarProcess.Enabled = true; // parse every record for (int i = 0; i < dt.Rows.Count; ++i) { switch (dt.Rows[i][2].ToString()) { // deliverable 12 created, process 3 terminated, non final status case "Compléter éval. qualitative et réexamen": // <message> writer.WriteStartElement("message"); curDate = Convert.ToDateTime(dt.Rows[i][3]); writer.WriteAttributeString("caseid", dt.Rows[i][6].ToString()); writer.WriteAttributeString("caseidremark", ""); writer.WriteAttributeString("externmessageid", dt.Rows[i][9].ToString()); writer.WriteAttributeString("externprocid", "3"); writer.WriteAttributeString("externsystemid", dt.Rows[i][8].ToString()); writer.WriteAttributeString("externuserid", dt.Rows[i][4].ToString()); writer.WriteAttributeString("messagetime", curDate.ToString("yyyyMMdd hhmmss")); writer.WriteAttributeString("statut", "0"); writer.WriteAttributeString("timestampproc", curDate.ToString("yyyyMMdd hhmmss")); writer.WriteAttributeString("externprestationid", dt.Rows[i][5].ToString()); writer.WriteStartElement("messagedeliv"); // attributes for <messagedeliv> writer.WriteAttributeString("doublecontrol", "0"); writer.WriteAttributeString("externdelivid", "-"); writer.WriteAttributeString("externdelividint", "12"); writer.WriteAttributeString("externuserid", dt.Rows[i][4].ToString()); writer.WriteAttributeString("timestampdeliv", curDate.ToString("yyyyMMdd hhmmss")); // </messagedeliv> writer.WriteEndElement(); // write the last </message> element writer.WriteEndElement(); break; // deliverable 4 created, process 50 terminated, final status case "Traiter le reexamen": // <message> writer.WriteStartElement("message"); curDate = Convert.ToDateTime(dt.Rows[i][3]); writer.WriteAttributeString("caseid", dt.Rows[i][6].ToString()); writer.WriteAttributeString("caseidremark", ""); writer.WriteAttributeString("externmessageid", dt.Rows[i][9].ToString()); writer.WriteAttributeString("externprocid", "50"); writer.WriteAttributeString("externsystemid", dt.Rows[i][8].ToString()); writer.WriteAttributeString("externuserid", dt.Rows[i][4].ToString()); writer.WriteAttributeString("messagetime", curDate.ToString("yyyyMMdd hhmmss")); writer.WriteAttributeString("statut", "1"); writer.WriteAttributeString("timestampproc", curDate.ToString("yyyyMMdd hhmmss")); writer.WriteAttributeString("externprestationid", dt.Rows[i][5].ToString()); writer.WriteStartElement("messagedeliv"); //attributes for <messagedeliv> writer.WriteAttributeString("doublecontrol", "0"); writer.WriteAttributeString("externdelivid", "-"); writer.WriteAttributeString("externdelividint", "4"); writer.WriteAttributeString("externuserid", dt.Rows[i][4].ToString()); writer.WriteAttributeString("timestampdeliv", curDate.ToString("yyyyMMdd hhmmss")); // </messagedeliv> writer.WriteEndElement(); // write the last </message> element writer.WriteEndElement(); break; // deliverable 41 created, process 10 terminated, final status case "Terminer le réexamen": // <message> writer.WriteStartElement("message"); curDate = Convert.ToDateTime(dt.Rows[i][3]); writer.WriteAttributeString("caseid", dt.Rows[i][6].ToString()); writer.WriteAttributeString("caseidremark", ""); writer.WriteAttributeString("externmessageid", dt.Rows[i][9].ToString()); writer.WriteAttributeString("externprocid", "10"); writer.WriteAttributeString("externsystemid", dt.Rows[i][8].ToString()); writer.WriteAttributeString("externuserid", dt.Rows[i][4].ToString()); writer.WriteAttributeString("messagetime", curDate.ToString("yyyyMMdd hhmmss")); writer.WriteAttributeString("statut", "1"); writer.WriteAttributeString("timestampproc", curDate.ToString("yyyyMMdd hhmmss")); writer.WriteAttributeString("externprestationid", dt.Rows[i][5].ToString()); writer.WriteStartElement("messagedeliv"); // attributes for <messagedeliv> writer.WriteAttributeString("doublecontrol", "0"); writer.WriteAttributeString("externdelivid", "-"); writer.WriteAttributeString("externdelividint", "41"); writer.WriteAttributeString("externuserid", dt.Rows[i][4].ToString()); writer.WriteAttributeString("timestampdeliv", curDate.ToString("yyyyMMdd hhmmss")); // </messagedeliv> writer.WriteEndElement(); // write the last </message> element writer.WriteEndElement(); break; // deliverable 23 created, process 3 terminated, final status case "Traiter le réexamen standard": // <message> writer.WriteStartElement("message"); curDate = Convert.ToDateTime(dt.Rows[i][3]); writer.WriteAttributeString("caseid", dt.Rows[i][6].ToString()); writer.WriteAttributeString("caseidremark", ""); writer.WriteAttributeString("externmessageid", dt.Rows[i][9].ToString()); writer.WriteAttributeString("externprocid", "3"); writer.WriteAttributeString("externsystemid", dt.Rows[i][8].ToString()); writer.WriteAttributeString("externuserid", dt.Rows[i][4].ToString()); writer.WriteAttributeString("messagetime", curDate.ToString("yyyyMMdd hhmmss")); writer.WriteAttributeString("statut", "1"); writer.WriteAttributeString("timestampproc", curDate.ToString("yyyyMMdd hhmmss")); writer.WriteAttributeString("externprestationid", dt.Rows[i][5].ToString()); writer.WriteStartElement("messagedeliv"); // attributes for <messagedeliv> writer.WriteAttributeString("doublecontrol", "0"); writer.WriteAttributeString("externdelivid", "-"); writer.WriteAttributeString("externdelividint", "23"); writer.WriteAttributeString("externuserid", dt.Rows[i][4].ToString()); writer.WriteAttributeString("timestampdeliv", curDate.ToString("yyyyMMdd hhmmss")); // </messagedeliv> writer.WriteEndElement(); // write the last </message> element writer.WriteEndElement(); break; // deliverable 8 created, process 13 terminared, final status case "Réexamen standard accepté": // <message> writer.WriteStartElement("message"); curDate = Convert.ToDateTime(dt.Rows[i][3]); writer.WriteAttributeString("caseid", dt.Rows[i][6].ToString()); writer.WriteAttributeString("caseidremark", ""); writer.WriteAttributeString("externmessageid", dt.Rows[i][9].ToString()); writer.WriteAttributeString("externprocid", "13"); writer.WriteAttributeString("externsystemid", dt.Rows[i][8].ToString()); writer.WriteAttributeString("externuserid", dt.Rows[i][4].ToString()); writer.WriteAttributeString("messagetime", curDate.ToString("yyyyMMdd hhmmss")); writer.WriteAttributeString("statut", "1"); writer.WriteAttributeString("timestampproc", curDate.ToString("yyyyMMdd hhmmss")); writer.WriteAttributeString("externprestationid", dt.Rows[i][5].ToString()); writer.WriteStartElement("messagedeliv"); // attributes for <messagedeliv> writer.WriteAttributeString("doublecontrol", "0"); writer.WriteAttributeString("externdelivid", "-"); writer.WriteAttributeString("externdelividint", "8"); writer.WriteAttributeString("externuserid", dt.Rows[i][4].ToString()); writer.WriteAttributeString("timestampdeliv", curDate.ToString("yyyyMMdd hhmmss")); // </messagedeliv> writer.WriteEndElement(); // write the last </message> element writer.WriteEndElement(); break; } prgBarProcess.PerformStep(); } // write the </root> element writer.WriteEndElement(); // Write end of document writer.WriteEndDocument(); writer.Close(); prgBarProcess.Enabled = false; radStep3.Enabled = false; radStep5.Enabled = true; radStep5.Checked = true; Cursor = Cursors.Default; MessageBox.Show("Génération du fichier XML terminée avec succès (" + txtXmlPath.Text + ")", m_DialogTitle); }
public DataTable ReadExcel(string fileName, string sheetName) { DataTable dtExport = new DataTable(); DataTable dtXlSheets = new DataTable(); OleDbConnection oleDbConnection = null; OleDbDataAdapter oledbCommand; try { oleDbConnection = new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source='" + fileName + "';Extended Properties='Excel 8.0;HDR=No;IMEX=1;'"); try { oleDbConnection.Open(); } catch { oleDbConnection = new OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;" + "Data Source=" + fileName + ";Extended Properties='Excel 12.0;HDR=No;IMEX=1;'"); oleDbConnection.Open(); } dtXlSheets = oleDbConnection.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null); DataRow[] dr = dtXlSheets.Select("TABLE_NAME = '" + sheetName + "$" + "'"); if (dr == null || dr.Length == 0) { MessageData msgData = new MessageData("mmce00008", Properties.Resources.mmce00008, sheetName); throw new Framework.ApplicationException(msgData); } oledbCommand = new OleDbDataAdapter("select * from [" + sheetName + "$]", oleDbConnection); oledbCommand.FillSchema(dtExport, SchemaType.Source); oledbCommand.Fill(dtExport); if (dtExport != null && dtExport.Rows.Count > 0) { DataRow headerRow = dtExport.Rows[0]; foreach (DataColumn column in dtExport.Columns) { if (!string.IsNullOrWhiteSpace(headerRow[column.ColumnName.ToString()].ToString())) { column.ColumnName = headerRow[column.ColumnName.ToString()].ToString(); } } headerRow.Delete(); dtExport.AcceptChanges(); foreach (DataRow dataRow in dtExport.Rows) { foreach (var colValue in dataRow.ItemArray) { if (!string.IsNullOrEmpty(colValue.ToString())) { break; } dataRow.Delete(); } } dtExport.AcceptChanges(); } oleDbConnection.Close(); } catch (Framework.ApplicationException exception) { throw exception; } catch (Framework.SystemException exception) { throw exception; } catch (Exception exception) { throw exception; } finally { if (oleDbConnection != null) { oleDbConnection.Close(); oleDbConnection.Dispose(); } } return(dtExport); }
public DataTable GetTable(string strTableName) { string crlfReplacementVal = Convert.ToChar(7).ToString(); try { //Open and query if (OleConn == null) { Open(); } if (OleConn != null) { if (OleConn.State != ConnectionState.Open) { throw new Exception("Connection cannot open error."); } } if (SetSheetQuerySelect() == false) { return(null); } //Fill table var oleAdapter = new OleDbDataAdapter { SelectCommand = OleCmdSelect }; var dt = new DataTable(strTableName); oleAdapter.FillSchema(dt, SchemaType.Source); oleAdapter.Fill(dt); if (Headers == false) { if (StrSheetRange.IndexOf(":") > 0) { string firstCol = StrSheetRange.Substring(0, StrSheetRange.IndexOf(":") - 1); int intCol = ColNumber(firstCol); for (int intI = 0; intI < dt.Columns.Count; intI++) { dt.Columns[intI].Caption = ColName(intCol + intI); } } } SetPrimaryKey(dt); //Cannot delete rows in Excel workbook dt.DefaultView.AllowDelete = false; //Clean up OleCmdSelect.Dispose(); OleCmdSelect = null; oleAdapter.Dispose(); if (KeepConnectionOpen == false) { Close(); } // replace CRLFReplacementVal with '\n' in the Step Data field string tempVal; foreach (DataRow myRow in dt.Rows) { object[] tempVals = myRow.ItemArray; if (tempVals[1].GetType() != typeof(DBNull)) { tempVal = (string)tempVals[1]; tempVal = tempVal.Replace(crlfReplacementVal, Convert.ToString('\n')); tempVals[1] = tempVal; myRow.ItemArray = tempVals; dt.AcceptChanges(); } } return(dt); } catch (OleDbException ex) { MessageBox.Show(ex.Message, "File Read Error", MessageBoxButtons.OK, MessageBoxIcon.Error); return(null); } }
protected void btnImport_Click(object sender, EventArgs e) { if (FileUpload1.HasFile) { PanelMsg.Visible = false; DirectoryInfo DI = new DirectoryInfo(Server.MapPath(@"~/UpLoadPath/ItemMaster/")); FileInfo[] Delfiles = DI.GetFiles("*.xlsx"); int i = 0; foreach (FileInfo fi in Delfiles) { System.IO.File.Delete(DI + "/" + Delfiles[i]); i++; } string filename1 = Path.GetFileName(FileUpload1.PostedFile.FileName); FileUpload1.SaveAs(Server.MapPath(@"~/UpLoadPath/ItemMaster/" + filename1)); string filename = Server.MapPath(@"~/UpLoadPath/ItemMaster/" + filename1); //string SheetName = "Sheet1"; string[] SheetName1 = GetExcelSheetNames(filename); string SheetName = SheetName1[0].ToString();// "ContractualMonthAttend-3"; string Con = @"Provider=Microsoft.ACE.OLEDB.12.0;" + @"Data Source=" + filename + ";" + @"Extended Properties=Excel 12.0"; //+Convert.ToChar(34).ToString() + //@"Excel 8.0;" + "Imex=2;" + "HDR=Yes;" + Convert.ToChar(34).ToString(); OleDbConnection oleConn = new OleDbConnection(Con); oleConn.Open(); OleDbCommand oleCmdSelect = new OleDbCommand(); oleCmdSelect = new OleDbCommand( @"SELECT * FROM [" + SheetName + "]", oleConn); OleDbDataAdapter oleAdapter = new OleDbDataAdapter(); oleAdapter.SelectCommand = oleCmdSelect; DataTable dt = new DataTable("Table1"); oleAdapter.FillSchema(dt, SchemaType.Source); oleAdapter.Fill(dt); oleCmdSelect.Dispose(); oleCmdSelect = null; oleAdapter.Dispose(); oleAdapter = null; oleConn.Dispose(); oleConn = null; if (dt.Columns[1].ColumnName != "Item Category") { PanelMsg.Visible = true; lblmsg.Text = "The 2nd(B) Column Name Name Sould be 'Item Category' instead of " + dt.Columns[1].ColumnName + ""; ScriptManager.RegisterStartupScript(this, GetType(), "displayalertmessage", "Showalert();", true); return; } if (dt.Columns[2].ColumnName != "Item Code") { PanelMsg.Visible = true; lblmsg.Text = "The 3rd(C) Column Name Name Sould be 'Item Code' instead of " + dt.Columns[2].ColumnName + ""; ScriptManager.RegisterStartupScript(this, GetType(), "displayalertmessage", "Showalert();", true); return; } if (dt.Columns[3].ColumnName != "Item Name") { PanelMsg.Visible = true; lblmsg.Text = "The 4th(D) Column Name Name Sould be 'Item Name' instead of " + dt.Columns[3].ColumnName + ""; ScriptManager.RegisterStartupScript(this, GetType(), "displayalertmessage", "Showalert();", true); return; } if (dt.Columns[4].ColumnName != "Tariff Code") { PanelMsg.Visible = true; lblmsg.Text = "The 5th(E) Column Name Name Sould be 'Tariff Code' instead of " + dt.Columns[4].ColumnName + ""; ScriptManager.RegisterStartupScript(this, GetType(), "displayalertmessage", "Showalert();", true); return; } if (dt.Columns[5].ColumnName != "Costing Head") { PanelMsg.Visible = true; lblmsg.Text = "The 6th(F) Column Name Name Sould be 'Costing Head' instead of " + dt.Columns[5].ColumnName + ""; ScriptManager.RegisterStartupScript(this, GetType(), "displayalertmessage", "Showalert();", true); return; } if (dt.Columns[6].ColumnName != "Ac Head Purchase") { PanelMsg.Visible = true; lblmsg.Text = "The 7th(G) Column Name Name Sould be 'Ac Head Purchase' instead of " + dt.Columns[6].ColumnName + ""; ScriptManager.RegisterStartupScript(this, GetType(), "displayalertmessage", "Showalert();", true); return; } if (dt.Columns[7].ColumnName != "Ac Head Sale") { PanelMsg.Visible = true; lblmsg.Text = "The 8th(H) Column Name Name Sould be 'Ac Head Sale' instead of " + dt.Columns[7].ColumnName + ""; ScriptManager.RegisterStartupScript(this, GetType(), "displayalertmessage", "Showalert();", true); return; } if (dt.Columns[8].ColumnName != "UOM") { PanelMsg.Visible = true; lblmsg.Text = "The 9th(I) Column Name Name Sould be 'Ac Head Purchase' instead of " + dt.Columns[8].ColumnName + ""; ScriptManager.RegisterStartupScript(this, GetType(), "displayalertmessage", "Showalert();", true); return; } if (dt.Columns[9].ColumnName != "Weight OUM") { PanelMsg.Visible = true; lblmsg.Text = "The 10th(J) Column Name Name Sould be 'Weight OUM' instead of " + dt.Columns[9].ColumnName + ""; ScriptManager.RegisterStartupScript(this, GetType(), "displayalertmessage", "Showalert();", true); return; } if (dt.Columns[10].ColumnName != "IS DEVELOPMENT (Y/N)") { PanelMsg.Visible = true; lblmsg.Text = "The 11th(K) Column Name Name Sould be 'IS DEVELOPMENT (Y/N)' instead of " + dt.Columns[10].ColumnName + ""; ScriptManager.RegisterStartupScript(this, GetType(), "displayalertmessage", "Showalert();", true); return; } if (dt.Rows.Count > 0) { for (int k = 0; k < dt.Rows.Count; k++) { DataTable dtIcodeCheck = CommonClasses.Execute("select * from ITEM_MASTER where ES_DELETE=0 and I_CODENO='" + dt.Rows[k]["Item Code"].ToString().TrimEnd().TrimStart() + "'"); if (dtIcodeCheck.Rows.Count > 0) { PanelMsg.Visible = true; lblmsg.Text = "Duplicate Item Code found at Sr No " + (k + 1); ScriptManager.RegisterStartupScript(this, GetType(), "displayalertmessage", "Showalert();", true); return; } DataTable dtInameCheck = CommonClasses.Execute("select * from ITEM_MASTER where ES_DELETE=0 and I_NAME='" + dt.Rows[k]["Item Name"].ToString().TrimEnd().TrimStart() + "'"); if (dtInameCheck.Rows.Count > 0) { PanelMsg.Visible = true; lblmsg.Text = "Duplicate Item Name found at Sr No " + (k + 1); ScriptManager.RegisterStartupScript(this, GetType(), "displayalertmessage", "Showalert();", true); return; } DataTable dtCat = CommonClasses.Execute("SELECT * FROM ITEM_CATEGORY_MASTER WHERE I_CAT_NAME ='" + dt.Rows[k]["Item Category"].ToString().TrimEnd().TrimStart() + "'"); if (dtCat.Rows.Count == 0) { PanelMsg.Visible = true; lblmsg.Text = "Item Category Not found at Sr No " + (k + 1); ScriptManager.RegisterStartupScript(this, GetType(), "displayalertmessage", "Showalert();", true); return; } DataTable TariffCode = CommonClasses.Execute("select * FROM EXCISE_TARIFF_MASTER WHERE E_TARIFF_NO='" + dt.Rows[k]["Tariff Code"].ToString().TrimEnd().TrimStart() + "'"); if (TariffCode.Rows.Count == 0) { PanelMsg.Visible = true; lblmsg.Text = "Tariff Code Not found at Sr No " + (k + 1); ScriptManager.RegisterStartupScript(this, GetType(), "displayalertmessage", "Showalert();", true); return; } DataTable dtAcPur = CommonClasses.Execute("SELECT * FROM TALLY_MASTER WHERE TALLY_NAME ='" + dt.Rows[k]["Ac Head Purchase"].ToString().TrimEnd().TrimStart() + "'"); if (dtAcPur.Rows.Count == 0) { PanelMsg.Visible = true; lblmsg.Text = "Purchase Ac Head Not found at Sr No " + (k + 1); ScriptManager.RegisterStartupScript(this, GetType(), "displayalertmessage", "Showalert();", true); return; } DataTable dtAcSale = CommonClasses.Execute("SELECT * FROM TALLY_MASTER WHERE TALLY_NAME ='" + dt.Rows[k]["Ac Head Sale"].ToString().TrimEnd().TrimStart() + "'"); if (dtAcSale.Rows.Count == 0) { PanelMsg.Visible = true; lblmsg.Text = "Sale Ac Head Not found at Sr No " + (k + 1); ScriptManager.RegisterStartupScript(this, GetType(), "displayalertmessage", "Showalert();", true); return; } DataTable dtUOM = CommonClasses.Execute("SELECT * FROM ITEM_UNIT_MASTER WHERE I_UOM_NAME='" + dt.Rows[k]["UOM"].ToString().TrimEnd().TrimStart() + "'"); if (dtUOM.Rows.Count == 0) { PanelMsg.Visible = true; lblmsg.Text = "Item UOM Not found at Sr No " + (k + 1); ScriptManager.RegisterStartupScript(this, GetType(), "displayalertmessage", "Showalert();", true); return; } DataTable dtWUOM = CommonClasses.Execute("SELECT * FROM ITEM_UNIT_MASTER WHERE I_UOM_NAME='" + dt.Rows[k]["Weight OUM"].ToString().TrimEnd().TrimStart() + "'"); if (dtWUOM.Rows.Count == 0) { PanelMsg.Visible = true; lblmsg.Text = "Item weight UOM Not found at Sr No " + (k + 1); ScriptManager.RegisterStartupScript(this, GetType(), "displayalertmessage", "Showalert();", true); return; } } } else { PanelMsg.Visible = true; lblmsg.Text = "No rows Found"; ScriptManager.RegisterStartupScript(this, GetType(), "displayalertmessage", "Showalert();", true); return; } string I_Cat = ""; string I_code = ""; string I_Name = ""; string I_TARIFF_CODE = ""; string COST_HEAD = ""; string AC_HED_PER = ""; string AC_HED_SALE = ""; string UOM = ""; string W_UOM = ""; string ISDEV = ""; for (int j = 0; j < dt.Rows.Count; j++) { DataTable dtCat = CommonClasses.Execute("SELECT * FROM ITEM_CATEGORY_MASTER WHERE I_CAT_NAME ='" + dt.Rows[j]["Item Category"].ToString().TrimEnd().TrimStart() + "'"); I_Cat = dtCat.Rows[0]["I_CAT_CODE"].ToString(); I_code = dt.Rows[j]["Item Code"].ToString().TrimEnd().TrimStart(); I_Name = dt.Rows[j]["Item Name"].ToString().TrimEnd().TrimStart(); DataTable TariffCode = CommonClasses.Execute("select * FROM EXCISE_TARIFF_MASTER WHERE E_TARIFF_NO='" + dt.Rows[j]["Tariff Code"].ToString().TrimEnd().TrimStart() + "'"); I_TARIFF_CODE = TariffCode.Rows[0]["E_CODE"].ToString(); COST_HEAD = dt.Rows[j]["Costing Head"].ToString().TrimEnd().TrimStart(); DataTable dtAcPur = CommonClasses.Execute("SELECT * FROM TALLY_MASTER WHERE TALLY_NAME ='" + dt.Rows[j]["Ac Head Purchase"].ToString().TrimEnd().TrimStart() + "'"); AC_HED_PER = dtAcPur.Rows[0]["TALLY_CODE"].ToString(); DataTable dtAcSale = CommonClasses.Execute("SELECT * FROM TALLY_MASTER WHERE TALLY_NAME ='" + dt.Rows[j]["Ac Head Sale"].ToString().TrimEnd().TrimStart() + "'"); AC_HED_SALE = dtAcSale.Rows[0]["TALLY_CODE"].ToString(); DataTable dtUOM = CommonClasses.Execute("SELECT * FROM ITEM_UNIT_MASTER WHERE I_UOM_NAME='" + dt.Rows[j]["UOM"].ToString().TrimEnd().TrimStart() + "'"); UOM = dtUOM.Rows[0]["I_UOM_CODE"].ToString(); DataTable dtWUOM = CommonClasses.Execute("SELECT * FROM ITEM_UNIT_MASTER WHERE I_UOM_NAME='" + dt.Rows[j]["Weight OUM"].ToString().TrimEnd().TrimStart() + "'"); W_UOM = dtWUOM.Rows[0]["I_UOM_CODE"].ToString(); if (dt.Rows[j]["IS DEVELOPMENT (Y/N)"].ToString().TrimEnd().TrimStart().ToUpper() == "Y") { ISDEV = "True"; } else { ISDEV = "False"; } CommonClasses.Execute("INSERT INTO ITEM_MASTER (I_CM_COMP_ID,I_CAT_CODE,I_SCAT_CODE,I_CODENO,I_DRAW_NO,I_NAME,I_E_CODE,I_ACCOUNT_SALES,I_ACCOUNT_PURCHASE,I_UOM_CODE,I_MAX_LEVEL,I_MIN_LEVEL,I_REORDER_LEVEL,I_OP_BAL,I_OP_BAL_RATE,I_STORE_LOC,I_INV_RATE,I_RECEIPT_DATE,I_ISSUE_DATE,I_CURRENT_BAL,I_ACTIVE_IND,I_UWEIGHT,I_COSTING_HEAD,I_INV_CAT,I_OPEN_RATE,I_DENSITY,I_PIGMENT,I_SOLIDS,I_VOLATILE,I_WEIGHT_UOM,I_DEVELOMENT,I_TARGET_WEIGHT) VALUES ('" + (string)Session["CompanyId"] + "','" + I_Cat + "','0','" + I_code + "','" + I_code + "','" + I_Name + "','" + I_TARIFF_CODE + "','" + AC_HED_SALE + "','" + AC_HED_PER + "','" + UOM + "','0','0','0','0','0','','0','31/MAR/2017','31/MAR/2017','0','TRUE','0','" + COST_HEAD + "','1','0','0','0','0','0','" + W_UOM + "','" + ISDEV + "','0')"); } PanelMsg.Visible = true; lblmsg.Text = "Item Master Added Successfully..."; ScriptManager.RegisterStartupScript(this, GetType(), "displayalertmessage", "Showalert();", true); } #region Import_CSV //if (FileUpload1.HasFile) // { // string csvPath = Server.MapPath("~/UpLoadPath/") + Path.GetFileName(FileUpload1.PostedFile.FileName); // FileUpload1.SaveAs(csvPath); // DataTable dt = new DataTable(); // dt.Columns.AddRange(new DataColumn[3] { new DataColumn("Id", typeof(int)), // new DataColumn("Name", typeof(string)), // new DataColumn("Country",typeof(string)) }); // string csvData = File.ReadAllText(csvPath); // foreach (string row in csvData.Split('\n')) // { // if (!string.IsNullOrEmpty(row)) // { // dt.Rows.Add(); // int i = 0; // foreach (string cell in row.Split(',')) // { // dt.Rows[dt.Rows.Count - 1][i] = cell; // i++; // } // } // } //} #endregion Import CSV Comment }
ColumnMetadataCollection <OleDbType> GetColumns(string tableName, DataTable columns, DataTable?primaryKeys) { var result = new List <ColumnMetadata <OleDbType> >(); DataTable tableSchema; using (var con = new OleDbConnection(m_ConnectionBuilder.ConnectionString)) { using (var adapter = new OleDbDataAdapter($"SELECT * FROM [{tableName}] WHERE 1=0", con)) tableSchema = adapter.FillSchema(new DataTable() { Locale = CultureInfo.InvariantCulture }, SchemaType.Source) !; } foreach (DataColumn col in tableSchema.Columns) { var name = col.ColumnName; var isPrimaryKey = false; var isIdentity = col.AutoIncrement; OleDbType?type = null; if (primaryKeys != null) { foreach (DataRow row in primaryKeys.Rows) { if (string.Equals(row["TABLE_NAME"].ToString(), tableName, StringComparison.Ordinal) && string.Equals(row["COLUMN_NAME"].ToString(), name, StringComparison.Ordinal)) { isPrimaryKey = true; break; } } } bool? isNullable = null; long? maxLength = null; int? precision = null; int? scale = null; string typeName = ""; string fullTypeName = ""; //Task-290: Add support for full name foreach (DataRow row in columns.Rows) { if (string.Equals(row["TABLE_NAME"].ToString(), tableName, StringComparison.Ordinal) && string.Equals(row["COLUMN_NAME"].ToString(), name, StringComparison.Ordinal)) { type = (OleDbType)row["DATA_TYPE"]; isNullable = (bool)row["IS_NULLABLE"]; precision = row["NUMERIC_PRECISION"] != DBNull.Value ? (int?)row["NUMERIC_PRECISION"] : null; scale = row["NUMERIC_SCALE"] != DBNull.Value ? (int?)row["NUMERIC_SCALE"] : null; maxLength = row["CHARACTER_MAXIMUM_LENGTH"] != DBNull.Value ? (long?)row["CHARACTER_MAXIMUM_LENGTH"] : null; break; } } Type?clrType = null; if (type.HasValue) { typeName = type.Value.ToString(); clrType = ToClrType(type.Value, isNullable ?? true, (int?)maxLength); } result.Add(new ColumnMetadata <OleDbType>(name, false, isPrimaryKey, isIdentity, typeName, type ?? OleDbType.Empty, $"[{name}]", isNullable, (int?)maxLength, precision, scale, fullTypeName, clrType)); } return(new ColumnMetadataCollection <OleDbType>(tableName, result)); }
public static void Print_Excel(string datasource) { DataTable dt = new DataTable("newtable"); OleDbConnectionStringBuilder csbuilder = new OleDbConnectionStringBuilder(); csbuilder.Provider = "Microsoft.ACE.OLEDB.12.0"; csbuilder.DataSource = @"..\..\..\Temp\" + datasource; csbuilder.Add("Extended Properties", "Excel 12.0 Xml;HDR=YES"); //Console.WriteLine(datasource); using (OleDbConnection connection = new OleDbConnection(csbuilder.ConnectionString)) { connection.Open(); string selectSql = @"SELECT * FROM [Sales$]"; using (OleDbDataAdapter adapter = new OleDbDataAdapter(selectSql, connection)) { adapter.FillSchema(dt, SchemaType.Source); adapter.Fill(dt); } connection.Close(); } string dateStr = datasource.Substring(0, datasource.IndexOf('/')); DateTime date = DateTime.Parse(dateStr); Sale sale = new Sale(); sale.Date = date; bool endOfTable = false; foreach (DataRow row in dt.Rows) { //Console.WriteLine("-------------"); //Console.WriteLine(row); int countColumn = 1; using (var context = new SupermarketSQL.Context.SupermarketContext()) { foreach (var item in row.ItemArray) { string itemStr = item.ToString(); if (itemStr.StartsWith("Total")) { endOfTable = true; } if (itemStr != "") { if (sale.Supermarket == null) { sale.Supermarket = itemStr; //Console.WriteLine(sale.Supermarket); } if (char.IsDigit(itemStr[0]) && !endOfTable) { if (countColumn == 1) { sale.ProductId = int.Parse(itemStr); countColumn++; } else if (countColumn == 2) { sale.Quantity = int.Parse(itemStr); countColumn++; } else if (countColumn == 3) { sale.UnitPrice = decimal.Parse(itemStr); countColumn++; } else if (countColumn == 4) { sale.Sum = decimal.Parse(itemStr); countColumn++; context.Sales.Add(sale); context.SaveChanges(); } } //Console.Write(item + " "); } } //Console.WriteLine(); } } //Console.WriteLine("End table \n"); }
private void cmdProcess_Click(object sender, EventArgs e) { try { if (txtXmlPath.Text == "" || txtExcelPath.Text == "") { MessageBox.Show("Vous devez sélectionner un fichier Excel source et un fichier XML destination !", m_DialogTitle); return; } radStep5.Enabled = false; prgBarProcess.Value = 0; prgBarProcess.Enabled = false; Cursor = Cursors.WaitCursor; // open oledb connection on excel file OleDbConnection oleConn = new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + txtExcelPath.Text + ";Extended Properties=\"Excel 8.0;HDR=Yes;IMEX=1;\""); oleConn.Open(); // select all in sheet OleDbCommand oleCmdSelect = new OleDbCommand(@"SELECT * FROM [" + "Feuil1$" + "]", oleConn); radStep2.Enabled = true; radStep2.Checked = true; // get the data OleDbDataAdapter oleAdapter = new OleDbDataAdapter(); oleAdapter.SelectCommand = oleCmdSelect; DataTable dt = new DataTable("test"); oleAdapter.FillSchema(dt, SchemaType.Source); oleAdapter.Fill(dt); // open the XML Writer XmlTextWriter writer; string xmlFilename = txtXmlPath.Text; writer = new XmlTextWriter(xmlFilename, System.Text.Encoding.UTF8); // indent the XML document for readability writer.Formatting = System.Xml.Formatting.Indented; // call WriteStartDocument to write XML declaration writer.WriteStartDocument(); // write the root element writer.WriteStartElement("root"); prgBarProcess.Minimum = 0; prgBarProcess.Maximum = dt.Rows.Count; System.Threading.Thread.Sleep(1000); radStep2.Enabled = false; radStep3.Enabled = true; radStep3.Checked = true; prgBarProcess.Enabled = true; bool flagFirstPass = true; // parse every record for (int i = 0; i < dt.Rows.Count; ++i) { // check if recrod is Message (M) or Deliverable (D) if (dt.Rows[i][0].ToString() == "M") { if (!flagFirstPass) { writer.WriteEndElement(); } else { flagFirstPass = false; } // <message> writer.WriteStartElement("message"); // attributes for <message> writer.WriteAttributeString("caseid", dt.Rows[i][3].ToString()); writer.WriteAttributeString("caseidremark", dt.Rows[i][11].ToString()); writer.WriteAttributeString("externmessageid", dt.Rows[i][1].ToString()); writer.WriteAttributeString("externprocid", dt.Rows[i][4].ToString()); writer.WriteAttributeString("externsystemid", dt.Rows[i][2].ToString()); writer.WriteAttributeString("externuserid", dt.Rows[i][5].ToString()); writer.WriteAttributeString("messagetime", dt.Rows[i][6].ToString() + " " + dt.Rows[i][7].ToString()); writer.WriteAttributeString("statut", dt.Rows[i][10].ToString()); writer.WriteAttributeString("timestampproc", dt.Rows[i][8].ToString() + " " + dt.Rows[i][9].ToString()); writer.WriteAttributeString("externprestationid", dt.Rows[i][12].ToString()); } else { // <messagedeliv> writer.WriteStartElement("messagedeliv"); // attributes for <messagedeliv> writer.WriteAttributeString("doublecontrol", dt.Rows[i][6].ToString()); writer.WriteAttributeString("externdelivid", dt.Rows[i][1].ToString()); writer.WriteAttributeString("externdelividint", dt.Rows[i][2].ToString()); writer.WriteAttributeString("externuserid", dt.Rows[i][7].ToString()); writer.WriteAttributeString("timestampdeliv", dt.Rows[i][4].ToString() + " " + dt.Rows[i][5].ToString()); // </messagedeliv> writer.WriteEndElement(); } prgBarProcess.PerformStep(); } // write the last </message> element writer.WriteEndElement(); // write the </root> element writer.WriteEndElement(); // write End of document writer.WriteEndDocument(); writer.Close(); prgBarProcess.Enabled = false; radStep3.Enabled = false; radStep5.Enabled = true; radStep5.Checked = true; Cursor = Cursors.Default; MessageBox.Show("Génération du fichier XML terminée avec succès (" + txtXmlPath.Text + ")", m_DialogTitle); } catch (Exception err) { MessageBox.Show("Une erreur s'est produite : " + err.Message, m_DialogTitle); radStep2.Enabled = false; radStep3.Enabled = false; radStep5.Enabled = false; prgBarProcess.Value = 0; cmdProcess.Enabled = true; } }
/// <summary> /// Used for select query. /// </summary> /// <param name="sqlCommand">The SQL string.</param> /// <param name="querySchema">if set to <c>true</c> [query schema].</param> /// <param name="schemaType">Type of the schema.</param> /// <returns> /// A Data Set with the selected Data tables and Data Rows. /// </returns> public DataSet Query( string sqlCommand, bool querySchema = false, SchemaType schemaType = SchemaType.Source) { var result = new DataSet(); if (this.ConnectionType == ConnType.MSSQL) { int retryCount = 0; while (true) { try { using (var con = new SqlConnection(this.connectionStringBuilderForSql.ConnectionString)) { con.Open(); var adapter = new SqlDataAdapter(); adapter.SelectCommand = new SqlCommand(sqlCommand, con); adapter.SelectCommand.CommandTimeout = SqlCmdTimeout; if (querySchema) { adapter.FillSchema(result, schemaType); } adapter.Fill(result); break; } } catch (Exception ex) { if (ex is SqlException && Enum.IsDefined(typeof(RetryableSqlErrors), ((SqlException)ex).Number) && retryCount < MaxRetry) { retryCount++; Thread.Sleep(((SqlException)ex).Number == (int)RetryableSqlErrors.Timeout ? LongWait : ShortWait); continue; } else { this.WriteErrorLog(ex.ToString(), sqlCommand); throw; } } } } else if (this.ConnectionType == ConnType.ODBC) { try { using (var con = new OdbcConnection(this.connectionStringBuilderForOdbc.ConnectionString)) { con.Open(); var adapter = new OdbcDataAdapter(); adapter.SelectCommand = new OdbcCommand(sqlCommand, con); adapter.SelectCommand.CommandTimeout = SqlCmdTimeout; if (querySchema) { adapter.FillSchema(result, schemaType); } adapter.Fill(result); } } catch (Exception ex) { this.WriteErrorLog(ex.ToString(), sqlCommand); throw; } } else if (this.ConnectionType == ConnType.OLEDB) { try { using (var con = new OleDbConnection(this.connectionStringBuilderForOledb.ConnectionString)) { con.Open(); var adapter = new OleDbDataAdapter(); adapter.SelectCommand = new OleDbCommand(sqlCommand, con); adapter.SelectCommand.CommandTimeout = SqlCmdTimeout; if (querySchema) { adapter.FillSchema(result, schemaType); } adapter.Fill(result); } } catch (Exception ex) { this.WriteErrorLog(ex.ToString(), sqlCommand); throw; } } return(result); }
//===================================================================== /// <summary> /// Create the data source for the demo. /// </summary> /// <remarks>You can use the designer to create the data source and use strongly typed data sets. For /// this demo, we'll do it by hand. /// </remarks> private void CreateDataSource() { // The test database should be in the project folder dbConn = new OleDbConnection(@"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=.\TestData.mdb"); daAddresses = new OleDbDataAdapter(); dsAddresses = new DataSet(); // Set the table name daAddresses.TableMappings.Add("Table", "Addresses"); // In a real application we wouldn't use literal SQL but we will for the demo daAddresses.SelectCommand = new OleDbCommand("Select * From Addresses Order By LastName", dbConn); daAddresses.DeleteCommand = new OleDbCommand("Delete From Addresses Where ID = @paramID", dbConn); daAddresses.DeleteCommand.Parameters.Add(new OleDbParameter("@paramID", OleDbType.Integer, 0, ParameterDirection.Input, false, 0, 0, "ID", DataRowVersion.Original, null)); daAddresses.InsertCommand = new OleDbCommand( "INSERT INTO Addresses (FirstName, LastName, Address, City, State, Zip, SumValue) " + "VALUES (@paramFN, @paramLN, @paramAddress, @paramCity, @paramState, @paramZip, @paramSumValue)", dbConn); daAddresses.InsertCommand.Parameters.Add(new OleDbParameter("@paramFirstName", OleDbType.VarWChar, 20, "FirstName")); daAddresses.InsertCommand.Parameters.Add(new OleDbParameter("@paramLastName", OleDbType.VarWChar, 30, "LastName")); daAddresses.InsertCommand.Parameters.Add(new OleDbParameter("@paramAddress", OleDbType.VarWChar, 50, "Address")); daAddresses.InsertCommand.Parameters.Add(new OleDbParameter("@paramCity", OleDbType.VarWChar, 20, "City")); daAddresses.InsertCommand.Parameters.Add(new OleDbParameter("@paramState", OleDbType.VarWChar, 2, "State")); daAddresses.InsertCommand.Parameters.Add(new OleDbParameter("@paramZip", OleDbType.VarWChar, 10, "Zip")); daAddresses.InsertCommand.Parameters.Add(new OleDbParameter("@paramSumValue", OleDbType.Integer, 0, "SumValue")); daAddresses.UpdateCommand = new OleDbCommand( "UPDATE Addresses SET FirstName = @paramFirstName, LastName = @paramLastName, " + "Address = @paramAddress, City = @paramCity, State = @paramState, Zip = @paramZip, " + "SumValue = @paramSumValue WHERE ID = @paramID", dbConn); daAddresses.UpdateCommand.Parameters.Add(new OleDbParameter("@paramFirstName", OleDbType.VarWChar, 20, "FirstName")); daAddresses.UpdateCommand.Parameters.Add(new OleDbParameter("@paramLastName", OleDbType.VarWChar, 30, "LastName")); daAddresses.UpdateCommand.Parameters.Add(new OleDbParameter("@paramAddress", OleDbType.VarWChar, 50, "Address")); daAddresses.UpdateCommand.Parameters.Add(new OleDbParameter("@paramCity", OleDbType.VarWChar, 20, "City")); daAddresses.UpdateCommand.Parameters.Add(new OleDbParameter("@paramState", OleDbType.VarWChar, 2, "State")); daAddresses.UpdateCommand.Parameters.Add(new OleDbParameter("@paramZip", OleDbType.VarWChar, 10, "Zip")); daAddresses.UpdateCommand.Parameters.Add(new OleDbParameter("@paramSumValue", OleDbType.Integer, 0, "SumValue")); daAddresses.UpdateCommand.Parameters.Add(new OleDbParameter("@paramID", OleDbType.Integer, 0, ParameterDirection.Input, false, 0, 0, "ID", System.Data.DataRowVersion.Original, null)); // Fill in the schema for auto-increment etc daAddresses.FillSchema(dsAddresses, SchemaType.Mapped); // Connect the Row Updated event so that we can retrieve the new primary key values as they are // identity values. daAddresses.RowUpdated += daAddresses_RowUpdated; // Load the state codes for the row template's shared data source OleDbDataAdapter daStates = new OleDbDataAdapter("Select State, StateDesc From States", dbConn); DataTable dtStates = new DataTable(); daStates.Fill(dtStates); // Add a blank row to allow no selection dtStates.Rows.InsertAt(dtStates.NewRow(), 0); dlList.SharedDataSources.Add("States", dtStates.DefaultView); }
private void cmdProcess_Click(object sender, EventArgs e) { deleteIncompleteRows(); DateTime curDate = new DateTime(); if (txtXmlPath.Text == "" || txtExcelPath.Text == "") { MessageBox.Show("Vous devez sélectionner un fichier Excel source et un fichier XML destination !", m_DialogTitle); return; } radStep5.Enabled = false; prgBarProcess.Value = 0; prgBarProcess.Enabled = false; Cursor = Cursors.WaitCursor; // open oledb connection on excel file OleDbConnection oleConn = new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + txtExcelPath.Text + ";Extended Properties=\"Excel 8.0;HDR=Yes;IMEX=1;\""); oleConn.Open(); // select all in sheet OleDbCommand oleCmdSelect = new OleDbCommand(@"SELECT * FROM [" + "Feuil1$" + "]", oleConn); radStep2.Enabled = true; radStep2.Checked = true; // get the data OleDbDataAdapter oleAdapter = new OleDbDataAdapter(); oleAdapter.SelectCommand = oleCmdSelect; DataTable dt = new DataTable("test"); oleAdapter.FillSchema(dt, SchemaType.Source); oleAdapter.Fill(dt); // open the XML Writer XmlTextWriter writer; string xmlFilename = txtXmlPath.Text; writer = new XmlTextWriter(xmlFilename, System.Text.Encoding.UTF8); // indent the XML document for readability writer.Formatting = System.Xml.Formatting.Indented; // call WriteStartDocument to write XML declaration writer.WriteStartDocument(); // write the root element writer.WriteStartElement("root"); prgBarProcess.Minimum = 0; prgBarProcess.Maximum = dt.Rows.Count; System.Threading.Thread.Sleep(1000); radStep2.Enabled = false; radStep3.Enabled = true; radStep3.Checked = true; prgBarProcess.Enabled = true; string[] criteres; int countDiscardedRows = 0; // parse every record for (int i = 0; i < dt.Rows.Count; ++i) { criteres = getCriteresValues(dt.Rows[i][1].ToString(), dt.Rows[i][2].ToString()); if (criteres[0] == null) { prgBarProcess.PerformStep(); ++countDiscardedRows; continue; } writer.WriteStartElement("message"); curDate = Convert.ToDateTime(dt.Rows[i][3]); writer.WriteAttributeString("caseid", dt.Rows[i][6].ToString()); writer.WriteAttributeString("caseidremark", ""); writer.WriteAttributeString("externmessageid", dt.Rows[i][9].ToString()); if (criteres[0] != null) { // from table writer.WriteAttributeString("externprocid", criteres[0].ToString()); } writer.WriteAttributeString("externsystemid", dt.Rows[i][8].ToString()); writer.WriteAttributeString("externuserid", dt.Rows[i][4].ToString()); writer.WriteAttributeString("messagetime", curDate.ToString("yyyyMMdd hhmmss")); writer.WriteAttributeString("statut", dt.Rows[i][7].ToString()); writer.WriteAttributeString("timestampproc", curDate.ToString("yyyyMMdd hhmmss")); writer.WriteAttributeString("externprestationid", dt.Rows[i][5].ToString()); writer.WriteStartElement("messagedeliv"); // attributes for <messagedeliv> writer.WriteAttributeString("doublecontrol", "0"); writer.WriteAttributeString("externdelivid", "-"); if (criteres[1] != null) { // from table writer.WriteAttributeString("externdelividint", criteres[1].ToString()); } writer.WriteAttributeString("externuserid", dt.Rows[i][4].ToString()); writer.WriteAttributeString("timestampdeliv", curDate.ToString("yyyyMMdd hhmmss")); // </messagedeliv> writer.WriteEndElement(); // write the last </message> element writer.WriteEndElement(); prgBarProcess.PerformStep(); } // write the </root> element writer.WriteEndElement(); // write End of document writer.WriteEndDocument(); writer.Close(); prgBarProcess.Enabled = false; radStep3.Enabled = false; radStep5.Enabled = true; radStep5.Checked = true; Cursor = Cursors.Default; MessageBox.Show("Génération du fichier XML terminée avec succès, " + countDiscardedRows + " lignes ignorées (" + txtXmlPath.Text + ")", m_DialogTitle); }
protected void Button1_Click(object sender, EventArgs e) { try { GridView1.ControlStyle.Font.Size = 10; GridView2.ControlStyle.Font.Size = 10; GridView3.ControlStyle.Font.Size = 10; GridView4.ControlStyle.Font.Size = 10; GridView5.ControlStyle.Font.Size = 10; //GridView2.Visible = true; GridView1.Visible = true; GridView2.Visible = true; //GridView1.DataSource = null; //GridView2.DataSource = null; string uid = Request.QueryString["UID"]; //OleDbConnection con = new OleDbConnection(@"Provider=Microsoft.Jet.OLEDB.4.0;Data Source= " + Server.MapPath("~/Access//Tablas.mdb")); //OleDbConnection con = new OleDbConnection(@"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=D:\CSK\Tablas.mdb"); OleDbConnection con = new OleDbConnection(@"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=W:\test\access\Tablas.mdb"); OleDbDataAdapter ad = new OleDbDataAdapter("SELECT NumOrd AS UID,CodPie AS Piece,ProPie AS Vend,PlaPie AS DelDate,PieFin AS OK,PreAce AS Material FROM [Ordenes de fabricación (piezas)] WHERE NumOrd = " + uid, con); DataTable dt = new DataTable(); ad.FillSchema(dt, SchemaType.Source); dt.Columns["DelDate"].DataType = typeof(string); ad.Fill(dt); foreach (DataRow dr in dt.Rows) { if (dr["DelDate"] != DBNull.Value) { dr["DelDate"] = Convert.ToDateTime(dr["DelDate"]).ToString("dd/MM/yyyy"); } } string article = Request.QueryString["Article"]; OleDbDataAdapter ad1 = new OleDbDataAdapter("SELECT CodPie AS Piece,NomPie AS Description,MatPie AS Material,CalPie AS Grade,DurPie AS Hardness,DiaExt AS OD,Longit AS Length,DiaInt AS ID,ModPie AS M FROM [Artículos de clientes (piezas)] WHERE CodArt = '" + article + "' ", con); DataTable dt1 = new DataTable(); ad1.Fill(dt1); if (dt.Rows.Count > 0) { GridView1.Visible = true; } else { GridView1.Visible = false; } if (dt1.Rows.Count > 0) { GridView2.Visible = true; } else { GridView2.Visible = false; } GridView1.DataSource = dt; GridView1.DataBind(); GridView2.DataSource = dt1; GridView2.DataBind(); } catch (Exception exception) { Response.Write(exception.ToString()); } }
/***************************************************************************** * This method is called from the click event of Save button and * SelectedIndexChanged event of Products DropDown list. * * The purpose of this method is to insert a new advertisement or * update an existing advertisement for the product chosen from * the 'productCbBx' list. The advertisement data is stored in the * database table 'PrintMedia'. * * The flow of this method is as follows: * 1. Instantiate an OleDbDataAdapter object with the query for 'PrintMedia' * table. * 2. Configure the schema to match with Data Source. Set Primary Key information. * 3. OleDbCommandBuilder automatically generates the command * (in this case SelectCommand)for loading data for the given query. * 4. The Dataset is filled with data that is loaded through OleDbAdapter, * 5. Create a DataRow in a DataTable contained in the DataSet for a new * advertisement or find the current advertisement DataRow for existing * advertisement. * 6. Convert the new advertisement image into a byte array. * 7. Assign the corresponding values to the columns in the Data Row. * 8. Add the Data Row to the Data Set for a new advertisement or end the edit * operation for existing advertisement. * 9. Update the database with the Data Set values. Hence adding/updating * 'PrintMedia' table data. *******************************************************************************/ private void updateData() { try { //Check if Ad Image or Ad Text is changed. if (strImageName != "" || strExistText != adTextBx.Text) { //Change the default cursor to 'WaitCursor'(an HourGlass) this.Cursor = Cursors.WaitCursor; //If curAdId is null then insert record if (curAdID == "") { //To fill Dataset and update datasource OleDbDataAdapter printmediaAdapter; //In-memory cache of data DataSet printmediaDataSet; //Data Table contained in Data Set DataTable printmediaDataTable; //Data Row contained in Data Table DataRow printmediaRow; //For automatically generating commands to make changes to database through dataset OleDbCommandBuilder printmediaCmdBldr; //Step 1.// //Query for 'PrintMedia' table given with OleDbAdapter printmediaAdapter = new OleDbDataAdapter("SELECT ad_text, ad_image, " + " product_id, date_of_creation FROM PrintMedia", conn); //Instantiate a Data Set object printmediaDataSet = new DataSet("PrintMedia"); //Step 2.// //AddWithKey sets the Primary Key information to complete the //schema information printmediaAdapter.MissingSchemaAction = MissingSchemaAction.AddWithKey; //Configures the schema to match with Data Source printmediaAdapter.FillSchema(printmediaDataSet, SchemaType.Source, "PrintMedia"); //Step 3.// //In this case 'OleDbCommandBuilder' automatically generates //'SelectCommand' printmediaCmdBldr = new OleDbCommandBuilder(printmediaAdapter); //Step 4.// //Adapter fills the Data Set with 'PrintMedia' data printmediaAdapter.Fill(printmediaDataSet, "PrintMedia"); //Step 5.// //Create a new row in the DataTable contained in the DataSet printmediaRow = printmediaDataSet.Tables["PrintMedia"].NewRow(); //part of Step 7.// //Assigning the value of advertisement text printmediaRow["Ad_text"] = adTextBx.Text; byte[] adImageData; //If image is added if (strImageName != "") { //Step 6.// //providing read access to the file chosen using the 'Browse' button FileStream fs = new FileStream(@strImageName, FileMode.Open, FileAccess.Read); //Create a byte array of file stream length adImageData = new byte[fs.Length]; //Read block of bytes from stream into the byte array fs.Read(adImageData, 0, System.Convert.ToInt32(fs.Length)); //Close the File Stream fs.Close(); // part of Step 7.// //Assigning the byte array containing image data printmediaRow["Ad_image"] = adImageData; } //Step 7.// //Assigning product id value with the 'ValueMember' of product drop down list printmediaRow["product_id"] = intProdID; //Assigning date of creation for advertisement to current date printmediaRow["date_of_creation"] = System.DateTime.Today; //Step 8.// //Adding the 'printmediaRow' to the DataSet printmediaDataSet.Tables["PrintMedia"].Rows.Add(printmediaRow); //Step 9.// //Update the database table 'PrintMedia' with new printmedia rows printmediaAdapter.Update(printmediaDataSet, "PrintMedia"); //On successful Insertion of Ad Image display the image in "exisitingImagePicBx" //and clear the "newImagePicBx" if (strImageName != "") { //Create a bitmap for selected image Bitmap newImage = new Bitmap(strImageName); //Fit the image to the size of picture box existingImagePicBx.SizeMode = PictureBoxSizeMode.StretchImage; //Show the bitmap in picture box existingImagePicBx.Image = (Image)newImage; //Clear contents newImagePicBx.Image = null; } //Reset Values strImageName = ""; strExistText = adTextBx.Text; //Set the wait cursor to default cursor this.Cursor = Cursors.Default; } //If advertisement existing, then update else { //Change the default cursor to 'WaitCursor'(an HourGlass) this.Cursor = Cursors.WaitCursor; //To fill Dataset and update datasource OleDbDataAdapter printmediaAdapter; //In-memory cache of data DataSet printmediaDataSet; //Data Table contained in Data Set DataTable printmediaDataTable; //Data Row contained in Data Table DataRow printmediaRow; //For automatically generating commands to make changes to database through DataSet OleDbCommandBuilder printmediaCmdBldr; //Step 1.// //Query for 'PrintMedia' table given with OleDbAdapter printmediaAdapter = new OleDbDataAdapter("SELECT ad_id, ad_text, ad_image, " + " date_of_creation FROM PrintMedia WHERE ad_id =" + curAdID, conn); //Instantiate a Data Set object printmediaDataSet = new DataSet("PrintMedia"); //Step 2.// //AddWithKey sets the Primary Key information to complete the //schema information printmediaAdapter.MissingSchemaAction = MissingSchemaAction.AddWithKey; //Configures the schema to match with Data Source printmediaAdapter.FillSchema(printmediaDataSet, SchemaType.Source, "PrintMedia"); //Step 3.// //In this case 'OleDbCommandBuilder' automatically generates //'SelectCommand' printmediaCmdBldr = new OleDbCommandBuilder(printmediaAdapter); //Step 4.// //Adapter fills the Data Set with 'Print Media' data printmediaAdapter.Fill(printmediaDataSet, "PrintMedia"); //Step 5.// //Get the current advertisement row for updation printmediaRow = printmediaDataSet.Tables[0].Rows.Find(curAdID); //Start the edit operation on the current row printmediaRow.BeginEdit(); //part of Step 7.// //Assigning the value of advertisement text printmediaRow["Ad_text"] = adTextBx.Text; byte[] adImageData; if (strImageName != "") { //Step 6.// //providing read access to the file chosen using the 'Browse' button FileStream fs = new FileStream(@strImageName, FileMode.Open, FileAccess.Read); //Create a byte array of file stream length adImageData = new byte[fs.Length]; //Read block of bytes from stream into the byte array fs.Read(adImageData, 0, System.Convert.ToInt32(fs.Length)); //Close the File Stream fs.Close(); //part of Step 7.// //Assigning the byte array containing image data printmediaRow["Ad_image"] = adImageData; } //Step 7.// //Assigning date of creation for advertisement to current date printmediaRow["date_of_creation"] = System.DateTime.Today; //Step 8.// //End the editing current row operation printmediaRow.EndEdit(); //Step 9.// //Update the database table 'PrintMedia' with new printmedia rows printmediaAdapter.Update(printmediaDataSet, "PrintMedia"); //On successful Insertion of Ad Image display the image in "exisitingImagePicBx" //and clear the "newImagePicBx" if (strImageName != "") { //Create a bitmap for selected image Bitmap newImage = new Bitmap(strImageName); //Fit the image to the size of picture box existingImagePicBx.SizeMode = PictureBoxSizeMode.StretchImage; //Show the bitmap in picture box existingImagePicBx.Image = (Image)newImage; //Clear contents newImagePicBx.Image = null; } //Reset variables strImageName = ""; strExistText = adTextBx.Text; //Set the wait cursor to default cursor this.Cursor = Cursors.Default; } //Display message on successful data updatation MessageBox.Show("Data saved successfully"); } else { MessageBox.Show("Select image or change text for the advertisement!"); } }catch (Exception ex) { //Display error message System.Windows.Forms.MessageBox.Show(ex.ToString()); } }
private void _toolbar_ButtonClick(object sender, System.Windows.Forms.ToolBarButtonClickEventArgs e) { // change connection string if (e.Button == this._tbConnString) { string conn = _qb.ConnectionString; _qb.ConnectionString = PromptConnectionString(conn, Handle); ResetUI(); return; } // show properties if (e.Button == this._tbProperties) { using (QueryPropertiesForm f = new QueryPropertiesForm()) { f.QueryBuilder = _qb; f.ShowDialog(); _txtSql.Text = _qb.Sql; } return; } // toggle GroupBy switch if (e.Button == this._tbGroupBy) { _qb.GroupBy = this._tbGroupBy.Pushed; _txtSql.Text = _qb.Sql; // show/hide GroupBy column _flex.Cols["GroupBy"].Visible = this._tbGroupBy.Pushed; return; } // clear query if (e.Button == this._tbClearQuery) { // confirm with user if (!ShowMessage("Are you sure you want to clear this query?", MessageBoxIcon.Question)) { return; } // clear it _qb.QueryFields.Clear(); return; } // preview results/check syntax if (e.Button == this._tbViewResults || e.Button == this._tbCheckSql) { // sanity... if (_qb.QueryFields.Count == 0) { return; } // prepare to get data/check sql string conn = _qb.ConnectionString; string sql = _qb.Sql; DataTable dt = new DataTable(); try { // build data adapter OleDbDataAdapter da = new OleDbDataAdapter(sql, conn); // check sql by retrieving schema if (e.Button == this._tbCheckSql) { da.FillSchema(dt, SchemaType.Mapped); ShowMessage("The SQL syntax has been verified against the data source.", MessageBoxIcon.Information); return; } // get data into table da.Fill(dt); } catch (Exception x) { ShowMessage("Failed to retrieve the data:\r\n" + x.Message, MessageBoxIcon.Warning); return; } // show data preview form PreviewForm f = new PreviewForm(); f._flex.DataSource = dt; f.ShowDialog(); } }