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