public void load_list()
{
try
{
string workerName = "admin"; //Session["USER_ID"].ToString();
string connection = ConfigurationSettings.AppSettings["ConnectionString"];
OdbcConnection dbCon = new OdbcConnection(connection);
dbCon.Open();
string sql = "select count(*) from Board";
OdbcCommand cmd = new OdbcCommand(sql, dbCon);
total = int.Parse(cmd.ExecuteScalar().ToString());
cmd.CommandText = "select * from Board order by headnum DESC, depth ASC";
OdbcDataAdapter dbAdap = new OdbcDataAdapter(cmd);
DataSet ds = new DataSet();
dbAdap.Fill(ds);
dbAdap.Update(ds);
GridView1.DataSource = ds;
GridView1.DataBind();
dbCon.Close();
}
catch { }
}
public void GetUpdateCommandDBConcurrencyExceptionTest ()
{
OdbcConnection conn = (OdbcConnection) ConnectionManager.Singleton.Connection;
try {
string selectQuery = "select id, lname from employee where id = 3";
OdbcDataAdapter da = new OdbcDataAdapter (selectQuery, conn);
DataSet ds = new DataSet ();
da.Fill (ds, "IntTest");
Assert.AreEqual (1, ds.Tables.Count);
OdbcCommandBuilder cb = new OdbcCommandBuilder (da);
Assert.IsNotNull (cb);
DataRow [] rows = ds.Tables [0].Select ("id=1");
rows [0] [0] = 6660; // non existent
ds.Tables [0].AcceptChanges (); // moves 6660 to original value
rows [0] [0] = 1; // moves 6660 as search key into db table
try {
da.Update (rows);
Assert.Fail ("#1");
} catch (DBConcurrencyException ex) {
// Concurrency violation: the UpdateCommand
// affected 0 records
Assert.AreEqual (typeof (DBConcurrencyException), ex.GetType (), "#2");
Assert.IsNull (ex.InnerException, "#3");
Assert.IsNotNull (ex.Message, "#4");
Assert.AreSame (rows [0], ex.Row, "#5");
Assert.AreEqual (1, ex.RowCount, "#6");
}
} finally {
ConnectionManager.Singleton.CloseConnection ();
}
}
// Sauvegarde tous les changements effectué dans le dataset
public void SaveDataSet(string tableName, DataSet dataSet)
{
if (dataSet.HasChanges() == false)
return;
switch (connType)
{
case ConnectionType.DATABASE_MSSQL:
{
try
{
var conn = new SqlConnection(connString);
var adapter = new SqlDataAdapter("SELECT * from " + tableName, conn);
var builder = new SqlCommandBuilder(adapter);
adapter.DeleteCommand = builder.GetDeleteCommand();
adapter.UpdateCommand = builder.GetUpdateCommand();
adapter.InsertCommand = builder.GetInsertCommand();
lock (dataSet) // lock dataset to prevent changes to it
{
adapter.ContinueUpdateOnError = true;
DataSet changes = dataSet.GetChanges();
adapter.Update(changes, tableName);
PrintDatasetErrors(changes);
dataSet.AcceptChanges();
}
conn.Close();
}
catch (Exception ex)
{
throw new DatabaseException("Can not save table " + tableName, ex);
}
break;
}
case ConnectionType.DATABASE_ODBC:
{
try
{
var conn = new OdbcConnection(connString);
var adapter = new OdbcDataAdapter("SELECT * from " + tableName, conn);
var builder = new OdbcCommandBuilder(adapter);
adapter.DeleteCommand = builder.GetDeleteCommand();
adapter.UpdateCommand = builder.GetUpdateCommand();
adapter.InsertCommand = builder.GetInsertCommand();
DataSet changes;
lock (dataSet) // lock dataset to prevent changes to it
{
adapter.ContinueUpdateOnError = true;
changes = dataSet.GetChanges();
adapter.Update(changes, tableName);
dataSet.AcceptChanges();
}
PrintDatasetErrors(changes);
conn.Close();
}
catch (Exception ex)
{
throw new DatabaseException("Can not save table ", ex);
}
break;
}
case ConnectionType.DATABASE_MYSQL:
{
return;
}
case ConnectionType.DATABASE_OLEDB:
{
try
{
var conn = new OleDbConnection(connString);
var adapter = new OleDbDataAdapter("SELECT * from " + tableName, conn);
var builder = new OleDbCommandBuilder(adapter);
adapter.DeleteCommand = builder.GetDeleteCommand();
adapter.UpdateCommand = builder.GetUpdateCommand();
adapter.InsertCommand = builder.GetInsertCommand();
DataSet changes;
lock (dataSet) // lock dataset to prevent changes to it
{
adapter.ContinueUpdateOnError = true;
changes = dataSet.GetChanges();
adapter.Update(changes, tableName);
dataSet.AcceptChanges();
}
PrintDatasetErrors(changes);
conn.Close();
}
catch (Exception ex)
{
throw new DatabaseException("Can not save table", ex);
}
break;
}
}
}
/// <summary>
///Updates the Database.
///</summary>
public void UpdateAll()
{
m_daCao = new OdbcDataAdapter();
//Update
//OdbcCommand myUpdateCommand = new OdbcCommand("UPDATE adressen SET fldStudentNr = ?, fldName = ?, fldVorname = ?, fldKlassenNr = ?, fldKlassenBez = ?, fldNotenNr = ?, fldNote = ?, fldFachNr = ?, fldBezeichnung = ? WHERE fldStudentNr = ?", m_cnCao);
#region string build for update commant
StringBuilder sbUpdate = new StringBuilder();
sbUpdate.Append("UPDATE adressen SET REC_ID = ?, ");
#region not in use
//sbUpdate.Append("MATCHCODE = ?, ");
//sbUpdate.Append("KUNDENGRUPPE = ?, ");
//sbUpdate.Append("SPRACH_ID = ?, ");
//sbUpdate.Append("GESCHLECHT = ?, ");
//sbUpdate.Append("KUNNUM1 = ?, ");
//sbUpdate.Append("KUNNUM2 = ?, ");
#endregion
sbUpdate.Append("NAME1 = ?, ");
sbUpdate.Append("PLZ = ?, ");
sbUpdate.Append("ORT = ?, ");
#region not in use
//sbUpdate.Append("LAND = ?, ");
//sbUpdate.Append("NAME2 = ?, ");
//sbUpdate.Append("NAME3 = ?, ");
//sbUpdate.Append("ABTEILUNG = ?, ");
#endregion
sbUpdate.Append("ANREDE = ?, ");
sbUpdate.Append("STRASSE = ?, ");
#region not in use
//sbUpdate.Append("POSTFACH = ?, ");
//sbUpdate.Append("PF_PLZ = ?, ");
//sbUpdate.Append("DEFAULT_LIEFANSCHRIFT_ID = ?, ");
//sbUpdate.Append("GRUPPE = ?, ");
#endregion
sbUpdate.Append("TELE1 = ?, ");
sbUpdate.Append("TELE2 = ?, ");
sbUpdate.Append("FAX = ?, ");
sbUpdate.Append("FUNK = ?, ");
sbUpdate.Append("EMAIL = ?, ");
//sbUpdate.Append("EMAIL2 = ?, ");
sbUpdate.Append("INTERNET = ?, ");
//sbUpdate.Append("DIVERSES = ?, ");
sbUpdate.Append("BRIEFANREDE = ?, ");
#region not in use
//sbUpdate.Append("BLZ = ?, ");
//sbUpdate.Append("KTO = ?, ");
//sbUpdate.Append("BANK = ?, ");
//sbUpdate.Append("IBAN = ?, ");
//sbUpdate.Append("SWIFT = ?, ");
//sbUpdate.Append("KTO_INHABER = ?, ");
//sbUpdate.Append("DEB_NUM = ?, ");
//sbUpdate.Append("KRD_NUM = ?, ");
//sbUpdate.Append("STATUS = ?, ");
//sbUpdate.Append("NET_SKONTO = ?, ");
//sbUpdate.Append("NET_TAGE = ?, ");
#endregion
sbUpdate.Append("BRT_TAGE = ?, ");
#region not in use
//sbUpdate.Append("WAEHRUNG = ?, ");
//sbUpdate.Append("UST_NUM = ?, ");
//sbUpdate.Append("VERTRETER_ID = ?, ");
//sbUpdate.Append("PROVIS_PROZ = ?, ");
#endregion
sbUpdate.Append("INFO = ?, ");
//sbUpdate.Append("GRABATT = ?, ");
//sbUpdate.Append("KUN_KRDLIMIT = ?, ");
sbUpdate.Append("KUN_LIEFART = ?, ");
sbUpdate.Append("KUN_ZAHLART = ?, ");
#region not in use
//sbUpdate.Append("KUN_PRLISTE = ?, ");
//sbUpdate.Append("KUN_LIEFSPERRE = ?, ");
//sbUpdate.Append("LIEF_LIEFART = ?, ");
//sbUpdate.Append("LIEF_ZAHLART = ?, ");
//sbUpdate.Append("LIEF_PRLISTE = ?, ");
//sbUpdate.Append("LIEF_TKOSTEN = ?, ");
//sbUpdate.Append("LIEF_MBWERT = ?, ");
//sbUpdate.Append("PR_EBENE = ?, ");
//sbUpdate.Append("BRUTTO_FLAG = ?, ");
//sbUpdate.Append("MWST_FREI_FLAG = ?, ");
//sbUpdate.Append("KUNPREIS_AUTO = ?, ");
#endregion
sbUpdate.Append("KUN_SEIT = ?, ");
sbUpdate.Append("KUN_GEBDATUM = ?");
#region not in use
//sbUpdate.Append("ENTFERNUNG = ?, ");
//sbUpdate.Append("ERSTELLT = ?, ");
//sbUpdate.Append("ERST_NAME = ?, ");
//sbUpdate.Append("GEAEND = ?, ");
//sbUpdate.Append("GEAEND_NAME = ?, ");
//sbUpdate.Append("SHOP_KUNDE = ?, ");
//sbUpdate.Append("SHOP_ID = ?, ");
//sbUpdate.Append("SHOP_NEWSLETTER = ?, ");
//sbUpdate.Append("SHOP_KUNDE_ID = ?, ");
//sbUpdate.Append("SHOP_CHANGE_FLAG = ?, ");
//sbUpdate.Append("SHOP_DEL_FLAG = ?, ");
//sbUpdate.Append("SHOP_PASSWORD = ?, ");
//sbUpdate.Append("USERFELD_01 = ?, ");
//sbUpdate.Append("USERFELD_02 = ?, ");
//sbUpdate.Append("USERFELD_03 = ?, ");
//sbUpdate.Append("USERFELD_04 = ?, ");
//sbUpdate.Append("USERFELD_05 = ?, ");
//sbUpdate.Append("USERFELD_06 = ?, ");
//sbUpdate.Append("USERFELD_07 = ?, ");
//sbUpdate.Append("USERFELD_08 = ?, ");
//sbUpdate.Append("USERFELD_09 = ?, ");
//sbUpdate.Append("USERFELD_10 = ?");
#endregion
sbUpdate.Append(" WHERE REC_ID = ?");
#endregion
OdbcCommand myUpdateCommand = new OdbcCommand(sbUpdate.ToString(), m_cnCao);
#region parameters
myUpdateCommand.Parameters.Add("@REC_ID", OdbcType.Int, 11, "@REC_ID");
#region not in use
//myUpdateCommand.Parameters.Add("@MATCHCODE", OdbcType.VarChar, 255, "@MATCHCODE");
//myUpdateCommand.Parameters.Add("@KUNDENGRUPPE", OdbcType.Int, 11, "@KUNDENGRUPPE");
//myUpdateCommand.Parameters.Add("@SPRACH_ID", OdbcType.Int, 11, "@SPRACH_ID");
//myUpdateCommand.Parameters.Add("@GESCHLECHT", OdbcType.Char, 1, "@GESCHLECHT");
//myUpdateCommand.Parameters.Add("@KUNNUM1", OdbcType.VarChar, 20, "@KUNNUM1");
//myUpdateCommand.Parameters.Add("@KUNNUM2", OdbcType.VarChar, 20, "@KUNNUM2");
#endregion
myUpdateCommand.Parameters.Add("@NAME1", OdbcType.VarChar, 40, "@NAME1");
myUpdateCommand.Parameters.Add("@PLZ", OdbcType.VarChar, 10, "@PLZ");
myUpdateCommand.Parameters.Add("@ORT", OdbcType.VarChar, 40, "@ORT");
#region not in use
//myUpdateCommand.Parameters.Add("@LAND", OdbcType.VarChar, 5, "@LAND");
//myUpdateCommand.Parameters.Add("@NAME2", OdbcType.VarChar, 40, "@NAME2");
//myUpdateCommand.Parameters.Add("@NAME3", OdbcType.VarChar, 40, "@NAME3");
//myUpdateCommand.Parameters.Add("@ABTEILUNG", OdbcType.VarChar, 40, "@ABTEILUNG");
#endregion
myUpdateCommand.Parameters.Add("@ANREDE", OdbcType.VarChar, 40, "@ANREDE");
myUpdateCommand.Parameters.Add("@STRASSE", OdbcType.VarChar, 40, "@STRASSE");
#region not in use
//myUpdateCommand.Parameters.Add("@POSTFACH", OdbcType.VarChar, 40, "@POSTFACH");
//myUpdateCommand.Parameters.Add("@PF_PLZ", OdbcType.VarChar, 10, "@PF_PLZ");
//myUpdateCommand.Parameters.Add("@DEFAULT_LIEFANSCHRIFT_ID", OdbcType.Int, 11, "@DEFAULT_LIEFANSCHRIFT_ID");
//myUpdateCommand.Parameters.Add("@GRUPPE", OdbcType.VarChar, 4, "@GRUPPE");
#endregion
myUpdateCommand.Parameters.Add("@TELE1", OdbcType.VarChar, 100, "@TELE1");
myUpdateCommand.Parameters.Add("@TELE2", OdbcType.VarChar, 100, "@TELE2");
myUpdateCommand.Parameters.Add("@FAX", OdbcType.VarChar, 100, "@FAX");
myUpdateCommand.Parameters.Add("@FUNK", OdbcType.VarChar, 100, "@FUNK");
myUpdateCommand.Parameters.Add("@EMAIL", OdbcType.VarChar, 100, "@EMAIL");
//myUpdateCommand.Parameters.Add("@EMAIL2", OdbcType.VarChar, 100, "@EMAIL2");
myUpdateCommand.Parameters.Add("@INTERNET", OdbcType.VarChar, 100, "@INTERNET");
//myUpdateCommand.Parameters.Add("@DIVERSES", OdbcType.VarChar, 100, "@DIVERSES");
myUpdateCommand.Parameters.Add("@BRIEFANREDE", OdbcType.VarChar, 100, "@BRIEFANREDE");
#region not in use
//myUpdateCommand.Parameters.Add("@BLZ", OdbcType.VarChar, 20, "@BLZ");
//myUpdateCommand.Parameters.Add("@KTO", OdbcType.VarChar, 20, "@KTO");
//myUpdateCommand.Parameters.Add("@BANK", OdbcType.VarChar, 40, "@BANK");
//myUpdateCommand.Parameters.Add("@IBAN", OdbcType.VarChar, 100, "@IBAN");
//myUpdateCommand.Parameters.Add("@SWIFT", OdbcType.VarChar, 100, "@SWIFT");
//myUpdateCommand.Parameters.Add("@KTO_INHABER", OdbcType.VarChar, 40, "@KTO_INHABER");
//myUpdateCommand.Parameters.Add("@DEB_NUM", OdbcType.Int, 11, "@DEB_NUM");
//myUpdateCommand.Parameters.Add("@KRD_NUM", OdbcType.Int, 11, "@KRD_NUM");
//myUpdateCommand.Parameters.Add("@STATUS", OdbcType.Int, 11, "@STATUS");
////myUpdateCommand.Parameters.Add("@NET_SKONTO", OdbcType.Decimal, 5, "@NET_SKONTO");
//OdbcParameter myParam1 = new OdbcParameter();
//myParam1.ParameterName = "@NET_SKONTO";
//myParam1.SourceColumn = "@NET_SKONTO";
//myParam1.DbType = DbType.Decimal;
//myParam1.Precision = 5;
//myParam1.Scale = 2;
//myUpdateCommand.Parameters.Add(myParam1);
//myUpdateCommand.Parameters.Add("@NET_TAGE", OdbcType.TinyInt, 4, "@NET_TAGE");
#endregion
myUpdateCommand.Parameters.Add("@BRT_TAGE", OdbcType.TinyInt, 4, "@BRT_TAGE");
#region not in use
//myUpdateCommand.Parameters.Add("@WAEHRUNG", OdbcType.VarChar, 5, "@WAEHRUNG");
//myUpdateCommand.Parameters.Add("@UST_NUM", OdbcType.VarChar, 25, "@UST_NUM");
//myUpdateCommand.Parameters.Add("@VERTRETER_ID", OdbcType.Int, 11, "@VERTRETER_ID");
////myUpdateCommand.Parameters.Add("@PROVIS_PROZ", OdbcType.Decimal, 5,, "@PROVIS_PROZ");
//OdbcParameter myParam2 = new OdbcParameter();
//myParam2.ParameterName = "@PROVIS_PROZ";
//myParam2.SourceColumn = "@PROVIS_PROZ";
//myParam2.DbType = DbType.Decimal;
//myParam2.Precision = 5;
//myParam2.Scale = 2;
//myUpdateCommand.Parameters.Add(myParam2);
#endregion
myUpdateCommand.Parameters.Add("@INFO", OdbcType.Text, 65537, "@INFO");
#region not in use
////myUpdateCommand.Parameters.Add("@GRABATT", OdbcType.Decimal, 5, "@GRABATT");
//OdbcParameter myParam3 = new OdbcParameter();
//myParam3.ParameterName = "@GRABATT";
//myParam3.SourceColumn = "@GRABATT";
//myParam3.DbType = DbType.Decimal;
//myParam3.Precision = 5;
//myParam3.Scale = 2;
//myUpdateCommand.Parameters.Add(myParam3);
////myUpdateCommand.Parameters.Add("@KUN_KRDLIMIT", OdbcType.Decimal, 10, "@KUN_KRDLIMIT");
//OdbcParameter myParam4 = new OdbcParameter();
//myParam4.ParameterName = "@KUN_KRDLIMIT";
//myParam4.SourceColumn = "@KUN_KRDLIMIT";
//myParam4.DbType = DbType.Decimal;
//myParam4.Precision = 10;
//myParam4.Scale = 2;
//myUpdateCommand.Parameters.Add(myParam4);
#endregion
myUpdateCommand.Parameters.Add("@KUN_LIEFART", OdbcType.Int, 11, "@KUN_LIEFART");
myUpdateCommand.Parameters.Add("@KUN_ZAHLART", OdbcType.Int, 11, "@KUN_ZAHLART");
#region not in use
////myUpdateCommand.Parameters.Add("@KUN_PRLISTE", OdbcType.enum('N','Y'), "@KUN_PRLISTE");
////myUpdateCommand.Parameters.Add("@KUN_LIEFSPERRE", OdbcType.enum('N','Y'), , "@KUN_LIEFFSPERRE");
//myUpdateCommand.Parameters.Add("@LIEF_LIEFART", OdbcType.Int, 11, "@LIEF_LIEFART");
//myUpdateCommand.Parameters.Add("@LIEF_ZAHLART", OdbcType.Int, 11, "@LIEF_ZAHLART");
////myUpdateCommand.Parameters.Add("@LIEF_PRLISTE", OdbcType.enum('N','Y'), , "@LIEF_PRLISTE");
#endregion
#region not in use
////myUpdateCommand.Parameters.Add("@LIEF_TKOSTEN", OdbcType.Decimal, 10, "@LIEF_TKOSTEN");
//OdbcParameter myParam5 = new OdbcParameter();
//myParam5.ParameterName = "@LIEF_TKOSTEN";
//myParam5.SourceColumn = "@LIEF_TKOSTEN";
//myParam5.DbType = DbType.Decimal;
//myParam5.Precision = 10;
//myParam5.Scale = 2;
//myUpdateCommand.Parameters.Add(myParam5);
////myUpdateCommand.Parameters.Add("@LIEF_MBWERT", OdbcType.Decimal, 10, "@LIEF_MBWERT");
//OdbcParameter myParam6 = new OdbcParameter();
//myParam6.ParameterName = "@LIEF_MBWERT";
//myParam6.SourceColumn = "@LIEF_MBWERT";
//myParam6.DbType = DbType.Decimal;
//myParam6.Precision = 10;
//myParam6.Scale = 2;
//myUpdateCommand.Parameters.Add(myParam6);
//myUpdateCommand.Parameters.Add("@PR_EBENE", OdbcType.TinyInt, 1, "@PR_EBENE");
////myUpdateCommand.Parameters.Add("@BRUTTO_FLAG", OdbcType.enum('N','Y'), , "@BRUTTO_FLAG");
////myUpdateCommand.Parameters.Add("@MWST_FREI_FLAG", OdbcType.enum('N','Y') , , "@MWST_FREI_FLAG");
////myUpdateCommand.Parameters.Add("@KUNPREIS_AUTO", OdbcType.enum('N','Y') , , "@KUNPREIS_AUTO");
#endregion
//myUpdateCommand.Parameters.Add("@KUN_SEIT", OdbcType.Date, , "@KUN_SEIT");
OdbcParameter myParam7 = new OdbcParameter();
myParam7.ParameterName = "@KUN_SEIT";
myParam7.SourceColumn = "@KUN_SEIT";
myParam7.DbType = DbType.Date;
myUpdateCommand.Parameters.Add(myParam7);
//myUpdateCommand.Parameters.Add("@KUN_GEBDATUM", OdbcType.Date, , "@KUN_GEBDATUM");
OdbcParameter myParam8 = new OdbcParameter();
myParam8.ParameterName = "@KUN_GEBDATUM";
myParam8.SourceColumn = "@KUN_GEBDATUM";
myParam8.DbType = DbType.Date;
myUpdateCommand.Parameters.Add(myParam8);
#region not in use
////myUpdateCommand.Parameters.Add("@ERSTELLT", OdbcType.Date, , "@ERSTELLT");
//OdbcParameter myParam9 = new OdbcParameter();
//myParam9.ParameterName = "@ERSTELLT";
//myParam9.SourceColumn = "@ERSTELLT";
//myParam9.DbType = DbType.Date;
//myUpdateCommand.Parameters.Add(myParam9);
//myUpdateCommand.Parameters.Add("@ERST_NAME", OdbcType.VarChar, 20, "@ERST_NAME");
////myUpdateCommand.Parameters.Add("@GEAEND", OdbcType.Date, , "@GEAND");
////myUpdateCommand.Parameters.Add("@GEAEND_NAME", OdbcType.VarChar, 20, "@GEAEND_NAME");
////myUpdateCommand.Parameters.Add("@SHOP_KUNDE", enum('N','Y'), , "@SHOP_KUNDE");
//myUpdateCommand.Parameters.Add("@SHOP_ID", OdbcType.TinyInt, 3, "@SHOP_ID");
////myUpdateCommand.Parameters.Add("@SHOP_NEWSLETTER", enum('N','Y'), , "@SHOP_NEWSLETTER");
//myUpdateCommand.Parameters.Add("@SHOP_KUNDE_ID", OdbcType.Int, 11, "@SHOP_KUNDE_ID");
//myUpdateCommand.Parameters.Add("@SHOP_CHANGE_FLAG", OdbcType.TinyInt, 1, "@SHOP_CHANGE_FLAG");
////myUpdateCommand.Parameters.Add("@SHOP_DEL_FLAG", enum('N','Y'), , "@SHOP_DEL_FLAG");
//myUpdateCommand.Parameters.Add("@SHOP_PASSWORD", OdbcType.VarChar, 20, "@SHOP_PASSWORD");
//myUpdateCommand.Parameters.Add("@USERFELD_01", OdbcType.VarChar, 255, "@USERFELD_01");
//myUpdateCommand.Parameters.Add("@USERFELD_02", OdbcType.VarChar, 255, "@USERFELD_02");
//myUpdateCommand.Parameters.Add("@USERFELD_03", OdbcType.VarChar, 255, "@USERFELD_03");
//myUpdateCommand.Parameters.Add("@USERFELD_04", OdbcType.VarChar, 255, "@USERFELD_04");
//myUpdateCommand.Parameters.Add("@USERFELD_05", OdbcType.VarChar, 255, "@USERFELD_05");
//myUpdateCommand.Parameters.Add("@USERFELD_06", OdbcType.VarChar, 255, "@USERFELD_06");
//myUpdateCommand.Parameters.Add("@USERFELD_07", OdbcType.VarChar, 255, "@USERFELD_07");
//myUpdateCommand.Parameters.Add("@USERFELD_08", OdbcType.VarChar, 255, "@USERFELD_08");
//myUpdateCommand.Parameters.Add("@USERFELD_09", OdbcType.VarChar, 255, "@USERFELD_09");
//myUpdateCommand.Parameters.Add("@USERFELD_10", OdbcType.VarChar, 255, "@USERFELD_10");
#endregion
#endregion
m_daCao.UpdateCommand = myUpdateCommand;
#region example
/*
myUpDateCommand.Parameters.Add("@fldStudentNr", OleDbType.Integer, 2, "fldStudentNr");
myUpdateCommand.Parameters.Add("@fldName", OleDbType.VarChar, 255, "fldName");
myUpdateCommand.Parameters.Add("@fldVorname", OleDbType.VarChar, 255, "fldVorname");
myUpdateCommand.Parameters.Add("@fldKlassenNr", OleDbType.Integer, 2, "fldKlassenNr");
myUpdateCommand.Parameters.Add("@fldKlassenBez", OleDbType.VarChar, 255, "fldKlassenBez");
myUpdateCommand.Parameters.Add("@fldNotenNr", OleDbType.Integer, 2, "fldNotenNr");
myUpdateCommand.Parameters.Add("@fldNote", OleDbType.Single, 2, "fldNote");
myUpdateCommand.Parameters.Add("@fldFachNr", OleDbType.Integer, 2, "fldFachNr");
myUpdateCommand.Parameters.Add("@fldBezeichnung", OleDbType.VarChar, 255, "fldBezeichnung");
m_daCao.UpdateCommand = myUpdateCommand;
*/
#endregion
//delete
//OdbcCommand myDeleteCommand = new OdbcCommand("delete from ADRESSEN_LIEF Where ADDR_ID=3;delete from ADRESSEN_TO_MERK Where ADDR_ID=3;DELETE FROM ADRESSEN WHERE REC_ID=3.DeleteCommand = myDeleteCommand;", myConnection);
//myDeleteCommand.Parameters.Add("@fldStudentNr", OleDbType.Integer, 2, "fldStudentNr");
m_daCao.Update(m_dsCao, m_const_strCaoTableSearchAllPrivate);
}
protected void ImageButton1_Click(object sender, ImageClickEventArgs e)
{
//search
try
{
string connection = ConfigurationSettings.AppSettings["ConnectionString"];
OdbcConnection dbCon = new OdbcConnection(connection);
dbCon.Open();
string sql = "select count(*) from Board";
OdbcCommand cmd = new OdbcCommand(sql, dbCon);
total = int.Parse(cmd.ExecuteScalar().ToString());
//search func
if (chkName.Checked == true)
{
//sql = "select name from bygem_board_"+boardname+ " Like '%" +txtSearch.Text + "%'";
sql = " name like '%" + txtSearch.Text + "%'";
}
if (chkSubject.Checked == true)
{
sql = " subject like '%" + txtSearch.Text + "%'";
}
if (chkMemo.Checked == true)
{
sql = " memo like '%" + txtSearch.Text + "%'";
}
//cmd.CommandText = sql+ " order by headnum DESC,depth";
cmd.CommandText = "select * from Board where" + sql + "order by seq DESC,depth";
//"select * from bygem_board_free where name like '%ca%' order by headnum DESC,depth";
//"select * from bygem_board_"+boardname+" where" +sql+ "order by headnum DESC,depth";
OdbcDataAdapter dbAdap = new OdbcDataAdapter(cmd);
DataSet ds = new DataSet();
dbAdap.Fill(ds);
dbAdap.Update(ds);
GridView1.DataSource = ds;
GridView1.DataBind();
dbCon.Close();
}
catch
{
string script;
script = "<script>alert('search failed.')</script>";
this.RegisterStartupScript("error", script);
}
}
public void SaveDataSetThroughAdapter(System.Data.DataSet dsSetRef,
Boolean blnRequiredTransaction, String ExcludeTableName, String strConName)
{
Boolean blnBeginTrans = false;
OleDbDataAdapter objOleDBAdpater;
OdbcDataAdapter objOdbcDBAdpater;
SqlDataAdapter objSqlDBAdpater;
OdbcCommandBuilder objOdbcDBCommandBuilder;
OleDbCommandBuilder objOleDBCommandBuilder;
SqlCommandBuilder objSqlDBCommandBuilder;
IDbCommand IMainCommand;
DataTable dtInsert;
DataTable dtUpdate;
DataTable dtDelete;
Boolean TableExist;
String strQuery;
try
{
if (dsSetRef == null)
{
throw new Exception("DataSet not Initialized");
}
String[] TableName;
char[] delimeter;
String seperator;
seperator = ",";
delimeter = seperator.ToCharArray();
TableName = ExcludeTableName.Split(delimeter);
if (blnRequiredTransaction.IsFalse())
{
if (strConName.Length > 0)
{
OpenConnection(strConName);
}
else
{
OpenConnection(String.Empty);
}
}
if (disconnection.IsNotNull())
{
if (blnRequiredTransaction.IsFalse())
{
transaction = disconnection.BeginTransaction(IsolationLevel.ReadUncommitted);
blnBeginTrans = true;
}
else
{
if (transaction == null)
{
throw new Exception("Transaction is not initialized");
}
else
{
blnBeginTrans = true;
}
}
if (ProviderType == Util.ConnectionLibrary.SQlClient)
{
IMainCommand = new SqlCommand();
}
else if (ProviderType == Util.ConnectionLibrary.Oledb)
{
IMainCommand = new OleDbCommand();
}
else if (ProviderType == Util.ConnectionLibrary.ODBC)
{
IMainCommand = new OdbcCommand();
}
else
{
IMainCommand = null;
}
IMainCommand.Connection = disconnection;
IMainCommand.Transaction = transaction;
}
else
{
throw new Exception("Connection is not initialized");
}
IMainCommand.CommandTimeout = CommandTimeOutValue;
foreach (DataTable dtRef in dsSetRef.Tables)
{
TableExist = false;
foreach (String tablename in TableName)
{
if (dtRef.TableName.ToUpper() == tablename.ToUpper())
{
TableExist = true;
break;
}
}
if (TableExist) continue;
if ((Boolean)dtRef.ExtendedProperties[JoinedQuery])
{
strQuery = dtRef.ExtendedProperties[UpdateQuery].ToString();
}
else
{
strQuery = dtRef.ExtendedProperties[Query].ToString();
}
if ((strQuery.Trim()).Length == 0)
{
throw new Exception("Query is blank");
}
IMainCommand.CommandText = strQuery;
dtInsert = dtRef.GetChanges(DataRowState.Added);
dtUpdate = dtRef.GetChanges(DataRowState.Modified);
dtDelete = dtRef.GetChanges(DataRowState.Deleted);
if (ProviderType == Util.ConnectionLibrary.SQlClient)
{
objSqlDBAdpater = new SqlDataAdapter((SqlCommand)IMainCommand);
objSqlDBCommandBuilder = new SqlCommandBuilder(objSqlDBAdpater);
if (dtDelete.IsNotNull())
{
objSqlDBCommandBuilder.GetDeleteCommand();
objSqlDBAdpater.Update(dtDelete);
dtDelete.Dispose();
dtDelete = null;
}
if (dtInsert.IsNotNull())
{
objSqlDBCommandBuilder.GetInsertCommand();
objSqlDBAdpater.Update(dtInsert);
dtInsert.Dispose();
dtInsert = null;
}
if (dtUpdate.IsNotNull())
{
objSqlDBCommandBuilder.GetUpdateCommand();
objSqlDBAdpater.Update(dtUpdate);
dtUpdate.Dispose();
dtUpdate = null;
}
}
else if (ProviderType == Util.ConnectionLibrary.Oledb)
{
objOleDBAdpater = new OleDbDataAdapter((OleDbCommand)IMainCommand);
objOleDBCommandBuilder = new OleDbCommandBuilder(objOleDBAdpater);
if (dtInsert.IsNotNull())
{
objOleDBCommandBuilder.GetInsertCommand();
objOleDBAdpater.Update(dtInsert);
dtInsert.Dispose();
dtInsert = null;
}
if (dtUpdate.IsNotNull())
{
objOleDBCommandBuilder.GetUpdateCommand();
objOleDBAdpater.Update(dtUpdate);
dtUpdate.Dispose();
dtUpdate = null;
}
if (dtDelete.IsNotNull())
{
objOleDBCommandBuilder.GetDeleteCommand();
objOleDBAdpater.Update(dtDelete);
dtDelete.Dispose();
dtDelete = null;
}
}
else if (ProviderType == Util.ConnectionLibrary.ODBC)
{
objOdbcDBAdpater = new OdbcDataAdapter((OdbcCommand)IMainCommand);
objOdbcDBCommandBuilder = new OdbcCommandBuilder(objOdbcDBAdpater);
if (dtInsert.IsNotNull())
{
objOdbcDBCommandBuilder.GetInsertCommand();
objOdbcDBAdpater.Update(dtInsert);
dtInsert.Dispose();
dtInsert = null;
}
if (dtUpdate.IsNotNull())
{
objOdbcDBCommandBuilder.GetUpdateCommand();
objOdbcDBAdpater.Update(dtUpdate);
dtUpdate.Dispose();
dtUpdate = null;
}
if (dtDelete.IsNotNull())
{
objOdbcDBCommandBuilder.GetDeleteCommand();
objOdbcDBAdpater.Update(dtDelete);
dtDelete.Dispose();
dtDelete = null;
}
}
else
{
objSqlDBAdpater = null;
objOleDBAdpater = null;
objOdbcDBAdpater = null;
objSqlDBCommandBuilder = null;
objOleDBCommandBuilder = null;
objOdbcDBCommandBuilder = null;
}
}
if (blnRequiredTransaction.IsFalse())
{
if (blnBeginTrans)
{
transaction.Commit();
blnBeginTrans = false;
}
disconnection.Close();
disconnection.Dispose();
disconnection = null;
}
}
catch (System.Data.OleDb.OleDbException exOleDb)
{
if (blnBeginTrans && blnRequiredTransaction.IsFalse())
{
transaction.Rollback();
if (disconnection.IsNotNull())
{
if (disconnection.State == System.Data.ConnectionState.Open)
{
disconnection.Close();
}
disconnection.Dispose();
disconnection = null;
}
}
throw (exOleDb);
}
catch (System.Data.DBConcurrencyException exDBCE)
{
if (blnBeginTrans && blnRequiredTransaction.IsFalse())
{
transaction.Rollback();
if (disconnection.IsNotNull())
{
if (disconnection.State == System.Data.ConnectionState.Open)
{
disconnection.Close();
}
disconnection.Dispose();
disconnection = null;
}
}
throw (exDBCE);
}
catch (System.Exception ex)
{
if (blnBeginTrans && blnRequiredTransaction.IsFalse())
{
transaction.Rollback();
if (disconnection.IsNotNull())
{
if (disconnection.State == System.Data.ConnectionState.Open)
{
disconnection.Close();
}
disconnection.Dispose();
disconnection = null;
}
}
throw (ex);
}
finally
{
if (ProviderType == Util.ConnectionLibrary.SQlClient)
{
IMainCommand = null;
objSqlDBAdpater = null;
objSqlDBCommandBuilder = null;
}
else if (ProviderType == Util.ConnectionLibrary.Oledb)
{
IMainCommand = null;
objOleDBAdpater = null;
objOleDBCommandBuilder = null;
}
else if (ProviderType == Util.ConnectionLibrary.ODBC)
{
IMainCommand = null;
objOdbcDBAdpater = null;
objOdbcDBCommandBuilder = null;
}
}
}
/// <summary>
/// 插入数据通过Datatable
/// </summary>
/// <param name="_dt"></param>
/// <returns>影响记录条数</returns>
public override int DataTableInsert(DataTable _dt)
{
bool flag = false;
int _nResult = 0;
if (_dt == null)
return _nResult;
string _sCmdText = string.Format("select * from {0} where 1=2", _dt.TableName);
OdbcCommand _Command = (OdbcCommand)CreateCommand(_sCmdText, CommandType.Text);
OdbcDataAdapter _adapter = new OdbcDataAdapter(_Command);
OdbcDataAdapter _adapter1 = new OdbcDataAdapter(_Command);
OdbcCommandBuilder _builder = new OdbcCommandBuilder(_adapter1);
DataTable dt = new DataTable(_dt.TableName);
_adapter.Fill(dt);
System.IO.MemoryStream ms = new System.IO.MemoryStream();
_dt.WriteXml(ms);
ms.Seek(0, System.IO.SeekOrigin.Begin);
dt.ReadXml(ms);
ms.Dispose();
_adapter.InsertCommand = _builder.GetInsertCommand();
if (_adapter.InsertCommand.Parameters.Count < _dt.Columns.Count)
{
flag = true;//因为表中有自增字段，所以CommandBuild生成的InserttCommand的参数中少了自增字段
foreach (DataColumn _dc in _dt.Columns)
{
if (!_adapter.InsertCommand.Parameters.Contains(_dc.ColumnName))
{
_adapter.InsertCommand.CommandText =
_adapter.InsertCommand.CommandText.Insert(_adapter.InsertCommand.CommandText.IndexOf(") VALUES"), ',' + _dc.ColumnName);
_adapter.InsertCommand.CommandText =
_adapter.InsertCommand.CommandText.Insert(_adapter.InsertCommand.CommandText.Length - 1, ",?");
_adapter.InsertCommand.Parameters.Add("@" + _dc.ColumnName, OdbcType.Decimal, _dc.MaxLength, _dc.ColumnName);
if (_adapter.InsertCommand.Parameters.Count >= _dt.Columns.Count)
break;
}
}
}
if (flag)
this.ExecuteNoQuery(string.Format("SET IDENTITY_INSERT {0} on", _dt.TableName));
this.BeginTransaction();
try
{
_Command.CommandText = "delete from " + _dt.TableName;
_Command.ExecuteNonQuery();
_adapter.InsertCommand.Transaction = _Command.Transaction;
_nResult = _adapter.Update(dt);
this.CommitTransaction();
}
catch (Exception ex)
{
this.RollbackTransaction();
throw ex;
}
finally
{
if (flag)
this.ExecuteNoQuery(string.Format("SET IDENTITY_INSERT {0} OFF", _dt.TableName));
dt.Dispose();
}
return _nResult;
}
private void BultInsertOdbc(int pageSize, string sqltest, DataTable dataTable)
{
// Connect to the AdventureWorks database.
using (OdbcConnection connectiontest = new
OdbcConnection(sqltest))
{
// Create a SqlDataAdapter.
OdbcDataAdapter adaptertest = new OdbcDataAdapter();
// Set the INSERT command and parameter.
adaptertest.InsertCommand = new OdbcCommand(
"INSERT INTO ATWO.BGDTBIK " +
"(BIK_ENTIDAD,BIK_CENTRO_ALTA,BIK_CUENTA,BIK_LAST_NUM,BIK_LAST_DATE,BIK_STATUS,BIK_BANK_MOD,BIK_CENTER_MOD,BIK_USERID_MOD,BIK_NETNAME_MOD,BIK_TIMEST_MOD) " +
"VALUES (" +
"?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?" +
")",
connectiontest);
adaptertest.InsertCommand.Parameters.Add("@BIK_ENTIDAD", OdbcType.NChar);
adaptertest.InsertCommand.Parameters.Add("@BIK_CENTRO_ALTA", OdbcType.NChar);
adaptertest.InsertCommand.Parameters.Add("@BIK_CUENTA", OdbcType.NChar);
adaptertest.InsertCommand.Parameters.Add("@BIK_LAST_NUM", OdbcType.Numeric);
adaptertest.InsertCommand.Parameters.Add("@BIK_LAST_DATE", OdbcType.Date);
adaptertest.InsertCommand.Parameters.Add("@BIK_STATUS", OdbcType.NChar);
adaptertest.InsertCommand.Parameters.Add("@BIK_BANK_MOD", OdbcType.NChar);
adaptertest.InsertCommand.Parameters.Add("@BIK_CENTER_MOD", OdbcType.NChar);
adaptertest.InsertCommand.Parameters.Add("@BIK_USERID_MOD", OdbcType.NChar);
adaptertest.InsertCommand.Parameters.Add("@BIK_NETNAME_MOD", OdbcType.NChar);
adaptertest.InsertCommand.Parameters.Add("@BIK_TIMEST_MOD", OdbcType.DateTime);
adaptertest.InsertCommand.UpdatedRowSource = UpdateRowSource.None;
// Set the batch size.
adaptertest.UpdateBatchSize = pageSize;
// Execute the update.
int rows = adaptertest.Update(dataTable);
}
}