public int runinsertquery(string query)
{
try
{
cmd = new OleDbCommand(query, con);
return cmd.ExecuteNonQuery();
}
catch (Exception ex)
{
Out.Print.Line(ex.Message);
}
return -1;
}
//**************************************************************************
/// <Description>
/// This method uses to add data to PO_ItemVendorReferenceDetail
/// </Description>
/// <Inputs>
/// PO_ItemVendorReferenceDetailVO
/// </Inputs>
/// <Outputs>
/// newly inserted primarkey value
/// </Outputs>
/// <Returns>
/// void
/// </Returns>
/// <Authors>
/// HungLa
/// </Authors>
/// <History>
/// Tuesday, March 01, 2005
/// </History>
/// <Notes>
/// </Notes>
//**************************************************************************
public void Add(object pobjObjectVO)
{
const string METHOD_NAME = THIS + ".Add()";
OleDbConnection oconPCS = null;
OleDbCommand ocmdPCS = null;
try
{
PO_ItemVendorReferenceDetailVO objObject = (PO_ItemVendorReferenceDetailVO)pobjObjectVO;
string strSql = String.Empty;
Utils utils = new Utils();
oconPCS = new OleDbConnection(Utils.Instance.OleDbConnectionString);
ocmdPCS = new OleDbCommand("", oconPCS);
strSql = "INSERT INTO PO_ItemVendorReferenceDetail("
+ PO_ItemVendorReferenceDetailTable.ITEMVENDORREFERENCEID_FLD + ","
+ PO_ItemVendorReferenceDetailTable.ENDDATE_FLD + ","
+ PO_ItemVendorReferenceDetailTable.UNITPRICE_FLD + ","
+ PO_ItemVendorReferenceDetailTable.FROMQUANTITY_FLD + ","
+ PO_ItemVendorReferenceDetailTable.TOQUANTITY_FLD + ","
+ PO_ItemVendorReferenceDetailTable.FROMPRICE_FLD + ","
+ PO_ItemVendorReferenceDetailTable.TOPRICE_FLD + ")"
+ "VALUES(?,?,?,?,?,?,?)";
ocmdPCS.Parameters.Add(new OleDbParameter(PO_ItemVendorReferenceDetailTable.ITEMVENDORREFERENCEID_FLD, OleDbType.Integer));
ocmdPCS.Parameters[PO_ItemVendorReferenceDetailTable.ITEMVENDORREFERENCEID_FLD].Value = objObject.ItemVendorReferenceID;
ocmdPCS.Parameters.Add(new OleDbParameter(PO_ItemVendorReferenceDetailTable.ENDDATE_FLD, OleDbType.Date));
ocmdPCS.Parameters[PO_ItemVendorReferenceDetailTable.ENDDATE_FLD].Value = objObject.EndDate;
ocmdPCS.Parameters.Add(new OleDbParameter(PO_ItemVendorReferenceDetailTable.UNITPRICE_FLD, OleDbType.Decimal));
ocmdPCS.Parameters[PO_ItemVendorReferenceDetailTable.UNITPRICE_FLD].Value = objObject.UnitPrice;
ocmdPCS.Parameters.Add(new OleDbParameter(PO_ItemVendorReferenceDetailTable.FROMQUANTITY_FLD, OleDbType.Decimal));
ocmdPCS.Parameters[PO_ItemVendorReferenceDetailTable.FROMQUANTITY_FLD].Value = objObject.FromQuantity;
ocmdPCS.Parameters.Add(new OleDbParameter(PO_ItemVendorReferenceDetailTable.TOQUANTITY_FLD, OleDbType.Decimal));
ocmdPCS.Parameters[PO_ItemVendorReferenceDetailTable.TOQUANTITY_FLD].Value = objObject.ToQuantity;
ocmdPCS.Parameters.Add(new OleDbParameter(PO_ItemVendorReferenceDetailTable.FROMPRICE_FLD, OleDbType.Decimal));
ocmdPCS.Parameters[PO_ItemVendorReferenceDetailTable.FROMPRICE_FLD].Value = objObject.FromPrice;
ocmdPCS.Parameters.Add(new OleDbParameter(PO_ItemVendorReferenceDetailTable.TOPRICE_FLD, OleDbType.Decimal));
ocmdPCS.Parameters[PO_ItemVendorReferenceDetailTable.TOPRICE_FLD].Value = objObject.ToPrice;
ocmdPCS.CommandText = strSql;
ocmdPCS.Connection.Open();
ocmdPCS.ExecuteNonQuery();
}
catch (OleDbException ex)
{
if (ex.Errors[1].NativeError == ErrorCode.SQLDUPLICATE_KEYCODE)
{
throw new PCSDBException(ErrorCode.DUPLICATE_KEY, METHOD_NAME, ex);
}
else
{
throw new PCSDBException(ErrorCode.ERROR_DB, METHOD_NAME, ex);
}
}
catch (InvalidOperationException ex)
{
throw new PCSDBException(ErrorCode.ERROR_DB, METHOD_NAME, ex);
}
catch (Exception ex)
{
throw new PCSDBException(ErrorCode.OTHER_ERROR, METHOD_NAME, ex);
}
finally
{
if (oconPCS != null)
{
if (oconPCS.State != ConnectionState.Closed)
{
oconPCS.Close();
}
}
}
}
//---------------------MENU INPUT EDIT----------------------//
//SIMPAN BIODATA//
private void buttonSimpanMasterBiodata_Click(object sender, EventArgs e)
{
//Cek Data Yang Ga Diisi//
if (textBoxNamaLengkapMasterBiodata.Text.Trim() == string.Empty)
{
MessageBox.Show("Nama Lengkap belum diisi Gan");
}
else if (textBoxNamaPanggilanMasterBiodata.Text.Trim() == string.Empty)
{
MessageBox.Show("Nama Panggilannya belum diisi Gan");
}
else if (textBoxTempatLhrMasterBiodata.Text.Trim() == string.Empty)
{
MessageBox.Show("Tempat Lahir belum diisi Gan");
}
else if (dateTimePickerTanggalLhrMasterBiodata.Value.ToString() == string.Empty)
{
MessageBox.Show("Tanggal belum diisi Gan");
}
else if (comboBoxJenisKelaminMasterBiodata.Text.Trim() == string.Empty)
{
MessageBox.Show("Jenis Kelamin belum diisi Gan");
}
else if (comboBoxAgamaMasterBiodata.Text.Trim() == string.Empty)
{
MessageBox.Show("Agama belum diisi Gan");
}
else if (textBoxBahasaMasterBiodata.Text.Trim() == string.Empty)
{
MessageBox.Show("Bahasa belum diisi Gan");
}
else if (textBoxKewarganegaraanMasterBiodata.Text.Trim() == string.Empty)
{
MessageBox.Show("Kewarganegaraan belum diisi Gan");
}
else if (comboBoxStatusKlgMasterBiodata.Text.Trim() == string.Empty)
{
MessageBox.Show("Status Keluarga belum diisi Gan");
}
else if (comboBoxEkoKlgMasterBiodata.Text.Trim() == string.Empty)
{
MessageBox.Show("Keadaan Ekonomi belum diisi Gan");
}
else if (textBoxAlamatMasterBiodata.Text.Trim() == string.Empty)
{
MessageBox.Show("Alamat belum diisi Gan");
}
else if (textBoxAlamatSkrgMasterBiodata.Text.Trim() == string.Empty)
{
MessageBox.Show("Alamat Sekarang belum diisi Gan");
}
else if (textBoxTinggalMasterBiodata.Text.Trim() == string.Empty)
{
MessageBox.Show("Tinggal Bersama belum diisi Gan");
}
else if (comboBoxKeadaan_RumahMasterBiodata.Text.Trim() == string.Empty)
{
MessageBox.Show("Keadaan Rumah belum diisi Gan");
}
else if (comboBoxR_TidurMasterBiodata.Text.Trim() == string.Empty)
{
MessageBox.Show("Ruang Tidur belum diisi Gan");
}
else if (comboBoxR_BelajarMasterBiodata.Text.Trim() == string.Empty)
{
MessageBox.Show("Ruang Belajar belum diisi Gan");
}
else if (comboBoxPenunjangBelajarMasterBiodata.Text.Trim() == string.Empty)
{
MessageBox.Show("Penunjang Belajar belum diisi Gan");
}
else if (textBoxNo_TlpMasterBiodata.Text.Trim() == string.Empty)
{
MessageBox.Show("No. Telp belum diisi Gan");
}
else if (textBoxEmailMasterBiodata.Text.Trim() == string.Empty)
{
MessageBox.Show("Email belum diisi Gan");
}
else if (textBoxNama_AyahMasterBiodata.Text.Trim() == string.Empty)
{
MessageBox.Show("Nama Ayah belum diisi Gan");
}
else if (textBoxTempat_Lhr_AyahMasterBiodata.Text.Trim() == string.Empty)
{
MessageBox.Show("Tempat Lahir Ayah belum diisi Gan");
}
else if (dateTimePickerTanggal_Lhr_AyahMasterBiodata.Value.ToString() == string.Empty)
{
MessageBox.Show("Tanggal Lahir Ayah belum diisi Gan");
}
else if (textBoxAlamat_AyahMasterBiodata.Text.Trim() == string.Empty)
{
MessageBox.Show("Alamat Ayah belum diisi Gan");
}
else if (comboBoxAgama_AyahMasterBiodata.Text.Trim() == string.Empty)
{
MessageBox.Show("Agama belum diisi Gan");
}
else if (textBoxPekerjaan_AyahMasterBiodata.Text.Trim() == string.Empty)
{
MessageBox.Show("Pekerjaan Ayah belum diisi Gan");
}
else if (textBoxPendidikan_AyahMasterBiodata.Text.Trim() == string.Empty)
{
MessageBox.Show("Pendidikan Ayah belum diisi Gan");
}
else if (textBoxPenghasilanAyahMasterBiodata.Text.Trim() == string.Empty)
{
MessageBox.Show("Penghasilan Ayah belum diisi Gan");
}
else if (textBoxNama_IbuMasterBiodata.Text.Trim() == string.Empty)
{
MessageBox.Show("Nama Ibu belum diisi Gan");
}
else if (textBoxTempat_Lhr_IbuMasterBiodata.Text.Trim() == string.Empty)
{
MessageBox.Show("Tempat Lahir Ibu belum diisi Gan");
}
else if (dateTimePickerTanggal_Lhr_IbuMasterBiodata.Value.ToString() == string.Empty)
{
MessageBox.Show("Tanggal Lahir Ibu belum diisi Gan");
}
else if (textBoxAlamat_IbuMasterBiodata.Text.Trim() == string.Empty)
{
MessageBox.Show("Alamat Ibu belum diisi Gan");
}
else if (comboBoxAgamaIbuMasterBiodata.Text.Trim() == string.Empty)
{
MessageBox.Show("Agama Ibu belum diisi Gan");
}
else if (textBoxPekerjaan_IbuMasterBiodata.Text.Trim() == string.Empty)
{
MessageBox.Show("Pekerjaan Ibu belum diisi Gan");
}
else if (textBoxPendidikan_IbuMasterBiodata.Text.Trim() == string.Empty)
{
MessageBox.Show("Pendidikan Ibu belum diisi Gan");
}
else if (textBoxPenghasilan_IbuMasterBiodata.Text.Trim() == string.Empty)
{
MessageBox.Show("Penghasilan Ibu belum diisi Gan");
}
else if (comboBoxJurusan.Text.Trim() == string.Empty)
{
MessageBox.Show("Jurusan belum dipilih Gan");
}
else
{
OleDbConnection koneksi = new OleDbConnection(db);
koneksi.Open();
string query = "";
query = "INSERT INTO MASTER_BIODATA (TANGGAL_DAFTAR, NAMA_LENGKAP, NAMA, " +
"TEMPAT_LHR, TANGGAL_LHR, JENIS_KEL, AGAMA, " +
"BAHASA, KEWARGANEGARAAN, STATUS_KLG, EKO_KLG, ALAMAT, ALAMAT_SKRG, " +
"TINGGAL, KEADAAN_RUMAH, R_TIDUR, R_BELAJAR, PENUNJANG_BELAJAR, " +
"NO_TLP, EMAIL, NAMA_AYAH, TEMPAT_LHR_AYAH, TANGGAL_LHR_AYAH, ALAMAT_AYAH, AGAMA_AYAH, " +
"PEKERJAAN_AYAH, PENDIDIKAN_AYAH, PENGHASILAN_AYAH, NAMA_IBU, TEMPAT_LHR_IBU," +
"TANGGAL_LHR_IBU, ALAMAT_IBU, AGAMA_IBU, PEKERJAAN_IBU, PENDIDIKAN_IBU, " +
"PENGHASILAN_IBU, JURUSAN ) VALUES ('"
+ DateTime.Now.ToString() + "', '" //TANGGAL_DAFTAR
+ textBoxNamaLengkapMasterBiodata.Text.Replace("'", "''") + "', '" //NAMA_LENGKAP
+ textBoxNamaPanggilanMasterBiodata.Text.Replace("'", "''") + "', '" // NAMA
+ textBoxTempatLhrMasterBiodata.Text.Replace("'", "''") + "', '" //TEMPAT_LHR
+ dateTimePickerTanggalLhrMasterBiodata.Value.ToString() + "', '" //TANGGAL_LHR
+ comboBoxJenisKelaminMasterBiodata.Text.Replace("'", "''") + "', '" //JENIS_KEL
+ comboBoxAgamaMasterBiodata.Text.Replace("'", "''") + "', '" //AGAMA
+ textBoxBahasaMasterBiodata.Text.Replace("'", "''") + "', '" //BAHASA
+ textBoxKewarganegaraanMasterBiodata.Text.Replace("'", "''") + "', '" //KEWARGANEGARAAN
+ comboBoxStatusKlgMasterBiodata.Text.Replace("'", "''") + "', '" //STATUS_KLG
+ comboBoxEkoKlgMasterBiodata.Text.Replace("'", "''") + "', '" //EKO_KLG
+ textBoxAlamatMasterBiodata.Text.Replace("'", "''") + "', '" //ALAMAT
+ textBoxAlamatSkrgMasterBiodata.Text.Replace("'", "''") + "', '" //ALAMAT_SKRG
+ textBoxTinggalMasterBiodata.Text.Replace("'", "''") + "', '" // TINGGAL
+ comboBoxKeadaan_RumahMasterBiodata.Text.Replace("'", "''") + "', '" //KEADAAN_RUMAH
+ comboBoxR_TidurMasterBiodata.Text.Replace("'", "''") + "', '" //R_TIDUR
+ comboBoxR_BelajarMasterBiodata.Text.Replace("'", "''") + "', '" //R_BELAJAR
+ comboBoxPenunjangBelajarMasterBiodata.Text.Replace("'", "''") + "', '" //PENUNJANG_BELAJAR
+ textBoxNo_TlpMasterBiodata.Text.Replace("'", "''") + "', '" //NO_TLP
+ textBoxEmailMasterBiodata.Text.Replace("'", "''") + "', '" //EMAIL
+ textBoxNama_AyahMasterBiodata.Text.Replace("'", "''") + "', '" //NAMA_AYAH
+ textBoxTempat_Lhr_AyahMasterBiodata.Text.Replace("'", "''") + "', '" //TEMPAT_LHR_AYAH
+ dateTimePickerTanggal_Lhr_AyahMasterBiodata.Value.ToString() + "', '" //TANGGAL_LAHIR_AYAH
+ textBoxAlamat_AyahMasterBiodata.Text.Replace("'", "''") + "', '" //ALAMAT_AYAH
+ comboBoxAgama_AyahMasterBiodata.Text.Replace("'", "''") + "', '" //AGAMA_AYAH
+ textBoxPekerjaan_AyahMasterBiodata.Text.Replace("'", "''") + "', '" //PEKERJAAN_AYAH
+ textBoxPendidikan_AyahMasterBiodata.Text.Replace("'", "''") + "', " //PENDIDIKAN_AYAH
+ textBoxPenghasilanAyahMasterBiodata.Text.Replace("'", "''") + ", '" //PENGHASILAN_AYAH
+ textBoxNama_IbuMasterBiodata.Text.Replace("'", "''") + "', '" //NAMA_IBU
+ textBoxTempat_Lhr_IbuMasterBiodata.Text.Replace("'", "''") + "', '" //TEMPAT_LHR_IBU
+ dateTimePickerTanggal_Lhr_IbuMasterBiodata.Value.ToString() + "', '" //TANGGAL_LHR_IBU
+ textBoxAlamat_IbuMasterBiodata.Text.Replace("'", "''") + "', '" //ALAMAT_IBU
+ comboBoxAgamaIbuMasterBiodata.Text.Replace("'", "''") + "', '" //AGAMA_IBU
+ textBoxPekerjaan_IbuMasterBiodata.Text.Replace("'", "''") + "', '" //PEKERJAAN_IBU
+ textBoxPendidikan_IbuMasterBiodata.Text.Replace("'", "''") + "', " //PENDIDIKAN_IBU
+ textBoxPenghasilan_IbuMasterBiodata.Text.Replace("'", "''") + ", '" //PENGHASILAN_IBU
+ comboBoxJurusan.Text.Replace("'", "''") + "')"; //JURUSAN //END
OleDbCommand komen = new OleDbCommand();
komen.CommandText = query;
komen.Connection = koneksi;
komen.ExecuteNonQuery();
koneksi.Close();
MessageBox.Show("Data Berhasil Disimpan");
Data();
}
}
public void ExecuteNonQuery(string sql)
{
OleDbCommand cmd = new OleDbCommand(sql, conn);
cmd.ExecuteNonQuery();
}
protected ColumnInfo SqlAddColumn(string columnName, string accessType, bool isDynamic, bool updateDatabase)
{
ColumnInfo result = null;
if (updateDatabase)
{
try
{
BeforeSqlQuery();
///////////////////////////////////////////////////////////////////////////////
try
{
var commandText = String.Format("ALTER TABLE [{0}] ADD COLUMN [{1}] {2}", _tableName, columnName, accessType);
var cmd = new OleDbCommand(commandText, _connection);
cmd.ExecuteNonQuery();
}
catch (Exception ex)
{
var hresult = -2147467259;
if (ex.HResult == hresult)
{
throw new Exception("Too many columns defined. To continue adding new columns please open this database in Access and press 'Compact and Repair'");
}
throw;
}
///////////////////////////////////////////////////////////////////////////////
ResetCommands();
}
finally
{
AfterSqlQuery();
}
}
else
{
ResetCommands();
}
///////////////////////////////////////////////////////////////////////////////
var clrType = AccessTypeToClrType(accessType);
_table.Columns.Add(columnName, clrType);
///////////////////////////////////////////////////////////////////////////////
result = new ColumnInfo
{
Name = columnName,
Type = clrType,
IsDynamic = isDynamic,
IsExtra = !isDynamic
};
_allColumnsList.Add(result);
///////////////////////////////////////////////////////////////////////////////
AdjustSqlColumnIndices();
///////////////////////////////////////////////////////////////////////////////
return(result);
}
private void WriteQuoteRecord()
{
string file = vari.DefaultDirectory + "Quotes.accdb";
string ConnectionString = "Provider = Microsoft.ACE.OLEDB.12.0; Data Source =" + file + ";";
// Attempt to connect to the database
using (var connection1 = new OleDbConnection(ConnectionString))
{
OleDbCommand OComm = new OleDbCommand();
OComm.Connection = connection1;
try
{
int q1; int.TryParse(tbQty1.Text, out q1);
int q2; int.TryParse(tbQty2.Text, out q2);
int q3; int.TryParse(tbQty3.Text, out q3);
int q4; int.TryParse(tbQty4.Text, out q4);
double p1; double.TryParse(lq1.Text, out p1);
double p2; double.TryParse(lq2.Text, out p2);
double p3; double.TryParse(lq3.Text, out p3);
double p4; double.TryParse(lq4.Text, out p4);
double dia; double.TryParse(cmbDiameter.Text, out dia);
double length; double.TryParse(tbLength.Text, out length);
double watt; double.TryParse(tbWatts.Text, out watt);
double volt; double.TryParse(tbVolts.Text, out volt);
int leads; int.TryParse(tbLeads.Text, out leads);
int leadcov; int.TryParse(tbLeadCov.Text, out leadcov);
double multi; double.TryParse(tbLabor.Text, out multi);
double adder; double.TryParse(tbManualAdder.Text, out adder);
string ad1 = ""; double ad1p = 0; string ad2 = ""; double ad2p = 0; string ad3 = ""; double ad3p = 0; string ad4 = ""; double ad4p = 0; string ad5 = ""; double ad5p = 0; string ad6 = ""; double ad6p = 0;
//VALIDATE THE ARRAY AND SET VARIABLES TO NULL IF ARRAY IS NULL
if (vari.AdderArray == null || vari.AdderArray.Length == 0)
{
ad1 = ""; ad1p = 0; ad2 = ""; ad2p = 0; ad3 = ""; ad3p = 0; ad4 = ""; ad4p = 0; ad5 = ""; ad5p = 0; ad6 = ""; ad6p = 0;
}
else
{
if (vari.AdderArray.GetLength(0) > 0)
{
ad1 = vari.AdderArray[0, 0].ToString(); ad1p = Convert.ToDouble(vari.AdderArray[0, 1]);
}
else
{
ad1 = ""; ad1p = 0;
}
if (vari.AdderArray.GetLength(0) > 1)
{
ad2 = vari.AdderArray[1, 0].ToString(); ad2p = Convert.ToDouble(vari.AdderArray[1, 1]);
}
else
{
ad2 = ""; ad2p = 0;
}
if (vari.AdderArray.GetLength(0) > 2)
{
ad3 = vari.AdderArray[2, 0].ToString(); ad3p = Convert.ToDouble(vari.AdderArray[2, 1]);
}
else
{
ad3 = ""; ad3p = 0;
}
if (vari.AdderArray.GetLength(0) > 3)
{
ad4 = vari.AdderArray[3, 0].ToString(); ad4p = Convert.ToDouble(vari.AdderArray[3, 1]);
}
else
{
ad4 = ""; ad4p = 0;
}
if (vari.AdderArray.GetLength(0) > 4)
{
ad5 = vari.AdderArray[4, 0].ToString(); ad5p = Convert.ToDouble(vari.AdderArray[4, 1]);
}
else
{
ad5 = ""; ad5p = 0;
}
if (vari.AdderArray.GetLength(0) > 5)
{
ad6 = vari.AdderArray[5, 0].ToString(); ad6p = Convert.ToDouble(vari.AdderArray[5, 1]);
}
else
{
ad6 = ""; ad6p = 0;
}
}
connection1.Open();
// INSERT A NEW RECORD FOR THE QUOTE
string CmdLine1 = "INSERT INTO CartQuotes (cust,dte,q1,q2,q3,q4,p1,p2,p3,p4,dia,length,termstyle,leads,leadcov,watts,volts,multi,adder,notes,smt,filename,ad1,ad1p,ad2,ad2p,ad3,ad3p,ad4,ad4p,ad5,ad5p,ad6,ad6p,pn) " +
"VALUES ('" + myComboBox.Text + "','" + DateTime.Now + "'," + q1 + "," + q2 + "," + q3 + "," + q4 + "," + vari.p1 + "," + vari.p2 + "," + vari.p3 + "," + vari.p4 + "," +
" '" + cmbDiameter.Text + "'," + length + ",'" + cmbTermStyle.Text + "'," + leads + "," + leadcov + "," + watt + "," + volt + "," + multi + "," + adder + ", '" + tbSpecials.Text + "','" + tbSMT.Text + "'," +
" '" + vari.SavedPDFDir + "','" + ad1 + "'," + ad1p + ",'" + ad2 + "'," + ad2p + ",'" + ad3 + "'," + ad3p + ",'" + ad4 + "'," + ad4p + ",'" + ad5 + "'," + ad5p + ",'" + ad6 + "'," + ad6p + ",'" + vari.pn + "'); ";
OleDbCommand Insert1 = new OleDbCommand(CmdLine1, connection1);
Insert1.ExecuteNonQuery();
}
catch (Exception ex)
{ MessageBox.Show(ex.Message); }
finally
{ connection1.Close(); }
}
}
public FuncStatus DataBind(BindOperator _bp)
{
bool _tf = true;
int i;
OleDbConnection _conn = new OleDbConnection(Prument.Properties.Settings.Default.Properties["dataConnectionString"].DefaultValue.ToString());
OleDbCommand _cmd = new OleDbCommand();
OleDbDataReader _dr;
_cmd.Connection = _conn;
try
{
_conn.Open();
if (_bp == BindOperator.Update) {
_cmd.CommandText = "delete from Images where pID = " + this.pID;
_cmd.ExecuteNonQuery();
}
if (_bp == BindOperator.Insert || _bp == BindOperator.Update)
{
i = 0;
while (i < _max)
{
_cmd.CommandText = "insert into Images (Path,pID) values ('" + this.Path[i] + "'," + this.pID + ")";
_cmd.ExecuteNonQuery();
i++;
}
}
_cmd.CommandText = "select ID,Path,pID from Images where pID=" + this.pID;
_dr = _cmd.ExecuteReader();
i = 0;
while (_dr.Read())
{
this.ID[i] = _dr.GetInt32(0);
this.Path[i] = _dr.GetValue(1).ToString();
this.pID = _dr.GetInt32(2);
i++;
}
}
catch (Exception ex) {
_tf = false;
Exception _ex = new Exception("ORM.Images:" + ex.Message);
throw _ex;
}
finally
{
_conn.Close();
}
return _tf == false ? FuncStatus.Fail : FuncStatus.Success;
}
public int connectEdit(Student x, string editchoice, string editdata)
{
OleDbConnection connection = new OleDbConnection();
try
{
connection.ConnectionString = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\myFolder\myAccessFile.accdb;
Jet OLEDB:Database Password=MyDbPassword; Data Source=C:\Users\franc_000\Documents\College\StudentRegfinal\StudentReg\studentregdb.accdb; Persist Security Info = false; ";
//var cmd = new OleDbCommand("UPDATE Student SET "+"@editchoice"+" = '"+"@entrydata"+"' WHERE "+"@a"+" = @searchdata;"); //need correct reference
var cmd = new OleDbCommand("UPDATE Student SET FirstName = 'bananak' WHERE Id = '123456';"); //need correct reference
connection.Open();
cmd.Parameters.AddRange(new[] {
new OleDbParameter("@entrytype", editchoice),
new OleDbParameter("@entrydata", x.Id),
new OleDbParameter("@a", "Id"),
new OleDbParameter("@searchdata", x.Id)
});
cmd.Connection = connection;
Console.WriteLine(editchoice);
Console.WriteLine(editdata);
Console.WriteLine(x.Id);
cmd.ExecuteNonQuery();
Console.WriteLine("Student edited successfully.");
}
catch(Exception ex)
{
Console.WriteLine("Error " + ex);
}
connection.Close();
return 0;
}
private Boolean DatabaseWrite(DataEntry inputEntry)
{
// Writes a given inputEntry to the Access Database
string ConnectionString = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + DatabaseLocation + ";Persist Security Info=False;";
string EmployeeInsertCommand = "INSERT INTO `Client Contact Information` (";
string CaseInsertCommand = "INSERT INTO Documentation (";
// Writing Insert Command strings
String[] EmployeeFields = new String[10] {
"Employment Number", "Last Name", "First Name", "Street Address", "City", "Province", "Postal Code", "Phone Number (Home)", "Phone Number (Work)", "Email"
}; // Consider replacing with reference // Also may differ from actual DB
for (int i = 0; i < EmployeeFields.Length; i++)
{
EmployeeInsertCommand += "`" + EmployeeFields[i] + "`";
if (i != EmployeeFields.Length - 1)
{
EmployeeInsertCommand += ",";
}
}
EmployeeInsertCommand += ") VALUES (";
if (!inputEntry.ExistingPerson)
{
for (int i = 0; i < EmployeeFields.Length; i++)
{
object entryValue = inputEntry.PersonalFields[EmployeeFields[i]];
if (entryValue is int)
{
EmployeeInsertCommand += entryValue.ToString();
}
else
{
EmployeeInsertCommand += "\"" + entryValue.ToString() + "\"";
}
if (i != EmployeeFields.Length - 1)
{
EmployeeInsertCommand += ",";
}
}
}
EmployeeInsertCommand += ")";
String[] CaseFields = new String[7] {
"Employee Number", "Status", "LTD Eligible", "Referral Recieved", "Sick Leave Start", "Hourly Salary", "Hours Worked/Day"
}; //Replace with reference probably //Ignores ID and non filled fields
for (int i = 0; i < CaseFields.Length; i++)
{
CaseInsertCommand += "`" + CaseFields[i] + "`";
if (i != CaseFields.Length - 1)
{
CaseInsertCommand += ",";
}
}
CaseInsertCommand += ") VALUES (";
for (int i = 0; i < CaseFields.Length; i++)
{
object entryValue = inputEntry.RTWFields[CaseFields[i]];
if (entryValue == null)
{
entryValue = "";
}
if (entryValue is int || entryValue is bool || entryValue is decimal)
{
CaseInsertCommand += entryValue.ToString();
}
else
{
CaseInsertCommand += "\"" + entryValue.ToString() + "\"";
}
if (i != CaseFields.Length - 1)
{
CaseInsertCommand += ",";
}
}
CaseInsertCommand += ")";
OleDbConnection DatabaseConnection = new OleDbConnection(ConnectionString);
OleDbCommand EmployeeCommand = new OleDbCommand(EmployeeInsertCommand, DatabaseConnection);
OleDbCommand CaseCommand = new OleDbCommand(CaseInsertCommand, DatabaseConnection);
// Attempts to Connect to the database
try
{
DatabaseConnection.Open();
// DB Access Here is the first table as there is only one because of the query
if (!inputEntry.ExistingPerson)
{
EmployeeCommand.ExecuteNonQuery();
}
CaseCommand.ExecuteNonQuery();
DatabaseConnection.Close();
return(true);
}
catch (Exception ex)
{
// Make sure to close DB on fail or success
DatabaseConnection.Close();
MessageBox.Show(ex.Message);
if (MessageBox.Show("Error occurred in Database Connect", "Retry?", MessageBoxButtons.YesNo, MessageBoxIcon.Asterisk) == DialogResult.Yes)
{
return(DatabaseWrite(inputEntry));
}
else
{
return(false);
}
}
}
private void AddMerchandise_Click(object sender, EventArgs e)
{
string strName = listBox1.SelectedItem.ToString();
int stockCounts = System.Convert.ToInt32(textBox2.Text);
int extingCounts = System.Convert.ToInt32(ExtingCountText.Text);
OleDbConnection conn = MyDataBase.GetConn();
if (AddMerchandise.Text.Equals("添加"))
{
string strCmd1 = @"update Merchandise set StockCounts=StockCounts+" + stockCounts.ToString();
strCmd1 += @" where MerchandiseName='" + strName + "'";
string strCmd2 = @"update Merchandise set ExitingCounts=ExitingCounts+" + stockCounts.ToString();
strCmd2 += @" where MerchandiseName='" + strName + "'";
int iRows = 0;
sqlcmd.CommandText = strCmd1;
iRows = sqlcmd.ExecuteNonQuery();
if (iRows == 0)
{
MessageBox.Show("添加失败，请查证后重新添加。");
return;
}
sqlcmd.CommandText = strCmd2;
iRows = sqlcmd.ExecuteNonQuery();
if (iRows == 0)
{
MessageBox.Show("添加失败，请查证后重新添加。");
return;
}
SetText();
//////////////////////////////////////////////////////////////////////////
//添加出售信息
string timeDate = System.DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss:ffff");
string strCmd3 = @"INSERT INTO stockmerchandise ( merchandisename, timedate, stockcounts ) values('" + strName + "','" + timeDate + "'," + stockCounts.ToString() + ")";
sqlcmd.CommandText = strCmd1;
iRows = sqlcmd.ExecuteNonQuery();
if (iRows == 0)
{
MessageBox.Show("添加失败，请查证后重新添加。");
return;
}
}
else if (AddMerchandise.Text.Equals("更新"))
{
string strCmd1 = @"update Merchandise set StockCounts=" + stockCounts.ToString();
strCmd1 += @" where MerchandiseName='" + strName + "'";
string strCmd2 = @"update Merchandise set ExitingCounts=" + extingCounts.ToString();
strCmd2 += @" where MerchandiseName='" + strName + "'";
int iRows = 0;
sqlcmd.CommandText = strCmd1;
iRows = sqlcmd.ExecuteNonQuery();
if (iRows == 0)
{
MessageBox.Show("更新失败，请查证后重新添加。");
return;
}
sqlcmd.CommandText = strCmd2;
iRows = sqlcmd.ExecuteNonQuery();
if (iRows == 0)
{
MessageBox.Show("更新失败，请查证后重新添加。");
return;
}
ExtingCountText.Visible = false;
ExtingCountLable.Visible = false;
AddMerchandise.Text = "添加";
SetText();
}
if (dataGridView1.SelectedRows.Count != 0)
{
if (dataGridView1.SelectedRows[0].Cells.Count > 1)
{
DataGridViewRow dRow = dataGridView1.SelectedRows[0];
for (int i = 0; i < dataGridView1.RowCount; i++)
{
string str = dRow.Cells[0].Value.ToString();
Trace.WriteLine(str);
if (str.Equals(strName))
{
Trace.WriteLine("Enter if :");
dataGridView1.CurrentCell = dataGridView1[0, 1];
}
}
}
}
UpdateDataGridView();
}
public bool ExecuteSQL(string pSQL, List <System.Data.OleDb.OleDbParameter> OleDbParameterList = null, bool Is_Stored_Procedure = false, int TimeOut = 30)
{
bool result = false;
OleDbCommand command = null;
OleDbConnection conn = null;
try
{
if (this.transaction == null)
{
conn = new OleDbConnection(this.ConnectionString);
conn.Open();
command = new OleDbCommand(pSQL, conn);
command.CommandTimeout = TimeOut;
if (Is_Stored_Procedure)
{
command.CommandType = CommandType.StoredProcedure;
}
else
{
command.CommandType = CommandType.Text;
}
if (OleDbParameterList != null)
{
foreach (var parameter in OleDbParameterList)
{
command.Parameters.Add(parameter);
}
}
command.ExecuteNonQuery();
}
else
{
if (this.transaction_connection.State == ConnectionState.Closed)
{
this.transaction_connection.Open();
}
command = new OleDbCommand(pSQL, this.transaction_connection);
command.Transaction = this.transaction;
command.CommandTimeout = TimeOut;
if (Is_Stored_Procedure)
{
command.CommandType = CommandType.StoredProcedure;
}
else
{
command.CommandType = CommandType.Text;
}
if (OleDbParameterList != null)
{
foreach (var parameter in OleDbParameterList)
{
command.Parameters.Add(parameter);
}
}
command.ExecuteNonQuery();
}
}
catch (Exception ex)
{
throw (ex);
}
finally
{
if (command != null)
{
command.Dispose();
command = null;
}
if (this.transaction == null)
{
if (conn != null)
{
conn.Close();
conn.Dispose();
}
}
}
result = true;
return(result);
}
/// <summary>
///
/// </summary>
/// <param name="pathBDXls">Ruta completa del archivo Excel</param>
/// <param name="dtXml">Objeto Datatable que contiene los datos a exportar</param>
/// <param name="dbCnnt">Conexion Oledb</param>
/// <param name="rowIni">Fila inicial</param>
/// <param name="rowFin">Fila final</param>
/// <param name="count">Contador</param>
private static void DataTableToXls2003(string pathBDXls, DataTable dtXml, OleDbConnection dbCnnt, int rowIni, int rowFin, int count)
{
try
{
Int32 iRows = dtXml.Rows.Count;
Int32 iCount = 0;
string sTb = dtXml.TableName;
string sCm = String.Empty;
Boolean bMp = false;
StringBuilder sCm0 = new StringBuilder();
StringBuilder sCm1 = new StringBuilder();
sCm0.Append("(");
sCm1.Append("(");
FileInfo archivoexcel = null;
string logarchivo = string.Empty;
DateTime fechaarchivo = DateTime.Now;
archivoexcel = new FileInfo(pathBDXls);
logarchivo = "-------------------------------------------------------------------------------------------------------------------\n\n";
logarchivo = logarchivo + "Registro de generacion de archivo: " + archivoexcel.Name + "\n";
logarchivo = logarchivo + "Generando conexion con origen de datos excel 2003...\n";
logarchivo = logarchivo + "Conexion generada\n";
foreach (DataColumn dc in dtXml.Columns)
{
if (dc.Ordinal == 0)
{
sCm0.Append("[");
sCm1.Append("[");
}
else
{
sCm0.Append(", [");
sCm1.Append(", [");
}
if (bMp)
{
//sCm = maping[dc.Ordinal].Trim();
}
else
{
sCm = dc.ColumnName.Trim();
}
sCm0.Append(sCm);
sCm1.Append(sCm);
if (dc.DataType.Equals(System.Type.GetType("System.String")))
{
sCm0.Append("] NVARCHAR");
}
else if (dc.DataType.Equals(System.Type.GetType("System.DateTime")))
{
sCm0.Append("] DateTime");
}
else if (dc.DataType.Equals(System.Type.GetType("System.Decimal")) || dc.DataType.Equals(System.Type.GetType("System.Int32")) || dc.DataType.Equals(System.Type.GetType("System.Double")))
{
sCm0.Append("] Decimal");
}
else if (dc.DataType.Equals(System.Type.GetType("System.Byte")) || dc.DataType.Equals(System.Type.GetType("System.Int16")) || dc.DataType.Equals(System.Type.GetType("System.Int32")))
{
sCm0.Append("] Int");
}
else if (dc.DataType.Equals(System.Type.GetType("System.Boolean")))
{
sCm0.Append("] Boolean");
}
else
{
sCm0.Append("] NVARCHAR");
}
sCm1.Append("]");
}
sCm0.Append(")");
sCm1.Append(")");
logarchivo = logarchivo + "Generando archivo Excel 2003...\n";
if (DBXlsCreateTable(dbCnnt, (dtXml.TableName + (count + 1).ToString()), sCm0.ToString()) == false)
{
}
logarchivo = logarchivo + "Archivo Excel 2003 generado con exito en : " + pathBDXls + "\n";
StringBuilder sCm2;
string SCmd = String.Empty;
OleDbCommand dbCmd = new OleDbCommand();
int iCs = dtXml.Columns.Count - 1;
Int16 i;
string strValor = String.Empty;
dbCmd.Connection = dbCnnt;
dbCnnt.Open();
logarchivo = logarchivo + "Cantidad de registros a insertar en el archivo Excel: " + dtXml.Rows.Count + "\n";
logarchivo = logarchivo + "Insertando registros en archivo Excel...\n";
for (int fila = rowIni; fila < rowFin; fila++)
{
DataRow dr = dtXml.Rows[fila];
iCount = iCount + 1;
sCm2 = new StringBuilder();
sCm2.Append("(");
for (i = 0; i <= iCs; i++)
{
if (i == 0)
{
if (dtXml.Columns[i].DataType.Equals(System.Type.GetType("System.String")))
{
sCm2.Append("'");
}
else if (dtXml.Columns[i].DataType.Equals(System.Type.GetType("System.DateTime")))
{
if (dr[i] != System.DBNull.Value)
{
sCm2.Append("'");
}
}
}
else
{
if (dtXml.Columns[i].DataType.Equals(System.Type.GetType("System.String")))
{
sCm2.Append(", '");
}
else if (dtXml.Columns[i].DataType.Equals(System.Type.GetType("System.DateTime")))
{
if (dr[i] != System.DBNull.Value)
{
//Verificamos que la fecha sea mayor a 1900
if (Convert.ToDateTime(dr[i]).Year >= 1900)
{
sCm2.Append(", '");
}
else
{
sCm2.Append(", ");
}
}
else
{
sCm2.Append(", ");
}
}
else
{
sCm2.Append(", ");
}
}
if (dr[i] == System.DBNull.Value)
{
strValor = "";
if (dtXml.Columns[i].DataType.Equals(System.Type.GetType("System.DateTime")))
{
strValor = "Null";
}
}
else
{
if (dtXml.Columns[i].DataType.Equals(System.Type.GetType("System.DateTime")))
{
//Verificamos que la fecha sea mayor a 1900
if (Convert.ToDateTime(dr[i]).Year >= 1900)
{
strValor = Convert.ToDateTime(dr[i]).ToString("yyyy-MM-dd HH:mm:ss");
}
else
{
strValor = "Null";
}
}
else if (dtXml.Columns[i].DataType.Equals(System.Type.GetType("System.Boolean")))
{
if (Convert.ToBoolean(dtXml.Columns[i]) == false)
{
strValor = "False";
}
else
{
strValor = "True";
}
}
else
{
strValor = dr[i].ToString().Replace("'", "''");
//valor = strValor;
}
}
sCm2.Append(strValor);
if (dtXml.Columns[i].DataType.Equals(System.Type.GetType("System.String")))
{
sCm2.Append("'");
}
else if (dtXml.Columns[i].DataType.Equals(System.Type.GetType("System.DateTime")))
{
if (dr[i] != System.DBNull.Value)
{
//Verificamos que la fecha sea mayor a 1900
if (Convert.ToDateTime(dr[i]).Year >= 1900)
{
sCm2.Append("'");
}
}
}
else if (dtXml.Columns[i].DataType.Equals(System.Type.GetType("System.Decimal")) || dtXml.Columns[i].DataType.Equals(System.Type.GetType("System.Int16")) || dtXml.Columns[i].DataType.Equals(System.Type.GetType("System.Int32")) || dtXml.Columns[i].DataType.Equals(System.Type.GetType("System.Double")) || dtXml.Columns[i].DataType.Equals(System.Type.GetType("System.Byte")))
{
if (strValor.Length == 0)
{
sCm2.Append("0");
}
}
}
sCm2.Append(")");
SCmd = "INSERT INTO " + "[" + (sTb + (count + 1).ToString()) + "]" + sCm1.ToString() + " Values " + sCm2.ToString();
//insert = "INSERT INTO " + "[" + sTb + "]" + sCm1.ToString() + " Values " + sCm2.ToString();
dbCmd.CommandText = SCmd;
dbCmd.ExecuteNonQuery();
}
logarchivo = logarchivo + "Insertados " + iCount + " registros en archivo Excel con exito\n";
logarchivo = logarchivo + "Cerrando conexion en archivo Excel: " + pathBDXls + "...\n";
logarchivo = logarchivo + "Conexion en archivo Excel " + pathBDXls + " cerrada con exito\n\n";
Log_Archivo(logarchivo, archivoexcel.DirectoryName + "\\" + "LogExcel_" + fechaarchivo.ToString("dd-MM-yyyy") + ".txt");
dbCnnt.Close();
}
catch (Exception ex)
{
if (dbCnnt.State == ConnectionState.Open)
{
dbCnnt.Close();
}
throw ex;
}
}
public int Reinspect(string from, string to, string qcn, int amount)
{
this.Connection.Open();
OleDbTransaction transaction = this.Connection.BeginTransaction();
int result = 0;
try
{
string cmdText = "SELECT 送檢次數 FROM 產品檢驗 WHERE 工時資料編號 = ?";
OleDbCommand cmd = new OleDbCommand(cmdText, this.Connection);
cmd.Transaction = transaction;
cmd.Parameters.Add(new OleDbParameter("工時資料編號", from));
int num = Convert.ToInt32(cmd.ExecuteScalar());
cmdText = "INSERT INTO 產品檢驗 (工時資料編號,QCN,待驗數量,送檢次數,最後送檢編號,送檢日期) VALUES (?,?,?,?,?,?)";
cmd = new OleDbCommand(cmdText, this.Connection);
cmd.Transaction = transaction;
cmd.Parameters.Add(new OleDbParameter("工時資料編號1", to));
cmd.Parameters.Add(new OleDbParameter("QCN", qcn));
cmd.Parameters.Add(new OleDbParameter("待驗數量", amount));
cmd.Parameters.Add(new OleDbParameter("送檢次數", num + 1));
cmd.Parameters.Add(new OleDbParameter("最後送檢編號", to));
//cmd.Parameters.Add(new OleDbParameter("送檢日期", DateTime.Today));
OleDbParameter paramDate = new OleDbParameter();
paramDate.OleDbType = OleDbType.DBTimeStamp;
paramDate.Value = DateTime.Now.ToString("s");
cmd.Parameters.Add(paramDate);
result = cmd.ExecuteNonQuery();
cmdText = "UPDATE 產品檢驗 SET 重驗 = True WHERE 工時資料編號 = ?";
cmd = new OleDbCommand(cmdText, this.Connection);
cmd.Transaction = transaction;
//cmd.Parameters.Add(new OleDbParameter("最後送檢編號", to));
cmd.Parameters.Add(new OleDbParameter("工時資料編號", from));
cmd.ExecuteNonQuery();
cmdText = "UPDATE 產品檢驗 SET 最後送檢編號 = ? WHERE 最後送檢編號 = ?";
cmd = new OleDbCommand(cmdText, this.Connection);
cmd.Transaction = transaction;
cmd.Parameters.Add(new OleDbParameter("最後送檢編號1", to));
cmd.Parameters.Add(new OleDbParameter("最後送檢編號2", from));
cmd.ExecuteNonQuery();
cmdText = "INSERT INTO NG原因 (工時資料編號, 原因, 來源編號) SELECT '" + to + "' as 工時資料編號, 原因, 來源編號 FROM NG原因 WHERE 工時資料編號 = ?";
cmd = new OleDbCommand(cmdText, this.Connection);
cmd.Transaction = transaction;
cmd.Parameters.Add(new OleDbParameter("工時資料編號", from));
cmd.ExecuteNonQuery();
transaction.Commit();
this.Connection.Close();
return result;
}
catch (Exception ex)
{
transaction.Rollback();
this.Connection.Close();
throw ex;
}
}
public int DeleteEx(string id)
{
int retVal = 0;
OleDbConnection conn = Instance.Connection;
OleDbTransaction transaction = null;
OleDbCommand cmd = new OleDbCommand();
conn.Open();
try
{
transaction = conn.BeginTransaction();
cmd.Connection = conn;
cmd.Transaction = transaction;
//改正產品檢驗資料
//取得產品檢驗資料
cmd.CommandText = "SELECT * FROM 產品檢驗 WHERE 工時資料編號=?";
cmd.Parameters.Clear();
cmd.Parameters.Add(new OleDbParameter("工時資料編號", id));
OleDbDataReader reader = cmd.ExecuteReader();
if (reader.Read())
{
string last = reader["最後送檢編號"].ToString();
int inspectDel = (int)reader["送檢次數"];
reader.Close();
cmd.CommandText = "SELECT * FROM 產品檢驗 WHERE 最後送檢編號=? AND 工時資料編號<>? ORDER BY 送檢次數 ASC";
cmd.Parameters.Clear();
cmd.Parameters.Add(new OleDbParameter("最後送檢編號old", last));
cmd.Parameters.Add(new OleDbParameter("工時資料編號", id));
OleDbDataReader reader2 = cmd.ExecuteReader();
while (reader2.Read())
{
string nextID = reader2["工時資料編號"].ToString();
int inspect = (int)reader2["送檢次數"];
if (inspect > inspectDel)
{
//將該筆之後的檢驗資料做處理
OleDbCommand cmd2 = new OleDbCommand();
cmd2.Connection = conn;
cmd2.Transaction = transaction;
cmd2.CommandText = "UPDATE 產品檢驗 SET 送檢次數=送檢次數-1 WHERE 工時資料編號=?";
cmd2.Parameters.Clear();
cmd2.Parameters.Add(new OleDbParameter("工時資料編號", nextID));
cmd2.ExecuteNonQuery();
}
else
{
//將該筆之前的檢驗資料做處理
}
}
reader2.Close();
//刪除產品檢驗資料
cmd.CommandText = "DELETE FROM 產品檢驗 WHERE 工時資料編號=?";
cmd.Parameters.Clear();
cmd.Parameters.Add(new OleDbParameter("工時資料編號", id));
cmd.ExecuteNonQuery();
//挑出最後一筆檢驗紀錄
cmd.CommandText = "SELECT 工時資料編號 FROM 產品檢驗 WHERE 最後送檢編號 =? ORDER BY 送檢次數 DESC";
cmd.Parameters.Clear();
cmd.Parameters.Add(new OleDbParameter("最後送檢編號old", last));
reader2 = cmd.ExecuteReader();
if (reader2.Read())
{
string maxID = reader2["工時資料編號"].ToString();
reader2.Close();
//將原有的最後檢驗紀錄更新
cmd.CommandText = "UPDATE 產品檢驗 SET 最後送檢編號=?, 最後檢驗紀錄=FALSE, 重驗=TRUE WHERE 最後送檢編號=?";
cmd.Parameters.Clear();
cmd.Parameters.Add(new OleDbParameter("最後送檢編號new", maxID));
cmd.Parameters.Add(new OleDbParameter("最後送檢編號old", last));
cmd.ExecuteNonQuery();
//將最後一筆設成最後檢驗記錄
cmd.CommandText = "UPDATE 產品檢驗 SET 最後檢驗紀錄=TRUE, 重驗=FALSE WHERE 工時資料編號=?";
cmd.Parameters.Clear();
cmd.Parameters.Add(new OleDbParameter("工時資料編號", maxID));
cmd.ExecuteNonQuery();
}
if (!reader2.IsClosed)
reader2.Close();
}
if (!reader.IsClosed)
reader.Close();
//刪除工時資料
cmd.CommandText = "DELETE FROM 工時 WHERE 編號=?";
cmd.Parameters.Clear();
cmd.Parameters.Add(new OleDbParameter("編號", id));
retVal = cmd.ExecuteNonQuery();
transaction.Commit();
}
catch (Exception ex)
{
try
{
transaction.Rollback();
}
catch (Exception) { }
try
{
conn.Close();
}
catch (Exception) { }
throw ex;
}
conn.Close();
return retVal;
}
public FuncStatus DataBind(BindOperator _bo)
{
bool _tf = true;
OleDbConnection _conn = new OleDbConnection(Prument.Properties.Settings.Default.Properties["dataConnectionString"].DefaultValue.ToString());
OleDbCommand _cmd = new OleDbCommand();
OleDbDataReader _dr;
_cmd.Connection = _conn;
try
{
_conn.Open();
if (_bo == BindOperator.Select)
{
_cmd.CommandText = "select Products.ID,Products.Name,Products.Description from Products where " + (this.ID != 0 ? "ID=" + this.ID.ToString() : (this.Name != null ? "Name='" + this.Name : "Description like '_" + this.Description + "_") + "'");
}
if (_bo == BindOperator.Insert)
{
_cmd.CommandText = "insert into Products (Name,Description) values ('" + this.Name + "','" + this.Description + "')";
_cmd.ExecuteScalar();
_cmd.CommandText = "select ID,Name,Description from products where id = (select Max(ID) from Products)";
}
if (_bo == BindOperator.Update) {
_cmd.CommandText = "update products set Name='" + this.Name + "',Description='" + this.Description + "' where ID=" +this.ID;
_cmd.ExecuteNonQuery();
_cmd.CommandText = "select ID,Name,Description from products where id = " + this.ID;
}
if (_bo == BindOperator.Delete)
{
_cmd.CommandText = "select ID,Name,Description from products where id = " + this.ID;
}
_dr = _cmd.ExecuteReader();
_dr.Read();
this.ID = _dr.GetInt32(0);
this.Name = _dr.GetValue(1).ToString();
this.Description = _dr.GetValue(2).ToString();
this.Images.pID = this.ID;
_dr.Close();
if (_bo == BindOperator.Select) {
Images.DataBind(_bo);
}
if (_bo == BindOperator.Insert) {
Images.DataBind(_bo);
}
if (_bo == BindOperator.Update && this.Images.Path[0] != "") {
Images.DataBind(_bo);
}
if (_bo == BindOperator.Delete)
{
_cmd.CommandText = "delete from products where ID=" + this.ID;
_cmd.ExecuteNonQuery();
this.Images.clear();
this.Images.DataBind(BindOperator.Update);
}
this.Status = ORMStatus.Saved;
}
catch (Exception ex)
{
_tf = false;
Exception _ex = new Exception("ORM.Products["+this.ID+"]:" + ex.Message);
throw _ex;
}
finally {
_conn.Close();
}
return _tf == false?FuncStatus.Fail:FuncStatus.Success;
}
public void WriteData()
{
string sql = "";
string ConnString = GetConnectionString();
string result = System.Text.RegularExpressions.Regex.Replace(GetInputParamCalculatedValue("ColMappingRules"), @",(?=[^']*'(?:[^']*'[^']*')*[^']*$)", "~^GINGER-EXCEL-COMMA-REPLACE^~");
string[] varColMaps = result.Split(',');
string sSetDataUsed = "";
using (OleDbConnection Conn = new OleDbConnection(ConnString))
{
try
{
Conn.Open();
}
catch (Exception ex)
{
System.Threading.Thread.Sleep(3000);
Conn.Open();
Reporter.ToLog(eLogLevel.ERROR, $"Method - {MethodBase.GetCurrentMethod().Name}, Error - {ex.StackTrace}", ex);
}
OleDbCommand Cmd = new OleDbCommand();
Cmd.Connection = Conn;
string SheetName = GetInputParamCalculatedValue("SheetName").Trim();
if (String.IsNullOrEmpty(SheetName))
{
this.Error += "Sheet Name is empty or not selected. Please Select correct sheet name on action configurations";
Conn.Close();
return;
}
if (!SheetName.EndsWith("$"))
{
SheetName += "$";
}
if (SelectAllRows == false)
{
sql = "Select TOP 1 * from [" + SheetName + "]";
}
else
{
sql = "Select * from [" + SheetName + "]";
}
string where = GetInputParamCalculatedValue("SelectRowsWhere");
if (!string.IsNullOrEmpty(where))
{
sql += " WHERE " + where;
}
Cmd.CommandText = sql;
DataTable dt = new DataTable();
OleDbDataAdapter da = new OleDbDataAdapter();
da.SelectCommand = Cmd;
string updateSQL = "";
try
{
da.Fill(dt);
if (!string.IsNullOrEmpty(GetInputParamCalculatedValue("SetDataUsed")))
{
sSetDataUsed = @", " + GetInputParamCalculatedValue("SetDataUsed");
}
// we expect only 1 record
if (dt.Rows.Count == 1 && SelectAllRows == false)
{
DataRow r = dt.Rows[0];
//Read data to variables
foreach (string vc in varColMaps)
{
string strPrimaryKeyColumn = GetInputParamCalculatedValue("PrimaryKeyColumn");
if (strPrimaryKeyColumn.Contains("`"))
{
strPrimaryKeyColumn = strPrimaryKeyColumn.Replace("`", "");
}
string rowKey = r[strPrimaryKeyColumn].ToString();
int res;
int.TryParse(rowKey, out res);
if (res == 0 || r[strPrimaryKeyColumn].GetType() == typeof(System.String))
{
rowKey = "'" + rowKey + "'";
}
//TODO: fix me in OO Style
//Do mapping
string ColName = vc.Split('=')[0];
string Value = vc.Split('=')[1];
Value = Value.Replace("~^GINGER-EXCEL-COMMA-REPLACE^~", ",");
string txt = Value;
//keeping the translation of vars to support previous implementation
VariableBase var = RunOnBusinessFlow.GetHierarchyVariableByName(Value);
if (var != null)
{
var.Value = ValueExpression.Calculate(var.Value);
txt = var.Value;
}
//remove '' from value
txt = txt.TrimStart(new char[] { '\'' });
txt = txt.TrimEnd(new char[] { '\'' });
//TODO: create one long SQL to do the update in one time and not for each var
updateSQL = @"UPDATE [" + GetInputParamCalculatedValue("SheetName") + "$] SET " +
ColName + " = '" + txt + "'" + sSetDataUsed +
" WHERE " + GetInputParamCalculatedValue("PrimaryKeyColumn") + "=" + rowKey + ";";
this.ExInfo += updateSQL + Environment.NewLine;
OleDbCommand myCommand = new OleDbCommand();
myCommand.Connection = Conn;
myCommand.CommandText = updateSQL;
myCommand.ExecuteNonQuery();
}
// Do the update that row is used
}
else if (dt.Rows.Count > 0 && SelectAllRows == true)
{
updateSQL = @"UPDATE [" + GetInputParamCalculatedValue("SheetName") + "$] SET ";
foreach (string vc in varColMaps)
{
//TODO: fix me in OO Style
//Do mapping
string ColName = vc.Split('=')[0];
string Value = vc.Split('=')[1];
Value = Value.Replace("~^GINGER-EXCEL-COMMA-REPLACE^~", ",");
string txt = Value;
//keeping the translation of vars to support previous implementation
VariableBase var = RunOnBusinessFlow.GetHierarchyVariableByName(Value);
if (var != null)
{
var.Value = ValueExpression.Calculate(var.Value);
if (var != null)
{
txt = var.Value;
}
else
{
txt = Value;
}
}
//remove '' from value
txt = txt.TrimStart(new char[] { '\'' });
txt = txt.TrimEnd(new char[] { '\'' });
//TODO: create one long SQL to do the update in one time and not for each var
updateSQL = updateSQL + ColName + " = '" + txt + "',";
}
updateSQL = updateSQL.Substring(0, updateSQL.Length - 1);
updateSQL = updateSQL + sSetDataUsed;
if (!string.IsNullOrEmpty(where))
{
updateSQL += " WHERE " + where + ";";
}
this.ExInfo += updateSQL + Environment.NewLine;
OleDbCommand myCommand = new OleDbCommand();
myCommand.Connection = Conn;
myCommand.CommandText = updateSQL;
myCommand.ExecuteNonQuery();
}
else if (dt.Rows.Count == 0)
{
this.ExInfo = "No Rows updated with given criteria";
}
}
catch (Exception ex)
{
// Reporter.ToLog(eAppReporterLogLevel.ERROR, "Writing into excel got error " + ex.Message);
this.Error = "Error when trying to update the excel: " + ex.Message + Environment.NewLine + "UpdateSQL=" + updateSQL;
}
finally
{
Conn.Close();
}
// then show a message if needed
if (dt.Rows.Count == 0)
{
//TODO: reporter
// Reporter.ToUser("No rows found in excel file matching criteria - " + sql);
// throw new Exception("No rows found in excel file matching criteria - " + sql);
}
}
}
//**************************************************************************
/// <Description>
/// This method uses to update data to sys_ReportDrillDown
/// </Description>
/// <Inputs>
/// sys_ReportDrillDownVO
/// </Inputs>
/// <Outputs>
///
/// </Outputs>
/// <Returns>
///
/// </Returns>
/// <Authors>
/// HungLa
/// </Authors>
/// <History>
/// 09-Dec-2004
/// 12/Oct/2005 Thachnn: fix bug injection
/// </History>
/// <Notes>
/// </Notes>
//**************************************************************************
public void Update(object pobjObjecVO)
{
const string METHOD_NAME = THIS + ".Update()";
sys_ReportDrillDownVO objObject = (sys_ReportDrillDownVO)pobjObjecVO;
//prepare value for parameters
OleDbConnection oconPCS = null;
OleDbCommand ocmdPCS = null;
try
{
string strSql = String.Empty;
strSql = "UPDATE " + sys_ReportDrillDownTable.TABLE_NAME + " SET "
+ sys_ReportDrillDownTable.DETAILREPORTID_FLD + "= ?" + ","
+ sys_ReportDrillDownTable.MASTERPARA_FLD + "= ?" + ","
+ sys_ReportDrillDownTable.DETAILPARA_FLD + "= ?" + ","
+ sys_ReportDrillDownTable.FROMCOLUMN_FLD + "= ?" + ","
+ sys_ReportDrillDownTable.PARAORDER_FLD + "= ?"
+ " WHERE " + sys_ReportDrillDownTable.MASTERREPORTID_FLD + "= ?";
Utils utils = new Utils();
oconPCS = new OleDbConnection(Utils.Instance.OleDbConnectionString);
ocmdPCS = new OleDbCommand(strSql, oconPCS);
ocmdPCS.Parameters.Add(new OleDbParameter(sys_ReportDrillDownTable.DETAILREPORTID_FLD, OleDbType.VarWChar));
ocmdPCS.Parameters[sys_ReportDrillDownTable.DETAILREPORTID_FLD].Value = objObject.DetailReportID;
ocmdPCS.Parameters.Add(new OleDbParameter(sys_ReportDrillDownTable.MASTERPARA_FLD, OleDbType.VarWChar));
ocmdPCS.Parameters[sys_ReportDrillDownTable.MASTERPARA_FLD].Value = objObject.MasterPara;
ocmdPCS.Parameters.Add(new OleDbParameter(sys_ReportDrillDownTable.DETAILPARA_FLD, OleDbType.VarWChar));
ocmdPCS.Parameters[sys_ReportDrillDownTable.DETAILPARA_FLD].Value = objObject.DetailPara;
ocmdPCS.Parameters.Add(new OleDbParameter(sys_ReportDrillDownTable.FROMCOLUMN_FLD, OleDbType.Boolean));
ocmdPCS.Parameters[sys_ReportDrillDownTable.FROMCOLUMN_FLD].Value = objObject.FromColumn;
ocmdPCS.Parameters.Add(new OleDbParameter(sys_ReportDrillDownTable.PARAORDER_FLD, OleDbType.Integer));
ocmdPCS.Parameters[sys_ReportDrillDownTable.PARAORDER_FLD].Value = objObject.ParaOrder;
ocmdPCS.Parameters.Add(new OleDbParameter(sys_ReportDrillDownTable.MASTERREPORTID_FLD, OleDbType.VarWChar));
ocmdPCS.Parameters[sys_ReportDrillDownTable.MASTERREPORTID_FLD].Value = objObject.MasterReportID;
ocmdPCS.CommandText = strSql;
ocmdPCS.Connection.Open();
ocmdPCS.ExecuteNonQuery();
}
catch (OleDbException ex)
{
if (ex.Errors[1].NativeError == ErrorCode.SQLDUPLICATE_KEYCODE)
{
throw new PCSDBException(ErrorCode.DUPLICATE_KEY, METHOD_NAME, ex);
}
else
{
throw new PCSDBException(ErrorCode.ERROR_DB, METHOD_NAME, ex);
}
}
catch (InvalidOperationException ex)
{
throw new PCSDBException(ErrorCode.ERROR_DB, METHOD_NAME, ex);
}
catch (Exception ex)
{
throw new PCSDBException(ErrorCode.OTHER_ERROR, METHOD_NAME, ex);
}
finally
{
if (oconPCS != null)
{
if (oconPCS.State != ConnectionState.Closed)
{
oconPCS.Close();
}
}
}
}
private void btnSave_Click(object sender, EventArgs e)
{
String id = null;
String Ptype = null;
String pno = cmbPno.Text.ToString();
String mno = cmbMno.Text.ToString();
String category = cmbCname.Text.ToString();
String brand = cmbBname.Text.ToString();
String des = txtDes.Text.ToString();
String Pdes = txtDes.Text.ToString();
String unitVal = txtUnit.Text.ToString();
String CpriceVal = txtCost.Text.ToString();
String SpriceVal = txtSell.Text.ToString();
String roqVal = txtReOrder.Text.ToString();
int unit = int.Parse(unitVal);
double Cprice = double.Parse(CpriceVal);
double Sprice = double.Parse(SpriceVal);
int roq = int.Parse(roqVal);
if (rbStock.Checked)
{
Ptype = "s";
}
if (rbNStock.Checked)
{
Ptype = "ns";
}
if (rbService.Checked)
{
Ptype = "sr";
}
if (pno == String.Empty || mno == String.Empty || category == String.Empty || brand == String.Empty)
{
MessageBox.Show("At least one of the property values is invalid. ", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
else
{
DialogResult result = MessageBox.Show("Do you want to save data?", "", MessageBoxButtons.YesNo, MessageBoxIcon.Question);
if (result == DialogResult.Yes)
{
try
{
con.Open();
OleDbDataReader reader = null;
OleDbCommand cmd = new OleDbCommand("Select ID From Product WHERE PNo = '" + pno + "'", con);
reader = cmd.ExecuteReader();
while (reader.Read())
{
id = reader["ID"].ToString();
}
if (id == null)
{
OleDbCommand cmd2 = con.CreateCommand();
cmd2.CommandText = "INSERT INTO Product (PNo,Mno,Description,PDescription,Unit,Cprice,SellPrice,ROQ,PType,CName,BName) VALUES('" + pno + "','" + mno + "','" + des + "','" + Pdes + "'," + unit + "," + Cprice + "," + Sprice + "," + roq + ",'" + Ptype + "','" + category + "','" + brand + "')";
cmd2.Connection = con;
cmd2.ExecuteNonQuery();
}
else
{
OleDbCommand cmd2 = con.CreateCommand();
cmd2.CommandText = "UPDATE Product SET Mno ='" + mno + "', Description = '" + des + "', PDescription='" + Pdes + "', Unit=" + unit + ", Cprice=" + Cprice + ", SellPrice=" + Sprice + ", ROQ=" + roq + ", PType='" + Ptype + "', CName='" + category + "', BName='" + brand + "' WHERE PNo = '" + pno + "'";
cmd2.Connection = con;
cmd2.ExecuteNonQuery();
}
con.Close();
cmbPno.Enabled = true;
cmbPno.Text = null;
btnNew.Enabled = true;
btnDelete.Enabled = true;
this.Product_Load(null, null);
}
catch (Exception ex)
{
MessageBox.Show("error " + ex);
}
}
}
}
public int connectRemove(Student x, string idremove)
{
OleDbConnection connection = new OleDbConnection();
try
{
connection.ConnectionString = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\myFolder\myAccessFile.accdb;
Jet OLEDB:Database Password=MyDbPassword; Data Source=C:\Users\franc_000\Documents\College\StudentRegfinal\StudentReg\studentregdb.accdb; Persist Security Info = false; ";
var cmd = new OleDbCommand("DELETE FROM Student WHERE Id = @a;"); //need correct reference
connection.Open();
cmd.Parameters.AddRange(new[] {
new OleDbParameter("@a", x.Id)
});
cmd.Connection = connection;
cmd.ExecuteNonQuery();
Console.WriteLine("Student removed successfully.");
}
catch(Exception ex)
{
Console.WriteLine("Error " + ex);
}
connection.Close();
return 0;
}
//森林用途研判
private static void tdytJudge(IFeatureLayer pChangeFeaLayer, IFeatureLayer tdytFeaLayer, IWorkspace pOutWorkSpace, bool useSelection,SysCommon.CProgress vProgress)
{
ITable pChangeTable = pChangeFeaLayer as ITable;
ITable tdytTable = tdytFeaLayer as ITable;
double rol = 0.0001;
IFeatureClass tdytFeaClass = tdytFeaLayer.FeatureClass;
//创建结果地物类名称
IFeatureClassName pResFeaClassName = new FeatureClassNameClass();
String fcName = tdytFeaClass.AliasName.Trim().Substring(tdytFeaClass.AliasName.Trim().IndexOf(".") + 1)+"_res";
IDataset pOutDataset = (IDataset)pOutWorkSpace;
IDatasetName pOutDatasetName = (IDatasetName)pResFeaClassName;
pOutDatasetName.WorkspaceName = (IWorkspaceName)pOutDataset.FullName;
pOutDatasetName.Name = fcName;
IBasicGeoprocessor pGeoProcessor = new BasicGeoprocessorClass();
//叠置分析
pGeoProcessor.Intersect(pChangeTable, useSelection, tdytTable, false, rol, pResFeaClassName);
//从叠置结果生成报表
string connstr = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + pOutDatasetName.WorkspaceName.PathName;
OleDbConnection oledbconn = new OleDbConnection(connstr);
oledbconn.Open();
ModTableFun.DropTable(oledbconn,"tmprel");
string sqlstr = "select "+g_XZQHDM+","+g_TBBH+","+g_MJ+" as jctbmj,"+g_TDYTFQBH+","+g_TDYTFQDM+",shape_area as jsmj,shape_area as mj,shape_area as zb into tmprel from " + fcName;
//行政区划代码，编号，监测图斑面积，规划图斑编号，规划用地用途代码
OleDbCommand oledbcomm = oledbconn.CreateCommand();
oledbcomm.CommandText = sqlstr;
oledbcomm.ExecuteNonQuery();
oledbcomm.CommandText = "update tmprel set zb=mj/jctbmj*100";
oledbcomm.ExecuteNonQuery();
ModTableFun.DropTable(oledbconn ,"森林用途字典");
CopyTdytDictionary(oledbconn);//从业务库里面拷贝森林用途字典过来
//根据森林用途字典更新森林用途名称
oledbcomm.CommandText = "alter table tmprel add tdytmc text(30)";
oledbcomm.ExecuteNonQuery();
oledbcomm.CommandText = "update tmprel set tdytmc=" + g_TDYTFQDM;
oledbcomm.ExecuteNonQuery();
if (ModTableFun.isExist(oledbconn,"森林用途字典"))
{
oledbcomm.CommandText = "update tmprel a,森林用途字典 b set a.tdytmc=b.森林用途分区类型 where a." + g_TDYTFQDM + "=b.代码";
oledbcomm.ExecuteNonQuery();
}
//报表模板路径
string Templatepath = Application.StartupPath + "\\..\\Template\\森林资源规划研判模板.cel";
//生成报表对话框
oledbconn.Close();
FormFlexcell frm;
ModFlexcell.m_SpecialRow = -1;
ModFlexcell.m_SpecialRow_ex = -1;
ModFlexcell.m_SpecialRow_ex2 = -1;
//规划图斑没有图斑编号怎么办？？暂时使用森林用途分区编号
frm = ModFlexcell.SendDataToFlexcell(connstr, "监测图斑规划情况表", "tmprel", g_XZQHDM + "," + g_TBBH + ",jctbmj,TDYTFQBH,tdytmc," + g_MJ + "," + g_ZB, "", Templatepath, 4, 2);
//弹出报表对话框
AxFlexCell.AxGrid pGrid = frm.GetGrid();
string excelPath = m_WorkPath + "\\监测图斑规划情况表.xls";
pGrid.ExportToExcel(excelPath);
//frm.SaveFile(m_WorkPath + "\\监测图斑规划情况表.cel");
ModStatReport.OpenExcelFile(excelPath);
}
public void writeFail(string field, string value)//写入失败统计
{
cmd = new OleDbCommand("update sheet3 set " + field + "='" + value + "'where ID=1", conn);
cmd.ExecuteNonQuery();
}
public void AddtoBasket(string userID, string PlayChosen, float Standard, float Child, float OAP, DateTime PlayDate, string Time, string Price, float Quantity)
{
string checkString;
float checkValue;
//needs to write to database
string connString;
connString = @"Provider=Microsoft.JET.OLEDB.4.0;Data Source = L:\Comp-1632-System Development Project\TicketBookingSystem\TicketBookingSystem\TicketSysDB.mdb";
OleDbConnection myConnection = new OleDbConnection(connString);
myConnection.Open();
OleDbCommand checkCommand = new OleDbCommand("SELECT TicketsQuantity FROM Plays WHERE Title = @Title AND DateOfPlay = @DateOfPlay AND TimeOfPlay = @TimeOfPlay", myConnection);
checkCommand.Parameters.AddWithValue("@Title", PlayChosen);
checkCommand.Parameters.AddWithValue("@DateOfPlay", PlayDate);
checkCommand.Parameters.AddWithValue("@TimeOfPlay", Time);
OleDbDataReader reader2 = checkCommand.ExecuteReader();
while (reader2.Read())
{
checkString = reader2.GetString(0);
checkValue = Convert.ToInt32(checkString);
if ((checkValue - Quantity) >= 0)
{
OleDbCommand myCommand = new OleDbCommand("INSERT INTO [Basket] (UserID, PlayChosen, StandardNo, ChildNo, OAPNo, PlayDate, PlayTime, Price, Quantity) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)", myConnection);
myCommand.Parameters.AddWithValue("@UserID", userID);
myCommand.Parameters.AddWithValue("@PlayChosen", PlayChosen);
myCommand.Parameters.AddWithValue("@Standard", Standard);
myCommand.Parameters.AddWithValue("@Child", Child);
myCommand.Parameters.AddWithValue("@OAP", OAP);
myCommand.Parameters.AddWithValue("@PlayDate", PlayDate);
myCommand.Parameters.AddWithValue("@PlayTime", Time);
myCommand.Parameters.AddWithValue("@Price", Price);
myCommand.Parameters.AddWithValue("@Quantity", Quantity);
myCommand.ExecuteNonQuery();
success = 1;
OleDbCommand decrementCommand = new OleDbCommand("UPDATE Plays SET TicketsQuantity = @TicketsQuantity WHERE Title = @Title AND DateOfPlay = @DateOfPlay AND TimeOfPlay = @TimeOfPlay", myConnection);
decrementCommand.Parameters.AddWithValue("@TicketsQuantity", (checkValue - Quantity));
decrementCommand.Parameters.AddWithValue("@Title", PlayChosen);
decrementCommand.Parameters.AddWithValue("@DateOfPlay", PlayDate);
decrementCommand.Parameters.AddWithValue("@TimeOfPlay", Time);
decrementCommand.ExecuteNonQuery();
}
else
{
success = 2;
}
}
myConnection.Close();
}
private void sendUpdateBtn_Click(object sender, EventArgs e)
{
List <Student.Mark> marks = new List <Student.Mark>();
List <Student.Miss> misses = new List <Student.Miss>();
int resUpdMark, resAddMark, resDelMark, resAddSkip, resDelSkip;
resUpdMark = resAddMark = resDelMark = resAddSkip = resDelSkip = 0;
for (int i = 0; i < journalGrid.RowCount; i++)
{
for (int j = 2; j < journalGrid.ColumnCount; j++)
{
if (journalGrid[j, i].Value != null)
{
int idStudent = (int)journalGrid.Rows[i].Cells[0].Value;
string date = journalGrid.Columns[j].HeaderText;
string queryMark = "Select * " +
"From Marks " +
$"Where idStudent = {idStudent} And Marks.dateMark = @date And idSub = {subjectBox.SelectedValue}";
OleDbCommand commandMark = new OleDbCommand(queryMark, _connection);
commandMark.Parameters.Add(new OleDbParameter("@data", date));
var readerMark = commandMark.ExecuteReader();
string querySkip = "Select * " +
"From SkipLesson " +
$"Where idStudent = {idStudent} And dateSkip = @date And idSub = {subjectBox.SelectedValue}";
OleDbCommand commandSkip = new OleDbCommand(querySkip, _connection);
var paramDateSkip = new OleDbParameter("@date", date);
commandSkip.Parameters.Add(paramDateSkip);
var readerSkip = commandSkip.ExecuteReader();
if (Char.IsDigit(journalGrid[j, i].Value.ToString()[0]))
{
string queryCheckMark = "Select * " +
"From Marks " +
$"Where idStudent = {idStudent} And Marks.dateMark = @date And Marks.valueMark = {journalGrid[j, i].Value} And idSub = {subjectBox.SelectedValue}";
OleDbCommand cmdCheckMark = new OleDbCommand(queryCheckMark, _connection);
OleDbParameter param = new OleDbParameter("@date", date);
cmdCheckMark.Parameters.Add(param);
var checker = cmdCheckMark.ExecuteReader();
cmdCheckMark.Parameters.Clear();
if (checker.HasRows)
{
continue;
}
if (readerMark.HasRows)
{
string query = $"Update Marks " +
$"Set Marks.valueMark = {journalGrid[j, i].Value} " +
$"Where idStudent = { journalGrid.Rows[i].Cells[0].Value } And Marks.dateMark = @date And idSub = { subjectBox.SelectedValue }";
OleDbCommand updateCmd = new OleDbCommand(query, _connection);
OleDbParameter paramUpdateMark =
updateCmd.Parameters.Add(new OleDbParameter("@date", date));
resUpdMark += updateCmd.ExecuteNonQuery();
}
else if (readerSkip.HasRows)
{
readerSkip.Read();
string query = $"Delete From SkipLesson Where SkipLesson.id = {readerSkip["id"]}";
OleDbCommand deleteSkip = new OleDbCommand(query, _connection);
resDelSkip += deleteSkip.ExecuteNonQuery();
}
else
{
string query = $"Insert Into Marks(idStudent, idSub, dateMark, valueMark) " +
$"Values({idStudent}, {subjectBox.SelectedValue}, '{journalGrid.Columns[j].HeaderText}', {journalGrid[j, i].Value})";
OleDbCommand addCommand = new OleDbCommand(query, _connection);
resAddMark += addCommand.ExecuteNonQuery();
}
}
else if (journalGrid[j, i].Value.ToString() == "Н")
{
if (readerMark.HasRows)
{
string query = $"Delete From Marks " +
$"Where idStudent = {idStudent} And Marks.dateMark = @date And idSub = { subjectBox.SelectedValue }";
OleDbCommand deleteMarkCmd = new OleDbCommand(query, _connection);
deleteMarkCmd.Parameters.Add(new OleDbParameter("@date", date));
resDelMark += deleteMarkCmd.ExecuteNonQuery();
string addSkipQuery = "Insert Into SkipLesson(idSub, idStudent, dateSkip) " +
$"Values( {subjectBox.SelectedValue}, {idStudent}, '{date}')";
OleDbCommand addSkipCmd = new OleDbCommand(addSkipQuery, _connection);
resAddSkip += addSkipCmd.ExecuteNonQuery();
}
else if (readerSkip.HasRows)
{
continue;
}
else
{
string query = $"Insert Into SkipLesson(idStudent, idSub, dateSkip) " +
$"Values({idStudent}, {subjectBox.SelectedValue}, '{date}')";
OleDbCommand addCommand = new OleDbCommand(query, _connection);
resAddSkip += addCommand.ExecuteNonQuery();
}
}
else if (journalGrid[j, i].Value.ToString() == "none")
{
if (readerMark.HasRows)
{
readerMark.Read();
string query = $"Delete From Marks Where id = {readerMark["id"]}";
OleDbCommand deleteMarkCmd = new OleDbCommand(query, _connection);
resDelMark = deleteMarkCmd.ExecuteNonQuery();
}
if (readerSkip.HasRows)
{
readerSkip.Read();
string query = $"Delete From SkipLesson Where id = {readerSkip["id"]}";
OleDbCommand deleteSkipCmd = new OleDbCommand(query, _connection);
resDelSkip = deleteSkipCmd.ExecuteNonQuery();
}
}
}
}
}
MessageBox.Show($@"
Обновлено отметок: {resUpdMark}
Добавлено отметок: {resAddMark}
Удалено отметок : {resDelMark}
Добавлено пропусков: {resAddSkip}
Удалено пропусков: {resDelSkip}
", "Статистика");
}
//**************************************************************************
/// <Description>
/// This method uses to update data to MST_EmployeeApprovalLevel
/// </Description>
/// <Inputs>
/// MST_EmployeeApprovalLevelVO
/// </Inputs>
/// <Outputs>
///
/// </Outputs>
/// <Returns>
///
/// </Returns>
/// <Authors>
/// HungLa
/// </Authors>
/// <History>
/// 09-Dec-2004
/// </History>
/// <Notes>
/// </Notes>
//**************************************************************************
public void Update(object pobjObjecVO)
{
const string METHOD_NAME = THIS + ".Update()";
MST_EmployeeApprovalLevelVO objObject = (MST_EmployeeApprovalLevelVO)pobjObjecVO;
//prepare value for parameters
OleDbConnection oconPCS = null;
OleDbCommand ocmdPCS = null;
try
{
string strSql = String.Empty;
Utils utils = new Utils();
oconPCS = new OleDbConnection(Utils.Instance.OleDbConnectionString);
ocmdPCS = new OleDbCommand(strSql, oconPCS);
strSql = "UPDATE MST_EmployeeApprovalLevel SET "
+ MST_EmployeeApprovalLevelTable.EMPLOYEEAPPROVALLEVELID_FLD + "= ?" + ","
+ MST_EmployeeApprovalLevelTable.EMPLOYEEID_FLD + "= ?" + ","
+ MST_EmployeeApprovalLevelTable.APPROVALLEVELID_FLD + "= ?"
+ " WHERE " + MST_EmployeeApprovalLevelTable.DESCRIPTION_FLD + "= ?";
ocmdPCS.Parameters.Add(new OleDbParameter(MST_EmployeeApprovalLevelTable.EMPLOYEEAPPROVALLEVELID_FLD, OleDbType.Integer));
ocmdPCS.Parameters[MST_EmployeeApprovalLevelTable.EMPLOYEEAPPROVALLEVELID_FLD].Value = objObject.EmployeeApprovalLevelID;
ocmdPCS.Parameters.Add(new OleDbParameter(MST_EmployeeApprovalLevelTable.EMPLOYEEID_FLD, OleDbType.Integer));
ocmdPCS.Parameters[MST_EmployeeApprovalLevelTable.EMPLOYEEID_FLD].Value = objObject.EmployeeID;
ocmdPCS.Parameters.Add(new OleDbParameter(MST_EmployeeApprovalLevelTable.APPROVALLEVELID_FLD, OleDbType.Integer));
ocmdPCS.Parameters[MST_EmployeeApprovalLevelTable.APPROVALLEVELID_FLD].Value = objObject.ApprovalLevelID;
ocmdPCS.Parameters.Add(new OleDbParameter(MST_EmployeeApprovalLevelTable.DESCRIPTION_FLD, OleDbType.VarWChar));
ocmdPCS.Parameters[MST_EmployeeApprovalLevelTable.DESCRIPTION_FLD].Value = objObject.Description;
ocmdPCS.CommandText = strSql;
ocmdPCS.Connection.Open();
ocmdPCS.ExecuteNonQuery();
}
catch (OleDbException ex)
{
if (ex.Errors[1].NativeError == ErrorCode.SQLDUPLICATE_KEYCODE)
{
throw new PCSDBException(ErrorCode.DUPLICATE_KEY, METHOD_NAME, ex);
}
else
{
throw new PCSDBException(ErrorCode.ERROR_DB, METHOD_NAME, ex);
}
}
catch (InvalidOperationException ex)
{
throw new PCSDBException(ErrorCode.ERROR_DB, METHOD_NAME, ex);
}
catch (Exception ex)
{
throw new PCSDBException(ErrorCode.OTHER_ERROR, METHOD_NAME, ex);
}
finally
{
if (oconPCS != null)
{
if (oconPCS.State != ConnectionState.Closed)
{
oconPCS.Close();
}
}
}
}
public void Execute(string sSQL)
{
OleDbCommand cmd = new OleDbCommand(sSQL, Connection);
cmd.ExecuteNonQuery();
}
//**************************************************************************
/// <Description>
/// This method uses to add data to MST_EmployeeApprovalLevel
/// </Description>
/// <Inputs>
/// MST_EmployeeApprovalLevelVO
/// </Inputs>
/// <Outputs>
/// newly inserted primarkey value
/// </Outputs>
/// <Returns>
/// void
/// </Returns>
/// <Authors>
/// HungLa
/// </Authors>
/// <History>
/// Tuesday, January 25, 2005
/// </History>
/// <Notes>
/// </Notes>
//**************************************************************************
public void Add(object pobjObjectVO)
{
const string METHOD_NAME = THIS + ".Add()";
OleDbConnection oconPCS = null;
OleDbCommand ocmdPCS = null;
try
{
MST_EmployeeApprovalLevelVO objObject = (MST_EmployeeApprovalLevelVO)pobjObjectVO;
string strSql = String.Empty;
Utils utils = new Utils();
oconPCS = new OleDbConnection(Utils.Instance.OleDbConnectionString);
ocmdPCS = new OleDbCommand("", oconPCS);
strSql = "INSERT INTO MST_EmployeeApprovalLevel("
+ MST_EmployeeApprovalLevelTable.EMPLOYEEAPPROVALLEVELID_FLD + ","
+ MST_EmployeeApprovalLevelTable.EMPLOYEEID_FLD + ","
+ MST_EmployeeApprovalLevelTable.APPROVALLEVELID_FLD + ")"
+ "VALUES(?,?,?)";
ocmdPCS.Parameters.Add(new OleDbParameter(MST_EmployeeApprovalLevelTable.EMPLOYEEAPPROVALLEVELID_FLD, OleDbType.Integer));
ocmdPCS.Parameters[MST_EmployeeApprovalLevelTable.EMPLOYEEAPPROVALLEVELID_FLD].Value = objObject.EmployeeApprovalLevelID;
ocmdPCS.Parameters.Add(new OleDbParameter(MST_EmployeeApprovalLevelTable.EMPLOYEEID_FLD, OleDbType.Integer));
ocmdPCS.Parameters[MST_EmployeeApprovalLevelTable.EMPLOYEEID_FLD].Value = objObject.EmployeeID;
ocmdPCS.Parameters.Add(new OleDbParameter(MST_EmployeeApprovalLevelTable.APPROVALLEVELID_FLD, OleDbType.Integer));
ocmdPCS.Parameters[MST_EmployeeApprovalLevelTable.APPROVALLEVELID_FLD].Value = objObject.ApprovalLevelID;
ocmdPCS.CommandText = strSql;
ocmdPCS.Connection.Open();
ocmdPCS.ExecuteNonQuery();
}
catch (OleDbException ex)
{
if (ex.Errors[1].NativeError == ErrorCode.SQLDUPLICATE_KEYCODE)
{
throw new PCSDBException(ErrorCode.DUPLICATE_KEY, METHOD_NAME, ex);
}
else
{
throw new PCSDBException(ErrorCode.ERROR_DB, METHOD_NAME, ex);
}
}
catch (InvalidOperationException ex)
{
throw new PCSDBException(ErrorCode.ERROR_DB, METHOD_NAME, ex);
}
catch (Exception ex)
{
throw new PCSDBException(ErrorCode.OTHER_ERROR, METHOD_NAME, ex);
}
finally
{
if (oconPCS != null)
{
if (oconPCS.State != ConnectionState.Closed)
{
oconPCS.Close();
}
}
}
}
protected void Page_Load(object sender, EventArgs e)
{
int i = 0;
if (Session["tryCount"] == null)//用户名或密码错误次数不得超过一定界限
{
Session["tryCount"] = 1;
}
if (Convert.ToInt32(Session["tryCount"]) > 50)
{
i = 2;
}
else
{
if (Request.Form["uid"] != null && Request.Form["pwd"] != null)
{
try
{
string username = SqlString.keepoutSqlKey(Request.Form["uid"]);
string password = SqlString.keepoutSqlKey(Request.Form["pwd"]);
OleDbConnection con = DB.createcon();
string sqlstr = "select * from [student] where [studentUsername]='" + username + "' and ([studentPassword]='" + password + "' or [studentPassword]='" + MD5_encode(password) + "')";
con.Open();
OleDbCommand cmd = new OleDbCommand();
cmd.CommandText = sqlstr;
cmd.Connection = con;
OleDbDataReader sdr = cmd.ExecuteReader();
while (sdr.Read())
{
string role = sdr["roleType"].ToString();
string realname = sdr["studentName"].ToString();
password = sdr["studentPassword"].ToString();
Session["userType"] = role;
Session["realName"] = realname;
Session["userName"] = username;
cookieOperation.setOneCookie("userType", role);
cookieOperation.setOneCookie("realName", realname);
cookieOperation.setOneCookie("userName", username);
string logincode = MD5Encode.docubleMD5_encode(username, password);
cookieOperation.setOneCookie("loginCode", logincode);
i = 1;
}
sdr.Close();
con.Close();
if (i == 1)
{
string mydatetime = DateTime.Now.ToString();
string ip = Request.ServerVariables["REMOTE_HOST"].ToString();
string sqlstr2 = "update [student] set loginCount=loginCount+1,lastLoinTime='" + mydatetime + "',lastLoinIP='" + ip + "' where studentUsername='******'";
cmd.CommandText = sqlstr2;
con.Open();
cmd.ExecuteNonQuery();
con.Close();
switch (Session["userType"].ToString())
{
case "2":
countOperation.countAddOne("adminLoginCount");
countOperation.countAddOne("onlineStudent");
systemRecord.insertOneRecord("管理员“" + username + "“登陆成功！");
break;
case "3":
countOperation.countAddOne("superAdminLoginCount");
countOperation.countAddOne("onlineStudent");
systemRecord.insertOneRecord("超级管理员“" + username + "“登陆成功！");
break;
default:
countOperation.countAddOne("onlineStudent");
systemRecord.insertOneRecord("学生“" + username + "“登陆成功！");
break;
}
}
}
catch (Exception exp)
{
saveErrorMessage.writeFile("用户登陆时发生错误！", exp.ToString());
i = 0;
}
}
}
if (i == 0)
{
Session["tryCount"] = Convert.ToInt32(Session["tryCount"]) + 1;
}
Response.Write(i.ToString());
}
private void button11_Click(object sender, EventArgs e)
{
if (button2.BackColor == Color.Green)
{
button2.BackColor = Color.Red;
}
if (button3.BackColor == Color.Green)
{
button3.BackColor = Color.Red;
}
if (button4.BackColor == Color.Green)
{
button4.BackColor = Color.Red;
}
if (button5.BackColor == Color.Green)
{
button5.BackColor = Color.Red;
}
if (button6.BackColor == Color.Green)
{
button6.BackColor = Color.Red;
}
if (button7.BackColor == Color.Green)
{
button7.BackColor = Color.Red;
}
if (button8.BackColor == Color.Green)
{
button8.BackColor = Color.Red;
}
if (button9.BackColor == Color.Green)
{
button9.BackColor = Color.Red;
}
label12.Text = "";
connection.Open();
OleDbCommand command = new OleDbCommand();
command.Connection = connection;
String qry = "insert into Booking(BookingDate, CustomerName) values(?,?)";
command.CommandText = qry;
command.Parameters.AddWithValue("@p1", DateTime.Now.ToString("h:mm:ss"));
command.Parameters.AddWithValue("@p2", textBox1.Text);
command.ExecuteNonQuery();
connection.Close();
connection.Open();
qry = "select BookingID from Booking where CustomerName ='" + textBox1.Text + "'";
command.CommandText = qry;
//command.Parameters.AddWithValue("@p1", textBox1.Text);
OleDbDataReader reader = command.ExecuteReader();
while (reader.Read())
{
BookingID = (int)reader[0];
}
connection.Close();
connection.Open();
OleDbCommand command2 = new OleDbCommand();
command2.Connection = connection;
for (int i = 0; i < bookSeats.Count; i++)
{
qry = "insert into BookSeat(BookingID, Price, ScheduleStationID, Source, Des, SeatNumber, CarNumber) values(?,?,?,?,?,'" + bookSeats[i] + "',?)";
command2.CommandText = qry;
command2.Parameters.AddWithValue("@p1", BookingID);
command2.Parameters.AddWithValue("@p2", 10);
if (comboBox1.SelectedIndex > comboBox2.SelectedIndex)
{
command2.Parameters.AddWithValue("@p3", ssID[1]);
}
command2.Parameters.AddWithValue("@p4", comboBox1.SelectedItem);
command2.Parameters.AddWithValue("@p5", comboBox2.SelectedItem);
//command2.Parameters.AddWithValue("@p6", bookSeats[i]);
command2.Parameters.AddWithValue("@p6", CarNumber);
command2.ExecuteNonQuery();
}
connection.Close();
MessageBox.Show("Booked!");
}
private void button8_Click(object sender, EventArgs e)
{
for (int v = 0; v < MyData.Tables[0].Rows.Count; v++)
{
// MessageBox.Show("run");
if (this.a0.Text == MyData.Tables[0].Rows[v][0].ToString())
{
string SQL = "delete from dtsbz where a0= '" + this.a0.Text + "'";
OleDbCommand MyCom = new OleDbCommand(SQL, MyConn);
MyCom.ExecuteNonQuery();
MyData.Tables[0].Rows.RemoveAt(v);
break;
}
}
panel2.Enabled = true;
/*
* word 存储开始
*
*/
//代码域
Thread MySaveS = new Thread(new ThreadStart(save));
MySaveS.Start();
/*
* word存储结束
*/
/*
* 数据库存储开始
*/
DataRow NewRow = MyData.Tables[0].NewRow();
NewRow["a0"] = a0.Text;
NewRow["a1"] = a1.Text;
NewRow["a2"] = a2.Text;
NewRow["a3"] = a3.Text;
NewRow["a4"] = a4.Text;
NewRow["a5"] = a5.Text;
NewRow["a6"] = a6.Text;
NewRow["a7"] = a7.Text;
NewRow["a8"] = a8.Text;
NewRow["a9"] = a9.Text;
NewRow["a10"] = a10.Text;
NewRow["a11"] = a11.Text;
NewRow["a12"] = a12.Text;
NewRow["a13"] = a13.Text;
NewRow["a14"] = a14.Text;
NewRow["a15"] = a15.Text;
NewRow["time_e"] = main.currentTime.Year.ToString() + "-" + main.currentTime.Month.ToString() + "-" + main.currentTime.Day.ToString();
MyData.Tables[0].Rows.Add(NewRow);
MyAd.Update(MyData);
//MessageBox.Show(a13_1.CheckState);
/*
* 数据库的存储结束
*
* *
*/
MessageBox.Show("保存成功！！");
i = MyData.Tables[0].Rows.Count - 1;
textBox18.Text = (i + 1).ToString();
panel1.Enabled = false;
button6.Enabled = true;
button7.Enabled = true;
button8.Enabled = false;
button9.Enabled = false;
button10.Enabled = true;
button11.Enabled = true;
button12.Enabled = true;
button13.Enabled = true;
button14.Enabled = true;
button15.Enabled = true;
button16.Enabled = true;
button17.Enabled = true;
OleDbDataAdapter MyAd0 = new OleDbDataAdapter("select count(a0) from dtsbz ", MyConn);
DataSet MyData0 = new DataSet();
MyAd0.Fill(MyData0);
this.textBox17.Text = MyData.Tables[0].Rows.Count.ToString();
}
private void button2_Click(object sender, EventArgs e)
{
double to = 0;
double t1 = 0;
double t2 = 0;
double tf = 0;
double bro = 0;
double pay = 0;
double cut1;
double S_P = 0;
flage = true;
if (dataGridView1.Rows.Count - 1 == 0)
{
flage = false;
}
check();
if (flage == true)
{
com = connction.CreateCommand();
com.CommandType = CommandType.Text;
try
{
com.CommandText = "insert into Info_Cost(Name_C,Da,Address_C,discount,pay,bro,Final_price,S_P,nodes) Values('" + textBox2.Text + "','" + dateTimePicker1.Text + "','" + textBox3.Text + "'," + 0 + "," + 0 + "," + 0 + "," + 0 + "," + 0 + ",'" + textBox5.Text + "')";
connction.Open();
com.ExecuteNonQuery();
connction.Close();
da1 = new OleDbDataAdapter("select Max(ID_C)from Info_Cost Where Name_C like '" + textBox2.Text + "%'", connction);
dt1 = new DataTable();
da1.Fill(dt1);
if (dt1.Rows.Count > 0)
{
textBox1.Text = dt1.Rows[0][0].ToString();
}
for (int i = 0; i < dataGridView1.Rows.Count - 1; i++)
{
t1 = Convert.ToDouble(dataGridView1.Rows[i].Cells[2].Value);
t2 = Convert.ToDouble(dataGridView1.Rows[i].Cells[3].Value);
to = t2 * t1;
tf = tf + to;
com.CommandText = "insert into Menu_Cost(ID_C,ID_O,Name_Object,No_Object,Price_object,Total_price) Values(" + Convert.ToInt32(textBox1.Text) + "," + (i + 1) + ",'" + dataGridView1.Rows[i].Cells[1].Value + "'," + dataGridView1.Rows[i].Cells[2].Value + "," + dataGridView1.Rows[i].Cells[3].Value + "," + to + ")";
dataGridView1.Rows[i].Cells[0].Value = (i + 1);
dataGridView1.Rows[i].Cells[5].Value = (to);
connction.Open();
com.ExecuteNonQuery();
connction.Close();
}
if (textBox6.Text == "")
{
cut1 = 0;
}
else
{
cut1 = Convert.ToDouble(textBox6.Text);
}
S_P = (tf - cut1);
pay = Convert.ToInt32(Microsoft.VisualBasic.Interaction.InputBox(":الرجاء ادخال المبلغ الواصل من قبل الزبون", "ادخال المبلغ", S_P.ToString()));
bro = S_P - pay;
com.CommandText = "update Info_Cost set discount=" + cut1 + ",pay=" + pay + ",bro=" + bro + ",Final_price=" + tf + ",S_P=" + S_P + " where ID_C=" + Convert.ToInt32(textBox1.Text) + "";
connction.Open();
com.ExecuteNonQuery();
connction.Close();
textBox8.Text = pay.ToString();
textBox4.Text = S_P.ToString();
textBox7.Text = tf.ToString();
textBox9.Text = bro.ToString();
MessageBox.Show("تم حفظ البيانات بنجاح");
button2.Enabled = false;
textBox2.ReadOnly = true;
textBox3.ReadOnly = true;
textBox6.ReadOnly = true;
dataGridView1.ReadOnly = true;
button3.Text = "قائمة جديدة";
}
catch (Exception ex)
{
MessageBox.Show("هناك نقص في البيانات المدخلة يرجى مراجع البيانات", "خطا في الادخال");
Console.WriteLine(ex.Message);
}
}
else
{
MessageBox.Show("هناك نقص اما باسم الزبون او بنوع المادة او العدد او سعر المفرد!!الرجاء ادخال البيانات كاملة هناك");
}
}
public void ReadData()
{
//TODO: check what is required on the machine and maybe support for other versions
string ConnString = GetConnectionString();
string sql = "";
using (OleDbConnection Conn = new OleDbConnection(ConnString))
{
try
{
Conn.Open();
}
catch (Exception ex)
{
System.Threading.Thread.Sleep(3000);
Conn.Open();
Reporter.ToLog(eLogLevel.ERROR, $"Method - {MethodBase.GetCurrentMethod().Name}, Error - {ex.StackTrace}", ex);
}
OleDbCommand Cmd = new OleDbCommand();
Cmd.Connection = Conn;
string SheetName = GetInputParamCalculatedValue("SheetName").Trim();
if (!SheetName.EndsWith("$"))
{
SheetName += "$";
}
if (SelectAllRows == false)
{
sql = "Select TOP 1 * from [" + SheetName + "]";
}
else
{
sql = "Select * from [" + SheetName + "]";
}
string where = GetInputParamCalculatedValue("SelectRowsWhere");
if (!string.IsNullOrEmpty(where))
{
sql += " WHERE " + where;
}
Cmd.CommandText = sql;
DataTable dt = new DataTable();
OleDbDataAdapter da = new OleDbDataAdapter();
da.SelectCommand = Cmd;
try
{
da.Fill(dt);
// we expect only 1 record
if (dt.Rows.Count == 1 && SelectAllRows == false)
{
DataRow r = dt.Rows[0];
for (int i = 0; i < dt.Columns.Count; i++)
{
AddOrUpdateReturnParamActual(dt.Columns[i].ColumnName, ((object)r[i]).ToString());
}
if (!String.IsNullOrEmpty(GetInputParamCalculatedValue("SetDataUsed")))
{
string rowKey = r[GetInputParamCalculatedValue("PrimaryKeyColumn")].ToString();
string updateSQL = @"UPDATE [" + GetInputParamCalculatedValue("SheetName") + "$] SET " + GetInputParamCalculatedValue("SetDataUsed") + " WHERE " + GetInputParamCalculatedValue("PrimaryKeyColumn") + "=" + rowKey + ";";
OleDbCommand myCommand = new OleDbCommand();
myCommand.Connection = Conn;
myCommand.CommandText = updateSQL;
myCommand.ExecuteNonQuery();
}
}
else if (dt.Rows.Count > 0 && SelectAllRows == true)
{
for (int j = 0; j < dt.Rows.Count; j++)
{
DataRow r = dt.Rows[j];
for (int i = 0; i < dt.Columns.Count; i++)
{
AddOrUpdateReturnParamActualWithPath(dt.Columns[i].ColumnName, ((object)r[i]).ToString(), "" + (j + 1).ToString());
}
}
if (!String.IsNullOrEmpty(GetInputParamCalculatedValue("SetDataUsed")))
{
string updateSQL = @"UPDATE [" + GetInputParamCalculatedValue("SheetName") + "$] SET " + GetInputParamCalculatedValue("SetDataUsed");
if (!string.IsNullOrEmpty(where))
{
updateSQL += " WHERE " + where + ";";
}
OleDbCommand myCommand = new OleDbCommand();
myCommand.Connection = Conn;
myCommand.CommandText = updateSQL;
myCommand.ExecuteNonQuery();
}
}
else if (dt.Rows.Count != 1 && SelectAllRows == false)
{
Status = Amdocs.Ginger.CoreNET.Execution.eRunStatus.Failed;
Error = "Excel Query should return only one row" + Environment.NewLine + sql + Environment.NewLine + "Returned: " + dt.Rows.Count + " Records";
}
}
catch (Exception ex)
{
this.Status = Amdocs.Ginger.CoreNET.Execution.eRunStatus.Failed;
Error = ex.Message;
}
finally
{
Conn.Close();
}
if (dt.Rows.Count == 0)
{
this.Status = Amdocs.Ginger.CoreNET.Execution.eRunStatus.Failed;
Error = "No rows found in excel file matching criteria - " + sql;
}
}
}
/// <summary>
/// This method uses to add data to MST_WorkingDayDetail
/// </summary>
/// <Inputs>
/// MST_WorkingDayDetailVO
/// </Inputs>
/// <Returns>
/// void
/// </Returns>
/// <History>
/// Thursday, July 21, 2005
/// </History>
public void Add(object pobjObjectVO)
{
const string METHOD_NAME = THIS + ".Add()";
OleDbConnection oconPCS = null;
OleDbCommand ocmdPCS = null;
try
{
MST_WorkingDayDetailVO objObject = (MST_WorkingDayDetailVO)pobjObjectVO;
string strSql = String.Empty;
Utils utils = new Utils();
oconPCS = new OleDbConnection(Utils.Instance.OleDbConnectionString);
ocmdPCS = new OleDbCommand("", oconPCS);
strSql = "INSERT INTO MST_WorkingDayDetail("
+ MST_WorkingDayDetailTable.OFFDAY_FLD + ","
+ MST_WorkingDayDetailTable.COMMENT_FLD + ","
+ MST_WorkingDayDetailTable.WORKINGDAYMASTERID_FLD + ")"
+ "VALUES(?,?,?)";
ocmdPCS.Parameters.Add(new OleDbParameter(MST_WorkingDayDetailTable.OFFDAY_FLD, OleDbType.Date));
ocmdPCS.Parameters[MST_WorkingDayDetailTable.OFFDAY_FLD].Value = objObject.OffDay;
ocmdPCS.Parameters.Add(new OleDbParameter(MST_WorkingDayDetailTable.COMMENT_FLD, OleDbType.VarWChar));
ocmdPCS.Parameters[MST_WorkingDayDetailTable.COMMENT_FLD].Value = objObject.Comment;
ocmdPCS.Parameters.Add(new OleDbParameter(MST_WorkingDayDetailTable.WORKINGDAYMASTERID_FLD, OleDbType.Integer));
ocmdPCS.Parameters[MST_WorkingDayDetailTable.WORKINGDAYMASTERID_FLD].Value = objObject.WorkingDayMasterID;
ocmdPCS.CommandText = strSql;
ocmdPCS.Connection.Open();
ocmdPCS.ExecuteNonQuery();
}
catch (OleDbException ex)
{
if (ex.Errors[1].NativeError == ErrorCode.SQLDUPLICATE_KEYCODE)
{
throw new PCSDBException(ErrorCode.DUPLICATE_KEY, METHOD_NAME, ex);
}
else
{
throw new PCSDBException(ErrorCode.ERROR_DB, METHOD_NAME, ex);
}
}
catch (InvalidOperationException ex)
{
throw new PCSDBException(ErrorCode.ERROR_DB, METHOD_NAME, ex);
}
catch (Exception ex)
{
throw new PCSDBException(ErrorCode.OTHER_ERROR, METHOD_NAME, ex);
}
finally
{
if (oconPCS != null)
{
if (oconPCS.State != ConnectionState.Closed)
{
oconPCS.Close();
}
}
}
}
//发表帖子
public void add(Post post)
{
OleDbConnection conn = null;
OleDbCommand comm = null;
try
{
conn = DBManage.getConn();
conn.Open();
comm = conn.CreateCommand();
comm.CommandText = "insert into post(userId,title,content,postTime,userIp,replyNum,browseNum) values (@userId,@title,@content,@postTime,@userIp,@replyNum,@browseNum)";
//动态设置参数
List <OleDbParameter> paras = new List <OleDbParameter>();
//paras.Add(new OleDbParameter() { ParameterName = "@postId", Value = post.PostId });
paras.Add(new OleDbParameter()
{
ParameterName = "@userId", Value = post.UserId
});
paras.Add(new OleDbParameter()
{
ParameterName = "@title", Value = post.Title
});
paras.Add(new OleDbParameter()
{
ParameterName = "@content", Value = post.Content
});
paras.Add(new OleDbParameter()
{
ParameterName = "@postTime", Value = post.PostTime.ToString()
});
paras.Add(new OleDbParameter()
{
ParameterName = "@userIp", Value = post.UserIp
});
paras.Add(new OleDbParameter()
{
ParameterName = "@replyNum", Value = post.ReplyNum
});
paras.Add(new OleDbParameter()
{
ParameterName = "@browseNum", Value = post.BrowseNum
});
comm.Parameters.AddRange(paras.ToArray <OleDbParameter>());
//3.执行命令
comm.ExecuteNonQuery();
}
catch (Exception e)
{
Console.Write(e.Message);
throw new Exception();
}
finally {
if (conn != null)
{
DBManage.closeConn(conn);
}
}
}
protected void ImageButton2_Click(object sender, ImageClickEventArgs e)
{
OleDbConnection vtbaglanti = new OleDbConnection();
vtbaglanti.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" + Server.MapPath("~/App_Data\\panel.mdb");
vtbaglanti.Open();
string ara = "SELECT count(kullaniciadi) FROM admin Where kullaniciadi = '" + txtKullaniciadi.Text + "'";
OleDbCommand sa = new OleDbCommand(ara, vtbaglanti);
int c = Convert.ToInt32(sa.ExecuteScalar());
if (c != 0)
{
Response.Write("<script>alert('Kullanıcı adı geçersiz')</script>");
}
else if (txtAd.Text == "" || txtSoyad.Text == "" || txtEposta.Text == "" || txtSifre1.Text == "" || txtSifre2.Text == "" || dpDay.SelectedValue == "" || dpMonth.SelectedValue == "" || dpYear.SelectedValue == "" || txtCeptelefonu.Text == "" || DropDownList3.SelectedIndex==0 ||(rdBay.Checked==false && rdBayan.Checked==false))
Response.Write("<script>alert('Lütfen Bilgilerinizi Eksiksiz Doldurunuz...')</script>");
else if (txtSifre1.Text != txtSifre2.Text)
{
Response.Write("<script>alert('Girdiğiniz Şifreler Eşleşmiyor')</script>");
}
else if (CheckBox1.Checked == false)
{
Response.Write("<script>alert('Canon Hizmet Şartlarını ve Gizlilik Politikasını Kabul Ediyorum Seçeneğini İşaretleyiniz')</script>");
}
else
{
if (vtbaglanti.State == ConnectionState.Closed)
{
vtbaglanti.Open();
}
OleDbCommand kaydet = new OleDbCommand("insert into admin(ad,soyad,eposta,kullaniciadi,sifre1,sifre2,dogumgun,dogumay,dogumyıl,cinsiyet,ceptelefonu,konum) values(@ad,@soyad,@eposta,@kullaniciadi,@sifre1,@sifre2,@dogumgun,@dogumay,@dogumyıl,@cinsiyet,@ceptelefonu,@konum)", vtbaglanti);
kaydet.Parameters.AddWithValue("@ad", txtAd.Text);
kaydet.Parameters.AddWithValue("@soyad", txtSoyad.Text);
kaydet.Parameters.AddWithValue("@eposta", txtEposta.Text);
kaydet.Parameters.AddWithValue("@kullaniciadi", txtKullaniciadi.Text);
kaydet.Parameters.AddWithValue("@sifre1", txtSifre1.Text);
kaydet.Parameters.AddWithValue("@sifre2", txtSifre2.Text);
kaydet.Parameters.AddWithValue("@dogumgun", dpDay.SelectedValue);
kaydet.Parameters.AddWithValue("@dogumay", dpMonth.SelectedValue);
kaydet.Parameters.AddWithValue("@dogumyıl", dpYear.SelectedValue);
if (rdBay.Checked)
{
kaydet.Parameters.AddWithValue("@cinsiyet", "Bay");
Session["cinsiyet"] = "Mr";
}
else
{
kaydet.Parameters.AddWithValue("@cinsiyet", "Bayan");
Session["cinsiyet"] = "Mrs";
}
kaydet.Parameters.AddWithValue("@ceptelefonu", txtCeptelefonu.Text);
kaydet.Parameters.AddWithValue("@konum", DropDownList3.SelectedValue);
kaydet.ExecuteNonQuery();
vtbaglanti.Close();
Response.Redirect("frmBilgi.aspx");
}
}
public void execute(string query)
{
OleDbTransaction trans = this.conn.BeginTransaction();
OleDbCommand comm = new OleDbCommand();
comm.Connection = this.conn;
comm.CommandText = query;
comm.Transaction = trans;
comm.ExecuteNonQuery();
trans.Commit();
}
/// <summary>
/// 将数据导出至Excel文件
/// </summary>
/// <param name="Table">DataTable对象</param>
/// <param name="ExcelFilePath">Excel文件路径</param>
public static bool OutputToExcel(DataTable Table, string ExcelFilePath)
{
if (File.Exists(ExcelFilePath))
{
throw new Exception("该文件已经存在！");
}
if ((Table.TableName.Trim().Length == 0) || (Table.TableName.ToLower() == "table"))
{
Table.TableName = "Sheet1";
}
//数据表的列数
int ColCount = Table.Columns.Count;
//用于记数，实例化参数时的序号
int i = 0;
//创建参数
OleDbParameter[] para = new OleDbParameter[ColCount];
//创建表结构的SQL语句
string TableStructStr = @"Create Table " + Table.TableName + "(";
//连接字符串
string connString = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + ExcelFilePath + ";Extended Properties=Excel 8.0;";
OleDbConnection objConn = new OleDbConnection(connString);
//创建表结构
OleDbCommand objCmd = new OleDbCommand();
//数据类型集合
ArrayList DataTypeList = new ArrayList();
DataTypeList.Add("System.Decimal");
DataTypeList.Add("System.Double");
DataTypeList.Add("System.Int16");
DataTypeList.Add("System.Int32");
DataTypeList.Add("System.Int64");
DataTypeList.Add("System.Single");
//遍历数据表的所有列，用于创建表结构
foreach (DataColumn col in Table.Columns)
{
//如果列属于数字列，则设置该列的数据类型为double
if (DataTypeList.IndexOf(col.DataType.ToString()) >= 0)
{
para[i] = new OleDbParameter("@" + col.ColumnName, OleDbType.Double);
objCmd.Parameters.Add(para[i]);
//如果是最后一列
if (i + 1 == ColCount)
{
TableStructStr += col.ColumnName + " double)";
}
else
{
TableStructStr += col.ColumnName + " double,";
}
}
else
{
para[i] = new OleDbParameter("@" + col.ColumnName, OleDbType.VarChar);
objCmd.Parameters.Add(para[i]);
//如果是最后一列
if (i + 1 == ColCount)
{
TableStructStr += col.ColumnName + " varchar)";
}
else
{
TableStructStr += col.ColumnName + " varchar,";
}
}
i++;
}
//创建Excel文件及文件结构
try
{
objCmd.Connection = objConn;
objCmd.CommandText = TableStructStr;
if (objConn.State == ConnectionState.Closed)
{
objConn.Open();
}
objCmd.ExecuteNonQuery();
}
catch (Exception exp)
{
throw exp;
}
//插入记录的SQL语句
string InsertSql_1 = "Insert into " + Table.TableName + " (";
string InsertSql_2 = " Values (";
string InsertSql = "";
//遍历所有列，用于插入记录，在此创建插入记录的SQL语句
for (int colID = 0; colID < ColCount; colID++)
{
if (colID + 1 == ColCount) //最后一列
{
InsertSql_1 += Table.Columns[colID].ColumnName + ")";
InsertSql_2 += "@" + Table.Columns[colID].ColumnName + ")";
}
else
{
InsertSql_1 += Table.Columns[colID].ColumnName + ",";
InsertSql_2 += "@" + Table.Columns[colID].ColumnName + ",";
}
}
InsertSql = InsertSql_1 + InsertSql_2;
//遍历数据表的所有数据行
for (int rowID = 0; rowID < Table.Rows.Count; rowID++)
{
for (int colID = 0; colID < ColCount; colID++)
{
if (para[colID].DbType == DbType.Double && Table.Rows[rowID][colID].ToString().Trim() == "")
{
para[colID].Value = 0;
}
else
{
para[colID].Value = Table.Rows[rowID][colID].ToString().Trim();
}
}
try
{
objCmd.CommandText = InsertSql;
objCmd.ExecuteNonQuery();
}
catch (Exception exp)
{
string str = exp.Message;
}
}
try
{
if (objConn.State == ConnectionState.Open)
{
objConn.Close();
}
}
catch (Exception exp)
{
throw exp;
}
return(true);
}
public int connectRegUG(UnderGrad x)
{
OleDbConnection connection = new OleDbConnection();
try
{
connection.ConnectionString = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\myFolder\myAccessFile.accdb;
Jet OLEDB:Database Password=MyDbPassword; Data Source=C:\Users\franc_000\Documents\College\StudentRegfinal\StudentReg\studentregdb.accdb; Persist Security Info = false; ";
connection.Open();
var cmd = new OleDbCommand("INSERT INTO Student ([Id],[Firstname], [Lastname] ,[Email] , [Address], [Subject], [intCAOdata], [doubleDegreegrade]) VALUES (@a, @b, @c, @d, @e, @f, @g, @h)");
cmd.Connection = connection;
cmd.Parameters.AddRange(new[] {
new OleDbParameter("@a", x.Id),
new OleDbParameter("@b", x.FirstName),
new OleDbParameter("@c", x.LastName),
new OleDbParameter("@d", x.Email), ////cant find reference to correct variables
new OleDbParameter("@e", x.Address),
new OleDbParameter("@f", x.Subject),
new OleDbParameter("@g", x.CAO),
new OleDbParameter("@h", "")
});
cmd.ExecuteNonQuery();
Console.WriteLine("Student registered successfully.");
}
catch (Exception ex)
{
Console.WriteLine("Error " + ex);
}
connection.Close();
return 0;
}
/// <summary>
/// 将数据导出至Excel文件
/// </summary>
/// <param name="Table">DataTable对象</param>
/// <param name="Columns">要导出的数据列集合</param>
/// <param name="ExcelFilePath">Excel文件路径</param>
public static bool OutputToExcel(DataTable Table, ArrayList Columns, string ExcelFilePath)
{
if (File.Exists(ExcelFilePath))
{
throw new Exception("该文件已经存在！");
}
//如果数据列数大于表的列数，取数据表的所有列
if (Columns.Count > Table.Columns.Count)
{
for (int s = Table.Columns.Count + 1; s <= Columns.Count; s++)
{
Columns.RemoveAt(s); //移除数据表列数后的所有列
}
}
//遍历所有的数据列，如果有数据列的数据类型不是 DataColumn，则将它移除
DataColumn column = new DataColumn();
for (int j = 0; j < Columns.Count; j++)
{
try
{
column = (DataColumn)Columns[j];
}
catch (Exception)
{
Columns.RemoveAt(j);
}
}
if ((Table.TableName.Trim().Length == 0) || (Table.TableName.ToLower() == "table"))
{
Table.TableName = "Sheet1";
}
//数据表的列数
int ColCount = Columns.Count;
//创建参数
OleDbParameter[] para = new OleDbParameter[ColCount];
//创建表结构的SQL语句
string TableStructStr = @"Create Table " + Table.TableName + "(";
//连接字符串
string connString = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + ExcelFilePath + ";Extended Properties=Excel 8.0;";
OleDbConnection objConn = new OleDbConnection(connString);
//创建表结构
OleDbCommand objCmd = new OleDbCommand();
//数据类型集合
ArrayList DataTypeList = new ArrayList();
DataTypeList.Add("System.Decimal");
DataTypeList.Add("System.Double");
DataTypeList.Add("System.Int16");
DataTypeList.Add("System.Int32");
DataTypeList.Add("System.Int64");
DataTypeList.Add("System.Single");
DataColumn col = new DataColumn();
//遍历数据表的所有列，用于创建表结构
for (int k = 0; k < ColCount; k++)
{
col = (DataColumn)Columns[k];
//列的数据类型是数字型
if (DataTypeList.IndexOf(col.DataType.ToString().Trim()) >= 0)
{
para[k] = new OleDbParameter("@" + col.Caption.Trim(), OleDbType.Double);
objCmd.Parameters.Add(para[k]);
//如果是最后一列
if (k + 1 == ColCount)
{
TableStructStr += col.Caption.Trim() + " Double)";
}
else
{
TableStructStr += col.Caption.Trim() + " Double,";
}
}
else
{
para[k] = new OleDbParameter("@" + col.Caption.Trim(), OleDbType.VarChar);
objCmd.Parameters.Add(para[k]);
//如果是最后一列
if (k + 1 == ColCount)
{
TableStructStr += col.Caption.Trim() + " VarChar)";
}
else
{
TableStructStr += col.Caption.Trim() + " VarChar,";
}
}
}
//创建Excel文件及文件结构
try
{
objCmd.Connection = objConn;
objCmd.CommandText = TableStructStr;
if (objConn.State == ConnectionState.Closed)
{
objConn.Open();
}
objCmd.ExecuteNonQuery();
}
catch (Exception exp)
{
throw exp;
}
//插入记录的SQL语句
string InsertSql_1 = "Insert into " + Table.TableName + " (";
string InsertSql_2 = " Values (";
string InsertSql = "";
//遍历所有列，用于插入记录，在此创建插入记录的SQL语句
for (int colID = 0; colID < ColCount; colID++)
{
if (colID + 1 == ColCount) //最后一列
{
InsertSql_1 += Columns[colID].ToString().Trim() + ")";
InsertSql_2 += "@" + Columns[colID].ToString().Trim() + ")";
}
else
{
InsertSql_1 += Columns[colID].ToString().Trim() + ",";
InsertSql_2 += "@" + Columns[colID].ToString().Trim() + ",";
}
}
InsertSql = InsertSql_1 + InsertSql_2;
//遍历数据表的所有数据行
DataColumn DataCol = new DataColumn();
for (int rowID = 0; rowID < Table.Rows.Count; rowID++)
{
for (int colID = 0; colID < ColCount; colID++)
{
//因为列不连续，所以在取得单元格时不能用行列编号，列需得用列的名称
DataCol = (DataColumn)Columns[colID];
if (para[colID].DbType == DbType.Double && Table.Rows[rowID][DataCol.Caption].ToString().Trim() == "")
{
para[colID].Value = 0;
}
else
{
para[colID].Value = Table.Rows[rowID][DataCol.Caption].ToString().Trim();
}
}
try
{
objCmd.CommandText = InsertSql;
objCmd.ExecuteNonQuery();
}
catch (Exception exp)
{
string str = exp.Message;
}
}
try
{
if (objConn.State == ConnectionState.Open)
{
objConn.Close();
}
}
catch (Exception exp)
{
throw exp;
}
return(true);
}
public void writeRate(Structs.RateInt ri)//写入rate
{
cmd = new OleDbCommand("update sheet2 set TESTNO='" + ri.COUNT.ToString() + "',FIRSTPASSNO='" + ri.FIRSTPASSNO.ToString() + "',FIRSTPASSRATE='" + ri.FIRSTPASSRATE.ToString("0.00") + "',REPEATNO='" + ri.REPEATNO.ToString() + "', REPEATRATE='" + ri.REPEATRATE.ToString("0.00") + "'where ID=1", conn);
cmd.ExecuteNonQuery();
}
public static void executeNonQuery(OleDbConnection conn, string insertStatement)
{
OleDbCommand comm = new OleDbCommand(insertStatement, conn);
comm.ExecuteNonQuery();
}
