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();
}
}