public DbCommandBuilder GetCommandBuilder(DbDataAdapter adapter)
{
string dbType = db.GetDataAdapter().GetType().ToString();
Type createType = null;
switch (dbType)
{
case "System.Data.Odbc.OdbcDataAdapter":
createType = new System.Data.Odbc.OdbcCommandBuilder().GetType();
break;
case "System.Data.OleDb.OleDbDataAdapter":
createType = new System.Data.OleDb.OleDbCommandBuilder().GetType();
break;
case "System.Data.SqlClient.SqlDataAdapter":
createType = new System.Data.SqlClient.SqlCommandBuilder().GetType();
break;
/*case "System.Data.SqlServerCe.SqlCeCommand":
* createType = "System.Data.SqlServerCe.SqlCeCommandBuilder";
* break;*/
default:
return(null);
}
if (adapter != null)
{
return((DbCommandBuilder)System.Activator.CreateInstance(createType, adapter));
}
else
{
return((DbCommandBuilder)System.Activator.CreateInstance(createType));
}
}
/**
* Deletes all data for the current data. Should be used in the save process only
*/
public void clearDailyData()
{
try
{
conn.Open();
DataSet joinRecords = new DataSet();
dailyAdapter.Fill(joinRecords, "dailyJoins");
System.Data.OleDb.OleDbCommandBuilder cb = new System.Data.OleDb.OleDbCommandBuilder(dailyAdapter);
int oldJoin = joinRecords.Tables["dailyJoins"].Rows.Count;
for (int i = oldJoin - 1; i >= 0; i--)
{
joinRecords.Tables["dailyJoins"].Rows[i].Delete();
dailyAdapter.Update(joinRecords, "dailyJoins");
}
}
catch (Exception ex)
{
System.Windows.Forms.MessageBox.Show("Failed to Clear Data");
System.Windows.Forms.MessageBox.Show(ex.Message);
}
finally
{
conn.Close();
}
}
private void btnGuardar_Click(object sender, EventArgs e)
{
if (compruebaDatos())
{
MessageBox.Show("todo ok");
//reconectamos con la base de datos
System.Data.OleDb.OleDbCommandBuilder cb;
cb = new System.Data.OleDb.OleDbCommandBuilder(da);
//Creamos un nuevo registro
DataRow dRegistro = dsCuestionario.Tables["Cuestionario"].NewRow();
//asignamos para cada registro el valor de cada textbox
dRegistro["Pregunta"] = txtPregunta.Text;
dRegistro["Respuesta1"] = txtRespuesta1.Text;
dRegistro["Respuesta2"] = txtRespuesta2.Text;
dRegistro["Respuesta3"] = txtRespuesta3.Text;
dRegistro["Respuesta4"] = txtRespuesta4.Text;
dRegistro["Correcta"] = txtRespuestaCorrecta.Text;
//añaidmos los nuevos registro al dataset
dsCuestionario.Tables["Cuestionario"].Rows.Add(dRegistro);
//actualizamos la base de datos
da.Update(dsCuestionario, "Cuestionario");
//actualizamos la lista de registros
totalRegistros++;
}
}
//Boton que elimina preguntas
private void btnEliminarPregunta_Click(object sender, EventArgs e)
{
if (!hayRegistros())
{
MessageBox.Show("No hay registros");
}
else
{
DialogResult deseaEliminar = MessageBox.Show("Esta seguro que desea elimnar el registro",
"Advertencia", MessageBoxButtons.YesNo, MessageBoxIcon.Question);
if (deseaEliminar == DialogResult.Yes)
{
//reconectamos con la base de datos
System.Data.OleDb.OleDbCommandBuilder cb;
cb = new System.Data.OleDb.OleDbCommandBuilder(da);
MessageBox.Show(pos.ToString());
if (pos > 1)
{
//eliminamos el registro en la posicion
dsCuestionario.Tables["Cuestionario"].Rows[pos].Delete();
//actualizamos la base de datos el registro de la tabla
da.Update(dsCuestionario, "Cuestionario");
//actualizamos el total de registros
totalRegistros = dsCuestionario.Tables["Cuestionario"].Rows.Count;
//mostramos una posicion aleatoria
pos = devolverPosicionAleatoria(totalRegistros);
mostrarRegistro(pos);
}
if (pos == 0)
{
//eliminamos el registro en la posicion
dsCuestionario.Tables["Cuestionario"].Rows[pos].Delete();
//actualizamos la base de datos el registro de la tabla
da.Update(dsCuestionario, "Cuestionario");
if (hayRegistros())
{
totalRegistros = dsCuestionario.Tables["Cuestionario"].Rows.Count;
pos = devolverPosicionAleatoria(totalRegistros);
mostrarRegistro(pos);
}
else
{
limpiarLabelPreguntas();
}
}
}
}
}
// ************************************************************************
#endregion
#region Constructor
public clsDataBaseAccessOleDbJet40(ref mdlTratamentoErro.clsTratamentoErro TratadorErro, string strPathDB, string strUser, string strPassword) : base(ref TratadorErro, strUser, strPassword)
{
m_enumConnectionType = ConnectionType.PATH;
m_strPathDB = strPathDB;
m_CommandSelect.Connection = m_Connection;
m_DataAdapter.SelectCommand = m_CommandSelect;
GenerateConnectionString();
m_CommandBuilder = new System.Data.OleDb.OleDbCommandBuilder(m_DataAdapter);
}
public void SqlTableModify()
{
System.Data.DataRow[] foundRows;
String Strtofind;
int Rowindex;
if (textBox1.Text == "" ||
textBox2.Text == "" ||
textBox3.Text == "" ||
textBox4.Text == "" ||
textBox5.Text == "" ||
textBox6.Text == "" ||
textBox7.Text == "" ||
textBox8.Text == "" ||
textBox9.Text == "" ||
textBox10.Text == "" ||
textBox11.Text == "" ||
textBox12.Text == "" ||
textBox13.Text == "" ||
textBox14.Text == "" ||
textBox17.Text == "")
{
System.Windows.Forms.MessageBox.Show("Incomplete Information, Try Again !!");
return;
}
Strtofind = "PartnerID =" + textBox1.Text;
foundRows = ds.Tables["AwarObjSqlTable"].Select(Strtofind);
System.Data.OleDb.OleDbCommandBuilder Cb = new System.Data.OleDb.OleDbCommandBuilder(da);
if (foundRows.Length == 0)
{
System.Windows.Forms.MessageBox.Show("Record Not Found, try again");
}
else
{
Rowindex = ds.Tables["AwarObjSqlTable"].Rows.IndexOf(foundRows[0]);
//ds.Tables["AwarObjSqlTable"].Rows[Rowindex].SetField<Int16>("PartnerID", Convert.ToInt16(textBox1.Text));
//ds.Tables["AwarObjSqlTable"].Rows[Rowindex].SetField<String>("PartnetType", textBox2.Text);
//ds.Tables["AwarObjSqlTable"].Rows[Rowindex].SetField<String>("Name", textBox3.Text);
//ds.Tables["AwarObjSqlTable"].Rows[Rowindex].SetField<String>("Phone", textBox4.Text);
//ds.Tables["AwarObjSqlTable"].Rows[Rowindex].SetField<String>("AlternativePhone", textBox5.Text);
//ds.Tables["AwarObjSqlTable"].Rows[Rowindex].SetField<String>("FaxNumber", textBox6.Text);
//ds.Tables["AwarObjSqlTable"].Rows[Rowindex].SetField<String>("Email", textBox6.Text);
//ds.Tables["AwarObjSqlTable"].Rows[Rowindex].SetField<String>("AlternativeEmail", textBox6.Text);
//ds.Tables["AwarObjSqlTable"].Rows[Rowindex].SetField<String>("SuiteNumber", textBox6.Text);
//ds.Tables["AwarObjSqlTable"].Rows[Rowindex].SetField<String>("StreetNumber", textBox6.Text);
//ds.Tables["AwarObjSqlTable"].Rows[Rowindex].SetField<String>("StreetName", textBox6.Text);
//ds.Tables["AwarObjSqlTable"].Rows[Rowindex].SetField<String>("City", textBox6.Text);
//ds.Tables["AwarObjSqlTable"].Rows[Rowindex].SetField<String>("Province", textBox6.Text);
//ds.Tables["AwarObjSqlTable"].Rows[Rowindex].SetField<String>("PostalCode", textBox6.Text);
//ds.Tables["AwarObjSqlTable"].Rows[Rowindex].SetField<Int16>("WhichDB", Convert.ToInt16(textBox6.Text));
SaveinSqlTable();
System.Windows.Forms.MessageBox.Show("Modifications saved successfully!");
}
}
private void m_axCalendar_DoCreateRPattern(object sender, AxXtremeCalendarControl._DCalendarControlEvents_DoCreateRPatternEvent e)
{
e.bResult = false;
if (!IsOpen())
{
return;
}
try
{
String strSQL = "SELECT * FROM CalendarRecurrencePatterns WHERE RecurrencePatternID = 0 ";
System.Data.OleDb.OleDbDataAdapter oleDbDataAdapter = new System.Data.OleDb.OleDbDataAdapter(strSQL, m_oleDbConnection);
System.Data.OleDb.OleDbCommandBuilder custCB = new System.Data.OleDb.OleDbCommandBuilder(oleDbDataAdapter);
custCB.QuotePrefix = "[";
custCB.QuoteSuffix = "]";
System.Data.DataSet objPatternDSet = new System.Data.DataSet();
oleDbDataAdapter.Fill(objPatternDSet);
//code to modify data in dataset here
System.Data.DataRow objDRow = objPatternDSet.Tables[0].NewRow();
objPatternDSet.Tables[0].Rows.Add(objDRow);
m_DataHelper.PutRPatternToData(e.pPattern, objDRow);
oleDbDataAdapter.Update(objPatternDSet);
//'-- update Auto EventID
System.Data.OleDb.OleDbCommand objCommand = m_oleDbConnection.CreateCommand();
objCommand.CommandText = "SELECT MAX(RecurrencePatternID) AS NewNewPatternID FROM CalendarRecurrencePatterns";
System.Data.OleDb.OleDbDataReader objReader = objCommand.ExecuteReader();
objReader.Read();
e.newPatternID = objReader.GetInt32(0);
objReader.Close();
e.bResult = true;
}
catch (System.Exception ex)
{
System.Diagnostics.Debug.WriteLine("EXCEPTION! providerSQLServer.m_axCalendar_DoCreateRPattern: " + ex.Message);
}
}
private void btnUpdate_Click(object sender, EventArgs e)
{
if (MessageBox.Show("Do you want to Update all the records", "TareEntry", MessageBoxButtons.YesNo) == DialogResult.Yes)
{
DataTable resultData = (DataTable)dgRakeView.DataSource;
string query = string.Format("Select ID,Slno,RakeNo,WagonNo,WagonType,Tare,Gross,dateout,timeout,spd,PCC,[From],[Product],[To],Net,UL,OL,CC from Temp where RakeNo={0} order by val(Slno)", txtRakeNo.Text);
var db = new DBEngine();
btnUpdate.Visible = false;
var dt = new System.Data.DataTable();
System.Data.OleDb.OleDbConnection cn = new System.Data.OleDb.OleDbConnection(db.ConnectionString);
System.Data.OleDb.OleDbCommand cmd = new System.Data.OleDb.OleDbCommand(query, cn);
System.Data.OleDb.OleDbDataAdapter da = new System.Data.OleDb.OleDbDataAdapter(cmd);
System.Data.OleDb.OleDbCommandBuilder ocb = new System.Data.OleDb.OleDbCommandBuilder(da);
//cn.Open();
da.Fill(dt);
ocb.QuoteSuffix = "]";
ocb.QuotePrefix = "[";
// var insertBuilder = new StringBuilder();
for (int i = 0; i < resultData.Rows.Count; i++)
{
//DataRow dr = dt.NewRow();
//dr["Slno"] = dttemp.Rows[i][0];
//dr["RakeNo"] = txtRakeNo.Text;
//dr["WagonType"] = txtWagonType.Text;
dt.Rows[i]["WagonNo"] = resultData.Rows[i][1];
dt.Rows[i]["Tare"] = resultData.Rows[i]["tare"];
dt.Rows[i]["Net"] = resultData.Rows[i]["net"];
//dt.Rows[i]["datein"] = resultData.Rows[i]["datein"];
//dt.Rows[i]["timein"] = resultData.Rows[i]["timein"];
dt.Rows[i]["UL"] = resultData.Rows[i]["UL"];
dt.Rows[i]["OL"] = resultData.Rows[i]["OL"];
dt.Rows[i]["CC"] = resultData.Rows[i]["CC"];
dt.Rows[i]["PCC"] = resultData.Rows[i]["PCC"];
}
da.Update(dt);
MessageBox.Show("Successfully Updated");
dgRakeView.DataSource = null;
txtRakeNo.Clear();
txtSlno.Clear();
}
}
private void deleteAllrecordsFromDB()
{
DataSet oDS;
System.Data.OleDb.OleDbDataAdapter oOrdersDataAdapter;
oDS = new DataSet();
oOrdersDataAdapter = new System.Data.OleDb.OleDbDataAdapter(new System.Data.OleDb.OleDbCommand("SELECT * FROM amitTable", conn));
System.Data.OleDb.OleDbCommandBuilder oOrdersCmdBuilder = new System.Data.OleDb.OleDbCommandBuilder(oOrdersDataAdapter);
oOrdersDataAdapter.FillSchema(oDS, SchemaType.Source);
DataTable pTable = oDS.Tables["Table"];
pTable.TableName = "statisticsTable";
oOrdersDataAdapter.Fill(oDS);
// Insert the Data
// oDS.Tables["statisticsTable"].Rows.Add(oOrderRow);
//foreach (DataRow oOrderRow in oDS.Tables["statisticsTable"].Rows)
//{
// oOrderRow.Delete();
//}
foreach (DataRow oOrderRow in oDS.Tables["Table"].Rows)
{
oOrderRow.Delete();
}
//oOrderRow["FirstName"] = (docNum + 1).ToString();
//oOrderRow["Field1"] = performStatistics(docNum, docNum);
//oDS.Tables["statisticsTable"].Rows.Add(oOrderRow);
//oDS.Tables[0].Rows.Clear();
//oDS.Tables[1].Rows.Clear();
//oOrderRow["FirstName"] = (docNum + 1).ToString();
//oOrderRow["Field1"] = performStatistics(docNum, docNum);
//oDS.Tables["statisticsTable"].Rows.Add(oOrderRow);
oOrdersDataAdapter.Update(oDS, "Table");
//System.Data.OleDb.OleDbDataAdapter dataAdapter = new System.Data.OleDb.OleDbDataAdapter();
//System.Data.OleDb.OleDbCommand command = new System.Data.OleDb.OleDbCommand("SELECT* from amitTable;", conn);
}
private void button1_Click(object sender, EventArgs e)
{
var cn = new System.Data.OleDb.OleDbConnection(
"Provider=Microsoft.ACE.OLEDB.12.0;"
+ "Data Source=C:\\C#2017\\SampleDB.accdb;");
//データセットに取得する
var ad = new System.Data.OleDb.OleDbDataAdapter("SELECT * FROM 社員", cn);
var dt = new DataTable();
ad.Fill(dt);
//コマンドビルダの作成
var builder = new System.Data.OleDb.OleDbCommandBuilder(ad);
//テーブルデータを更新し、データベースに反映する
dt.Rows[0]["氏名"] = "クラリス"; //氏名フィールド変更
ad.Update(dt); //データベースに反映
MessageBox.Show("社員テーブルを更新しました。", "通知");
}
private static void CreateUnTranslateRecord(string UntranlsateString, string lang)
{
System.Data.OleDb.OleDbConnection oleDBConn = new System.Data.OleDb.OleDbConnection();
string DebugFilePath = System.IO.Path.Combine(AppDomain.CurrentDomain.BaseDirectory, "Debug.mdb");
string strConn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + DebugFilePath + ";";
oleDBConn.ConnectionString = strConn;
try
{
oleDBConn.Open();
}
catch
{
oleDBConn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + DebugFilePath + ";";
oleDBConn.Open();
}
System.Data.OleDb.OleDbDataAdapter oleDbAdapter = new System.Data.OleDb.OleDbDataAdapter("Select * from [NotTranslated]"
+ " WHERE " + "TranslationString='" + UntranlsateString.Replace("'", "''") + "' and LanguageString='" + lang + "'"
, oleDBConn);
System.Data.OleDb.OleDbCommandBuilder commandBuilder = new System.Data.OleDb.OleDbCommandBuilder(oleDbAdapter);
System.Data.DataTable table = new DataTable();
oleDbAdapter.Fill(table);
DateTime currentDatetime = AppUtils.ServerDateTime();
//DataRow[] rowList = table.Select();
if (table.Rows.Count <= 0)
{
DataRow row = table.NewRow();
row["TranslationString"] = UntranlsateString;
row["LanguageString"] = lang;
row["CreateDate"] = currentDatetime;
row["LastUpdateDate"] = currentDatetime;
table.Rows.Add(row);
}
else
{
table.Rows[0]["LastUpdateDate"] = currentDatetime;
}
oleDbAdapter.Update(table);
oleDBConn.Close();
}
private void button1_Click(object sender, System.EventArgs e)
{
this.oleDbConnection1.ConnectionString = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + Application.StartupPath + "\\data.mdb;";
//this.oleDbConnection1.Open();
DataSet thisDataSet = new DataSet();
string scom;
scom = this.textBox1.Text;
try
{
//建立适配器
System.Data.OleDb.OleDbDataAdapter studentAdapter = new System.Data.OleDb.OleDbDataAdapter(scom, this.oleDbConnection1);
System.Data.OleDb.OleDbCommandBuilder studentBuilder = new System.Data.OleDb.OleDbCommandBuilder(studentAdapter);
//填充
studentAdapter.Fill(thisDataSet, "table1");
}
catch {}
this.oleDbConnection1.Close();
}
private void m_axCalendar_DoUpdateRPattern(object sender, AxXtremeCalendarControl._DCalendarControlEvents_DoUpdateRPatternEvent e)
{
e.bResult = false;
if (!IsOpen())
{
return;
}
try
{
String strSQL = "SELECT * FROM CalendarRecurrencePatterns WHERE RecurrencePatternID = " + Convert.ToString(e.pPattern.Id);
System.Data.OleDb.OleDbDataAdapter oleDbDataAdapter = new System.Data.OleDb.OleDbDataAdapter(strSQL, m_oleDbConnection);
System.Data.OleDb.OleDbCommandBuilder custCB = new System.Data.OleDb.OleDbCommandBuilder(oleDbDataAdapter);
custCB.QuotePrefix = "[";
custCB.QuoteSuffix = "]";
System.Data.DataSet objPatternDSet = new System.Data.DataSet();
oleDbDataAdapter.Fill(objPatternDSet);
if (objPatternDSet.Tables[0].Rows.Count == 0)
{
return;
}
//code to modify data in dataset here
System.Data.DataRow objDRow = objPatternDSet.Tables[0].Rows[0];
m_DataHelper.PutRPatternToData(e.pPattern, objDRow);
oleDbDataAdapter.Update(objPatternDSet);
e.bResult = true;
}
catch (System.Exception ex)
{
System.Diagnostics.Debug.WriteLine("EXCEPTION! providerSQLServer.m_axCalendar_DoUpdateRPattern: " + ex.Message);
}
}
private void RunStatistics_Click(object sender, EventArgs e)
{
deleteAllrecordsFromDB();
MainForm mainForm = (MainForm)this.parentForm;
if (SessionComparisonRadioButton.Checked)
{
SessionsStatisticsArray = new int[getDocNumber()];
for (int docNum = 0; docNum < getDocNumber(); docNum++)
{
bool dontCountThisSession = false;
foreach (CheckBox checkbox in SessionCheckBoxPanel.Controls)
{
//char [10] aa=new char("Session ");
string aa = "Session ";
string bb = checkbox.Text.TrimStart(aa.ToCharArray());
int sessionNumber = Convert.ToInt16(bb);
if (sessionNumber - 1 == docNum)
{
if (checkbox.Checked == false)
{
dontCountThisSession = true;
}
break;
}
}
if (dontCountThisSession)
{
continue;
}
else
{
//for (int patiantCounter = 0; patiantCounter < mainForm.SessionsArray[0].document[0].numOfPatiants; patiantCounter++)
//{
// bool dontCountThisPatiant = false;
// foreach (CheckBox checkbox in checkBoxPanel.Controls)
// {
// if (mainForm.SessionsArray[numOfSessions].document[docCounter].timeLine[patiantCounter].PatiantName == checkbox.Text && checkbox.Checked == false)
// dontCountThisPatiant = true;
// }
// if (dontCountThisPatiant)
// continue;
//}
DataSet oDS;
System.Data.OleDb.OleDbDataAdapter oOrdersDataAdapter;
SessionsStatisticsArray[docNum] = performStatistics(docNum, docNum, 0, mainForm.SessionsArray[0].document[0].numOfPatiants);
oDS = new DataSet();
oOrdersDataAdapter = new System.Data.OleDb.OleDbDataAdapter(new System.Data.OleDb.OleDbCommand("SELECT * FROM amitTable", conn));
System.Data.OleDb.OleDbCommandBuilder oOrdersCmdBuilder = new System.Data.OleDb.OleDbCommandBuilder(oOrdersDataAdapter);
oOrdersDataAdapter.FillSchema(oDS, SchemaType.Source);
DataTable pTable = oDS.Tables["Table"];
pTable.TableName = "statisticsTable";
// Insert the Data
DataRow oOrderRow = oDS.Tables["statisticsTable"].NewRow();
oOrderRow["FirstName"] = (docNum + 1).ToString();
oOrderRow["Field1"] = performStatistics(docNum, docNum, 0, mainForm.SessionsArray[0].document[0].numOfPatiants);
oDS.Tables["statisticsTable"].Rows.Add(oOrderRow);
//System.Data.OleDb.OleDbDataAdapter dataAdapter = new System.Data.OleDb.OleDbDataAdapter();
//System.Data.OleDb.OleDbCommand command = new System.Data.OleDb.OleDbCommand("SELECT* from amitTable;", conn);
oOrdersDataAdapter.Update(oDS, "statisticsTable");
// conn.Close();
// DataTable table = new DataTable("amit");
// table.Columns.Add("column");
// dataset.Tables.Add(table);
}
// Graphics g = this.TablePanel.CreateGraphics();
// g.
}
/* if (report == null)
* {
* report= new StatisticsReportChart();
* report.parentForm = this;
* report.WindowState = FormWindowState.Maximized;
* }
* report.reportView.RefreshReport();
* report.Show();*/
}
else //not session comparison
{
int fromSession = Convert.ToInt16(this.FromSessionNumericUpDown.Value) - 1;
int toSession = Convert.ToInt16(this.ToSessionNumericUpDown.Value) - 1;
int statisticsCounter = performStatistics(fromSession, toSession, 0, mainForm.SessionsArray[0].document[0].numOfPatiants);
this.CountTextBox.Text = statisticsCounter.ToString();
}
}
public static bool UpdateDataSource(System.Data.DataTable data, System.Collections.Specialized.ListDictionary addressbook, anmar.SharpWebMail.IEmailClient client)
{
bool error = false;
if ( data==null || addressbook==null || !addressbook.Contains("connectionstring")
|| !addressbook.Contains("searchstring") || !addressbook.Contains("allowupdate") || !((bool)addressbook["allowupdate"]) )
return false;
System.String connectstring = addressbook["connectionstring"].ToString();
System.String connectusername = null, connectpassword = null;
if ( addressbook.Contains("connectionusername") && addressbook.Contains("connectionpassword") ) {
connectusername = addressbook["connectionusername"].ToString();
connectpassword = addressbook["connectionpassword"].ToString();
} else if ( client!=null ) {
connectusername = client.UserName;
connectpassword = client.Password;
}
System.String searchfilter = addressbook["searchstring"].ToString();
if ( client!=null )
searchfilter=searchfilter.Replace("$USERNAME$", client.UserName);
else
searchfilter=searchfilter.Replace("$USERNAME$", System.String.Empty);
System.Data.Common.DbDataAdapter adapter = GetDataAdapter(addressbook["type"].ToString(), connectstring, connectusername, connectpassword, searchfilter);
if ( adapter!=null ) {
try {
if ( addressbook["type"].Equals("odbc") ) {
System.Data.Odbc.OdbcCommandBuilder builder = new System.Data.Odbc.OdbcCommandBuilder(adapter as System.Data.Odbc.OdbcDataAdapter);
adapter.Update(data);
builder = null;
} else if ( addressbook["type"].Equals("oledb") ) {
System.Data.OleDb.OleDbCommandBuilder builder = new System.Data.OleDb.OleDbCommandBuilder(adapter as System.Data.OleDb.OleDbDataAdapter);
adapter.Update(data);
builder = null;
}
} catch ( System.Exception e ) {
if ( log.IsErrorEnabled )
log.Error(System.String.Concat("Error updating address book [", addressbook["name"], "] for user [", client.UserName, "]" ), e);
error = true;
}
} else {
error = true;
}
adapter = null;
return !error;
}
private void btnSave_Click(object sender, EventArgs e)
{
if (MessageBox.Show("Do you want to Save all the records", "TareEntry", MessageBoxButtons.YesNo) == DialogResult.Yes)
{
//string insertQuery = string.Empty;
//string updateQuery = string.Empty;
DataTable resultData = (DataTable)dgRakeView.DataSource;
//insertQuery = "insert into TempTare(Slno,RakeNo,WagonNo,WagonType,Tare,Gross,Net,PCC) values({0},'{1}','{2}','{3}','{4}','{5}','{6}','{7}')";
//updateQuery = "update TempGross Set WagonNo='{0}',Tare='{1}',Net='{2}' where Slno={3} and RakeNo={4}";
//foreach (DataRow dr in resultData.Rows)
//{
// string insertBuilder = string.Format(insertQuery, dr[0],txtRakeNo.Text,dr[1],dr[2],dr[3],dr[5],dr[6],dr[4]);
// var db = new DBEngine();
// db.ExecuteNonQuery(insertBuilder);
// insertBuilder = string.Format(updateQuery, dr[1], dr[4], dr[6], dr[0], txtRakeNo.Text);
// db.ExecuteNonQuery(insertBuilder);
//}
string query = string.Format("Select ID,Slno,RakeNo,WagonNo,WagonType,Tare,Gross,dateout,timeout,spd,PCC,[From],[Product],[To],Net,UL,OL,CC from Temp where RakeNo={0} order by val(Slno)", txtRakeNo.Text);
var db = new DBEngine();
btnSave.Visible = false;
var dt = new System.Data.DataTable();
System.Data.OleDb.OleDbConnection cn = new System.Data.OleDb.OleDbConnection(db.ConnectionString);
System.Data.OleDb.OleDbCommand cmd = new System.Data.OleDb.OleDbCommand(query, cn);
System.Data.OleDb.OleDbDataAdapter da = new System.Data.OleDb.OleDbDataAdapter(cmd);
System.Data.OleDb.OleDbCommandBuilder ocb = new System.Data.OleDb.OleDbCommandBuilder(da);
//cn.Open();
da.Fill(dt);
ocb.QuoteSuffix = "]";
ocb.QuotePrefix = "[";
// var insertBuilder = new StringBuilder();
for (int i = 0; i < resultData.Rows.Count; i++)
{
//DataRow dr = dt.NewRow();
//dr["Slno"] = dttemp.Rows[i][0];
//dr["RakeNo"] = txtRakeNo.Text;
//dr["WagonType"] = txtWagonType.Text;
dt.Rows[i]["WagonNo"] = resultData.Rows[i][1];
dt.Rows[i]["Tare"] = resultData.Rows[i]["tare"];
dt.Rows[i]["Net"] = resultData.Rows[i]["net"];
//dt.Rows[i]["datein"] = resultData.Rows[i]["datein"];
//dt.Rows[i]["timein"] = resultData.Rows[i]["timein"];
dt.Rows[i]["UL"] = resultData.Rows[i]["UL"];
dt.Rows[i]["OL"] = resultData.Rows[i]["OL"];
dt.Rows[i]["CC"] = resultData.Rows[i]["CC"];
dt.Rows[i]["PCC"] = resultData.Rows[i]["PCC"];
}
da.Update(dt);
MessageBox.Show("Successfully Updated");
dgRakeView.DataSource = null;
txtRakeNo.Clear();
txtSlno.Clear();
}
}
private void button1_Click(object sender, System.EventArgs e)
{
this.oleDbConnection1.ConnectionString = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + Application.StartupPath + "\\data.mdb;";
this.oleDbConnection1.Open();
DataSet thisDataSet = new DataSet();
string scom;
string addcom = "";
scom = "SELECT * FROM table1";
int wccom = 0;
if (this.textBox1.Text.Length != 0)
{
addcom = " where no1=" + this.textBox1.Text;
wccom++;
}
if (this.textBox2.Text.Length != 0)
{
if (wccom > 0)
{
addcom = addcom + " and no2=" + this.textBox2.Text;
}
else
{
addcom = " where no2=" + this.textBox2.Text; wccom++;
}
}
if (this.textBox3.Text.Length != 0)
{
if (wccom > 0)
{
addcom = addcom + " and no3=" + this.textBox3.Text;
}
else
{
addcom = " where no3=" + this.textBox3.Text; wccom++;
}
}
if (this.textBox4.Text.Length != 0)
{
if (wccom > 0)
{
addcom = addcom + " and no4=" + this.textBox4.Text;
}
else
{
addcom = " where no4=" + this.textBox4.Text; wccom++;
}
}
if (this.textBox5.Text.Length != 0)
{
if (wccom > 0)
{
addcom = addcom + " and no5=" + this.textBox5.Text;
}
else
{
addcom = " where no5=" + this.textBox5.Text; wccom++;
}
}
if (this.textBox6.Text.Length != 0)
{
if (wccom > 0)
{
addcom = addcom + " and no6=" + this.textBox6.Text;
}
else
{
addcom = " where no6=" + this.textBox6.Text; wccom++;
}
}
if (this.textBox7.Text.Length != 0)
{
if (wccom > 0)
{
addcom = addcom + " and no7=" + this.textBox7.Text;
}
else
{
addcom = " where no7=" + this.textBox7.Text; wccom++;
}
}
if (this.textBox8.Text.Length != 0)
{
if (wccom > 0)
{
addcom = addcom + " and date1='" + this.textBox8.Text + "'";
}
else
{
addcom = " where date1='" + this.textBox8.Text + "'"; wccom++;
}
}
scom = scom + addcom;
this.label1.Text = scom;
try
{
//建立适配器
System.Data.OleDb.OleDbDataAdapter studentAdapter = new System.Data.OleDb.OleDbDataAdapter(scom, this.oleDbConnection1);
System.Data.OleDb.OleDbCommandBuilder studentBuilder = new System.Data.OleDb.OleDbCommandBuilder(studentAdapter);
//填充
studentAdapter.Fill(thisDataSet, "table1");
this.dataGrid1.DataSource = thisDataSet.Tables[0];
//1、新建立一个 DataGridTableStyle
DataGridTableStyle dtstyle = new DataGridTableStyle();
//2、绑定数据源
dtstyle.MappingName = thisDataSet.Tables[0].TableName;
//3、设定
this.dataGrid1.TableStyles.Clear();
this.dataGrid1.TableStyles.Add(dtstyle);
this.dataGrid1.TableStyles[thisDataSet.Tables[0].TableName].GridColumnStyles[0].Width = 30;
this.dataGrid1.TableStyles[thisDataSet.Tables[0].TableName].GridColumnStyles[1].Width = 30;
this.dataGrid1.TableStyles[thisDataSet.Tables[0].TableName].GridColumnStyles[2].Width = 30;
this.dataGrid1.TableStyles[thisDataSet.Tables[0].TableName].GridColumnStyles[3].Width = 30;
this.dataGrid1.TableStyles[thisDataSet.Tables[0].TableName].GridColumnStyles[4].Width = 30;
this.dataGrid1.TableStyles[thisDataSet.Tables[0].TableName].GridColumnStyles[5].Width = 30;
this.dataGrid1.TableStyles[thisDataSet.Tables[0].TableName].GridColumnStyles[6].Width = 30;
}
catch {}
this.oleDbConnection1.Close();
}
private void button3_Click(object sender, System.EventArgs e)
{
this.oleDbConnection1.ConnectionString = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + Application.StartupPath + "\\data.mdb;";
this.oleDbConnection1.Open();
DataSet thisDataSet = new DataSet();
string scom;
string addcom = "";
scom = "delete from table1";
int wccom = 0;
if (this.textBox1.Text.Length != 0)
{
addcom = " where no1=" + this.textBox1.Text;
wccom++;
}
if (this.textBox2.Text.Length != 0)
{
if (wccom > 0)
{
addcom = addcom + " and no2=" + this.textBox2.Text;
}
else
{
addcom = " where no2=" + this.textBox2.Text; wccom++;
}
}
if (this.textBox3.Text.Length != 0)
{
if (wccom > 0)
{
addcom = addcom + " and no3=" + this.textBox3.Text;
}
else
{
addcom = " where no3=" + this.textBox3.Text; wccom++;
}
}
if (this.textBox4.Text.Length != 0)
{
if (wccom > 0)
{
addcom = addcom + " and no4=" + this.textBox4.Text;
}
else
{
addcom = " where no4=" + this.textBox4.Text; wccom++;
}
}
if (this.textBox5.Text.Length != 0)
{
if (wccom > 0)
{
addcom = addcom + " and no5=" + this.textBox5.Text;
}
else
{
addcom = " where no5=" + this.textBox5.Text; wccom++;
}
}
if (this.textBox6.Text.Length != 0)
{
if (wccom > 0)
{
addcom = addcom + " and no6=" + this.textBox6.Text;
}
else
{
addcom = " where no6=" + this.textBox6.Text; wccom++;
}
}
if (this.textBox7.Text.Length != 0)
{
if (wccom > 0)
{
addcom = addcom + " and no7=" + this.textBox7.Text;
}
else
{
addcom = " where no7=" + this.textBox7.Text; wccom++;
}
}
if (this.textBox8.Text.Length != 0)
{
if (wccom > 0)
{
addcom = addcom + " and date1='" + this.textBox8.Text + "'";
}
else
{
addcom = " where date1='" + this.textBox8.Text + "'"; wccom++;
}
}
scom = scom + addcom;
this.label1.Text = scom;
try
{
//建立适配器
System.Data.OleDb.OleDbDataAdapter studentAdapter = new System.Data.OleDb.OleDbDataAdapter(scom, this.oleDbConnection1);
System.Data.OleDb.OleDbCommandBuilder studentBuilder = new System.Data.OleDb.OleDbCommandBuilder(studentAdapter);
//填充
studentAdapter.Fill(thisDataSet, "table1");
this.dataGrid1.DataSource = thisDataSet.Tables[0];
this.dataSet1 = thisDataSet;
}
catch { this.label1.Text = scom; }
this.oleDbConnection1.Close();
this.button1_Click(this, e);
}
private void button2_Click(object sender, System.EventArgs e)
{
this.oleDbConnection1.ConnectionString = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + Application.StartupPath + "\\data.mdb;";
//this.oleDbConnection1.Open();
DataSet thisDataSet = new DataSet();
string scom;
string addcom = "";
scom = "SELECT * FROM table1";
int wccom = 0;
string n1 = "", n2 = "", n3 = "", n4 = "", n5 = "", n6 = "", n7 = "";
string qi = "";
if (this.textBox1.Text.Length != 0)
{
n1 = "no1=" + this.textBox1.Text;
addcom = " where no1=" + this.textBox1.Text;
wccom++;
}
if (this.textBox2.Text.Length != 0)
{
if (wccom > 0)
{
n2 = ",no2=" + this.textBox2.Text;
addcom = addcom + " and no2=" + this.textBox2.Text;
}
else
{
n2 = "no2=" + this.textBox2.Text;;
addcom = " where no2=" + this.textBox2.Text; wccom++;
}
}
if (this.textBox3.Text.Length != 0)
{
if (wccom > 0)
{
n3 = ",no3=" + this.textBox3.Text;
addcom = addcom + " and no3=" + this.textBox3.Text;
}
else
{
n3 = "no3=" + this.textBox3.Text;
addcom = " where no3=" + this.textBox3.Text; wccom++;
}
}
if (this.textBox4.Text.Length != 0)
{
if (wccom > 0)
{
n4 = ",no4=" + this.textBox4.Text;
addcom = addcom + " and no4=" + this.textBox4.Text;
}
else
{
n4 = "no4=" + this.textBox4.Text;
addcom = " where no4=" + this.textBox4.Text; wccom++;
}
}
if (this.textBox5.Text.Length != 0)
{
if (wccom > 0)
{
n5 = ",no5=" + this.textBox5.Text;
addcom = addcom + " and no5=" + this.textBox5.Text;
}
else
{
n5 = "no5=" + this.textBox5.Text;
addcom = " where no5=" + this.textBox5.Text; wccom++;
}
}
if (this.textBox6.Text.Length != 0)
{
if (wccom > 0)
{
n6 = ",no6=" + this.textBox6.Text;
addcom = addcom + " and no6=" + this.textBox6.Text;
}
else
{
n6 = "no6=" + this.textBox6.Text;
addcom = " where no6=" + this.textBox6.Text; wccom++;
}
}
if (this.textBox7.Text.Length != 0)
{
if (wccom > 0)
{
n7 = ",no7=" + this.textBox7.Text;
addcom = addcom + " and no7=" + this.textBox7.Text;
}
else
{
n7 = "no7=" + this.textBox7.Text;
addcom = " where no7=" + this.textBox7.Text; wccom++;
}
}
if (this.textBox8.Text.Length != 0)
{
qi = ",date1='" + this.textBox8.Text + "'";
}
scom = "update table1 set " + n1 + n2 + n3 + n4 + n5 + n6 + n7 + ",s='1'" + qi + " ";//from table1";
//string scom1;
scom = scom + addcom;
this.label1.Text = scom;
this.button1_Click(this, e);
this.oleDbConnection1.Open();
if (this.dataSet1.Tables[0].Rows.Count > 0)
{
}
else
{
scom = "insert into table1 (no1,no2,no3,no4,no5,no6,no7,s,date1) values(" + this.textBox1.Text + "," + this.textBox2.Text + "," + this.textBox3.Text + "," + this.textBox4.Text + "," + this.textBox5.Text + "," + this.textBox6.Text + "," + this.textBox7.Text + ",'1','" + this.textBox8.Text + "')";
}
try
{
//建立适配器
System.Data.OleDb.OleDbDataAdapter studentAdapter = new System.Data.OleDb.OleDbDataAdapter(scom, this.oleDbConnection1);
System.Data.OleDb.OleDbCommandBuilder studentBuilder = new System.Data.OleDb.OleDbCommandBuilder(studentAdapter);
//填充
studentAdapter.Fill(thisDataSet, "table1");
this.dataGrid1.DataSource = thisDataSet.Tables[0];
this.label1.Text = scom;
}
catch { this.label1.Text = scom; }
this.oleDbConnection1.Close();
this.button1_Click(this, e);
}
private void ExportToXL(string sheetToCreate, List <DataRow> selectedRows, DataTable origDataTable, string tableName)
{
char Space = ' ';
string dest = sheetToCreate;
if (File.Exists(dest))
{
File.Delete(dest);
}
sheetToCreate = dest;
if (tableName == null)
{
tableName = string.Empty;
}
tableName = tableName.Trim().Replace(Space, '_');
if (tableName.Length == 0)
{
tableName = origDataTable.TableName.Replace(Space, '_');
}
if (tableName.Length == 0)
{
tableName = "NoTableName";
}
if (tableName.Length > 30)
{
tableName = tableName.Substring(0, 30);
}
//Excel names are less than 31 chars
string queryCreateExcelTable = "CREATE TABLE [" + tableName + "] (";
Dictionary <string, string> colNames = new Dictionary <string, string>();
foreach (DataColumn dc in origDataTable.Columns)
{
//Cause the query to name each of the columns to be created.
string modifiedcolName = dc.ColumnName;//.Replace(Space, '_').Replace('.', '#');
string origColName = dc.ColumnName;
colNames.Add(modifiedcolName, origColName);
queryCreateExcelTable += "[" + modifiedcolName + "]" + " text,";
}
queryCreateExcelTable = queryCreateExcelTable.TrimEnd(new char[] { Convert.ToChar(",") }) + ")";
//adds the closing parentheses to the query string
if (selectedRows.Count > 65000 && sheetToCreate.ToLower().EndsWith(".xls"))
{
//use Excel 2007 for large sheets.
sheetToCreate = sheetToCreate.ToLower().Replace(".xls", string.Empty) + ".xlsx";
}
string strCn = string.Empty;
string ext = System.IO.Path.GetExtension(sheetToCreate).ToLower();
if (ext == ".xls")
{
strCn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + sheetToCreate + "; Extended Properties='Excel 8.0;HDR=YES'";
}
if (ext == ".xlsx")
{
strCn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + sheetToCreate + ";Extended Properties='Excel 12.0 Xml;HDR=YES' ";
}
if (ext == ".xlsb")
{
strCn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + sheetToCreate + ";Extended Properties='Excel 12.0;HDR=YES' ";
}
if (ext == ".xlsm")
{
strCn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + sheetToCreate + ";Extended Properties='Excel 12.0 Macro;HDR=YES' ";
}
System.Data.OleDb.OleDbConnection cn = new System.Data.OleDb.OleDbConnection(strCn);
System.Data.OleDb.OleDbCommand cmd = new System.Data.OleDb.OleDbCommand(queryCreateExcelTable, cn);
cn.Open();
cmd.ExecuteNonQuery();
System.Data.OleDb.OleDbDataAdapter da = new System.Data.OleDb.OleDbDataAdapter("SELECT * FROM [" + tableName + "]", cn);
System.Data.OleDb.OleDbCommandBuilder cb = new System.Data.OleDb.OleDbCommandBuilder(da);
//creates the INSERT INTO command
cb.QuotePrefix = "[";
cb.QuoteSuffix = "]";
cmd = cb.GetInsertCommand();
//gets a hold of the INSERT INTO command.
foreach (DataRow row in selectedRows)
{
foreach (System.Data.OleDb.OleDbParameter param in cmd.Parameters)
{
param.Value = row[colNames[param.SourceColumn.Replace('#', '.')]];
}
cmd.ExecuteNonQuery(); //INSERT INTO command.
}
cn.Close();
cn.Dispose();
da.Dispose();
//GC.Collect();
//GC.WaitForPendingFinalizers();
}
public static bool UpdateDataSource(System.Data.DataTable data, System.Collections.Specialized.ListDictionary addressbook, anmar.SharpWebMail.IEmailClient client)
{
bool error = false;
if (data == null || addressbook == null || !addressbook.Contains("connectionstring") ||
!addressbook.Contains("searchstring") || !addressbook.Contains("allowupdate") || !((bool)addressbook["allowupdate"]))
{
return(false);
}
System.String connectstring = addressbook["connectionstring"].ToString();
System.String connectusername = null, connectpassword = null;
if (addressbook.Contains("connectionusername") && addressbook.Contains("connectionpassword"))
{
connectusername = addressbook["connectionusername"].ToString();
connectpassword = addressbook["connectionpassword"].ToString();
}
else if (client != null)
{
connectusername = client.UserName;
connectpassword = client.Password;
}
System.String searchfilter = addressbook["searchstring"].ToString();
if (client != null)
{
searchfilter = searchfilter.Replace("$USERNAME$", client.UserName);
}
else
{
searchfilter = searchfilter.Replace("$USERNAME$", System.String.Empty);
}
System.Data.Common.DbDataAdapter adapter = GetDataAdapter(addressbook["type"].ToString(), connectstring, connectusername, connectpassword, searchfilter);
if (adapter != null)
{
try {
if (addressbook["type"].Equals("odbc"))
{
System.Data.Odbc.OdbcCommandBuilder builder = new System.Data.Odbc.OdbcCommandBuilder(adapter as System.Data.Odbc.OdbcDataAdapter);
adapter.Update(data);
builder = null;
}
else if (addressbook["type"].Equals("oledb"))
{
System.Data.OleDb.OleDbCommandBuilder builder = new System.Data.OleDb.OleDbCommandBuilder(adapter as System.Data.OleDb.OleDbDataAdapter);
adapter.Update(data);
builder = null;
}
} catch (System.Exception e) {
if (log.IsErrorEnabled)
{
log.Error(System.String.Concat("Error updating address book [", addressbook["name"], "] for user [", client.UserName, "]"), e);
}
error = true;
}
}
else
{
error = true;
}
adapter = null;
return(!error);
}
//function adding data to sql table
public void SqlTableAdd()
{
System.Data.DataRow[] foundRows;
String Strtofind;
if (textBox1.Text == "" ||
textBox2.Text == "" ||
textBox3.Text == "" ||
textBox4.Text == "" ||
textBox5.Text == "" ||
textBox6.Text == "" ||
textBox7.Text == "" ||
textBox8.Text == "" ||
textBox9.Text == "" ||
textBox10.Text == "" ||
textBox11.Text == "" ||
textBox12.Text == "" ||
textBox13.Text == "" ||
textBox14.Text == "" ||
textBox17.Text == "")
{
System.Windows.Forms.MessageBox.Show("Incomplete Information, Try Again !!");
return;
}
Strtofind = "PartnerID =" + textBox1.Text;
foundRows = ds.Tables["AwarObjSqlTable"].Select(Strtofind);
if (foundRows.Length == 0)
{
//its a new record, we should be able to add
System.Data.DataRow NewRow = ds.Tables["AwarObjSqlTable"].NewRow();
//Next line is needed so we can update the database
System.Data.OleDb.OleDbCommandBuilder Cb = new System.Data.OleDb.OleDbCommandBuilder(da);
//NewRow.SetField<int>("PartnerID", Convert.ToInt32(textBox1.Text));
//NewRow.SetField<String>("PartnetType", textBox2.Text);
//NewRow.SetField<String>("Name", textBox3.Text);
//NewRow.SetField<String>("Phone", textBox4.Text);
//NewRow.SetField<String>("AlternativePhone", textBox5.Text);
//NewRow.SetField<String>("FaxNumber", textBox6.Text);
//NewRow.SetField<String>("Email", textBox7.Text);
//NewRow.SetField<String>("AlternativeEmail", textBox8.Text);
//NewRow.SetField<String>("SuiteNumber", textBox9.Text);
//NewRow.SetField<String>("StreetNumber", textBox10.Text);
//NewRow.SetField<String>("StreetName", textBox11.Text);
//NewRow.SetField<String>("City", textBox12.Text);
//NewRow.SetField<String>("Province", textBox13.Text);
//NewRow.SetField<String>("PostalCode", textBox14.Text);
SaveinSqlTable();
NewRow.SetField <Int16>("WhichDB", Convert.ToInt16(textBox17.Text));
ds.Tables["AwarObjSqlTable"].Rows.Add(NewRow);
//da.UpdateCommand = Cb.GetUpdateCommand();
da.Update(ds, "AwarObjSqlTable");
//da.AcceptChangesDuringUpdate = true;
//ds.AcceptChanges();
RecordCount = RecordCount + 1;
//Adding a record for starting Credit Balance
//On Transaction
System.Windows.Forms.MessageBox.Show("Record Added Succesfully");
//sending an email
}
else
{
System.Windows.Forms.MessageBox.Show("Duplicate ID, try Again!!!");
return;
}
}
