private void CreateAttemptedSheet(String buildingName)
{
try
{
//Fetches sheet with master list data
using (OLEDB.OleDbConnection conn = returnConnection())
{
try
{
conn.Open();
OLEDB.OleDbCommand cmd = new OLEDB.OleDbCommand();
cmd.Connection = conn;
cmd.CommandText = @"Create Table " + buildingName.Replace(' ', '_').Replace('-', '_') + "_Attempted(RfidTagId varchar, Location varchar,BU varchar, BUStaging varchar, RequestedDate varchar,RequestedModifyDate varchar,LocType varchar,PackageType varchar);";
cmd.ExecuteNonQuery();
cmd.CommandText = @"Insert Into [" + buildingName.Replace(' ', '_').Replace('-', '_') + "_Attempted$] Select RfidTagId,Location,BU,BUStaging,RequestedDate,RequestedModifyDate,LocType,PackageType From [MasterData$] Where Status<>'Missing' and PackageType NOT IN " + SizeListString() + " and (LocType<>'CUSTOMER STAGING' and LocType<>'delivered') and (Location NOT LIKE '%versum%' and Location NOT LIKE '%MarkGruver%' and (Location LIKE '%" + buildingName + "%attempt%' OR (Location LIKE '%" + buildingName + "%pallet%' AND LocType LIKE '%ATTEMPT%') OR (BUStaging LIKE '%" + buildingName + "%' AND LocType Like '%ATTEMPT%'))) Order By BU;";
cmd.ExecuteNonQuery();
}
catch (Exception ex)
{
Console.WriteLine(ex.ToString());
}
finally
{
conn.Close();
conn.Dispose();
}
}
}
catch (Exception ex)
{
MessageBox.Show(ex.ToString());
}
}
private void ENT_Click()
{
//项目名称修改
if (TbFileName.Text == "")
{
MessageBox.Show("文件项目名不能为空！", "提示");
}
else
{
//更新数据库
//Window_Project11.CurrentItemName;
OleDbConnection conn = new OleDbConnection(odbcConnStr);
string sql = "select * from ItemInfor where ItemName='" + TbFileName.Text.Trim() + "'";
OleDbCommand cmd = new OleDbCommand(sql, conn);
conn.Open();
if (cmd.ExecuteNonQuery() > 0)
{
MessageBox.Show("该项目已经存在！", "提示");
}
else
{
// sql = "update tbCourseInfo set filedCourseName='" + tbCourseName.Text.Trim() + "' where filedCourseID=" + courseID;
sql = "update ItemInfor set ItemName='" + TbFileName.Text.Trim() + "' where ItemID=" + Window_Project11.CurrentItemID;
MessageBox.Show(sql);
cmd.CommandText = sql;
cmd.ExecuteNonQuery();
MessageBox.Show("项目修改成功！", "提示");
}
}
}
public static void updatePosition(string swimlane, List<string> swimlaneBacklog, List<string> swimlaneBacklogPos)
{
OleDbConnection conn = new OleDbConnection();
OleDbCommand myCommand = new OleDbCommand();
conn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=|DataDirectory|LanbanDatabase.mdb;";
conn.Open();
myCommand.Connection = conn;
int statusID = 1;
if (swimlane == "5")
statusID = 3;
if (swimlane == "4")
statusID = 2;
for (int i = 0; i < swimlaneBacklog.Count; i++)
{
if (swimlane == "5")
{
myCommand.CommandText = "UPDATE Backlogs SET BacklogPosition = " + swimlaneBacklogPos[i]
+ ", swimlaneID = " + swimlane + ", BacklogStatusID = " + statusID + ", BacklogCompletedDate = '" + DateTime.Today
+ "' WHERE backlogID = " + swimlaneBacklog[i];
myCommand.ExecuteNonQuery();
myCommand.CommandText = "UPDATE Tasks SET TaskStatusID = " + statusID + ", TaskCompletedDate = '" + DateTime.Today
+ "' WHERE backlogID = " + swimlaneBacklog[i] + " AND TaskStatusID <> 3";
myCommand.ExecuteNonQuery();
}
else
{
myCommand.CommandText = "UPDATE Backlogs SET BacklogPosition = " + swimlaneBacklogPos[i]
+ ", swimlaneID = " + swimlane + ", BacklogStatusID = " + statusID + ", BacklogCompletedDate = null"
+ " WHERE backlogID = " + swimlaneBacklog[i];
myCommand.ExecuteNonQuery();
}
}
conn.Close();
}
private void CreateLargeAttemptedSheet(String roomName)
{
try
{
using (OLEDB.OleDbConnection conn = returnConnection())
{
try
{
conn.Open();
OLEDB.OleDbCommand cmd = new OLEDB.OleDbCommand();
cmd.Connection = conn;
cmd.CommandText = @"Create Table " + roomName.Replace(' ', '_').Replace('-', '_') + "_Large_Attempted(RfidTagId varchar, Location varchar,BU varchar, BUStaging varchar, RequestedDate varchar,RequestedModifyDate varchar,LocType varchar,PackageType varchar);";
cmd.ExecuteNonQuery();
cmd.CommandText = @"Insert Into [" + roomName.Replace(' ', '_').Replace('-', '_') + "_Large_Attempted$] Select RfidTagId,Location,BU,BUStaging,RequestedDate,RequestedModifyDate,LocType,PackageType From [MasterData$] Where Status<>'Missing' and (LocType<>'CUSTOMER STAGING' and LocType<>'delivered') and PackageType IN " + SizeListString() + " and (Location NOT LIKE '%RVN%' and Location NOT LIKE '%RCV-Stag%' and Location NOT LIKE '%versum%' and (Location LIKE '%attempt%' OR LocType LIKE '%ATTEMPT%') and Location NOT LIKE '%B72%' and BUStaging NOT LIKE '%B72%') and BUStaging='" + roomName + "' Order By BU;";
cmd.ExecuteNonQuery();
}
catch (Exception ex)
{
Console.WriteLine(ex.ToString());
}
finally
{
conn.Close();
conn.Dispose();
}
}
}
catch (Exception ex)
{
MessageBox.Show(ex.ToString());
}
}
protected void BtnSaveComment_Click(object sender, EventArgs e)
{
Button Btn = (Button)sender;
DataListItem DLI = (DataListItem)Btn.Parent;
DataList DL = (DataList)DLI.Parent;
DataTable EvalTextItem = (DataTable)ViewState["EvalTextItem"];
string EvalItemTextId = string.Empty;
EvalItemTextId = EvalTextItem.Rows[DLI.ItemIndex]["EvalItemTextId"].ToString();
TextBox txt = (TextBox)DLI.FindControl("TxtComment");
Label Lbl = (Label)DLI.FindControl("LblNotify");
Lbl.Text = string.Empty;
int Idx = (int)DLI.ItemIndex;
OleDbConnection Con = ET_Lib.E_trainingConnection;
OleDbCommand Cmd = new OleDbCommand("UPDATE DowraEvalioutionTrainerText SET dateEval = #" + DateTime.Today.ToShortDateString() + "#, EvalText = '" + txt.Text + "' WHERE (DowraEvalioutionTrainerText.TRshehId = " + GetTRshehId() + ") AND (DowraEvalioutionTrainerText.EvalItemTextId = " + EvalItemTextId + ")", Con);
try
{
Con.Open();
if (Cmd.ExecuteNonQuery() == 0)
{
Cmd.CommandText = "INSERT INTO DowraEvalioutionTrainerText (TRshehId, EvalItemTextId, dateEval, EvalText) VALUES (" + GetTRshehId() + ", " + EvalItemTextId + ", #" + DateTime.Today.ToShortDateString() + "#, '" + txt.Text + "')";
Cmd.ExecuteNonQuery();
}
Lbl.Text = "تم الاضـــــــافه";
Lbl.ForeColor = System.Drawing.Color.Green;
}
catch (Exception ex)
{
Lbl.Text = ex.Message;
Lbl.ForeColor = System.Drawing.Color.Red;
}
Con.Close();
}
protected void BtnAdd_Click(object sender, EventArgs e)
{
OleDbConnection Con = ET_Lib.E_trainingConnection;
string ApoloReason = "NULL";
if (TxtReason.Text.Trim().Length != 0)
{
ApoloReason = TxtReason.Text;
}
OleDbCommand Cmd = new OleDbCommand("", Con);
try
{
Con.Open();
Cmd.CommandText = "UPDATE DowraApology SET ApologyDate = #" + DateTime.Today.ToShortDateString().ToString() + "#, ApologyReson = '" + ApoloReason + "' WHERE (DowraApology.TRshehId = " + TRshehId(ET_Lib.Empid, Request.QueryString["Courseid"].ToString()) + ")";
if (Cmd.ExecuteNonQuery() == 0)
{
Cmd.CommandText = "INSERT INTO DowraApology (TRshehId, ApologyDate, ApologyReson) VALUES (" + TRshehId(ET_Lib.Empid, Request.QueryString["Courseid"].ToString()) + ", #" + DateTime.Today.ToShortDateString().ToString() + "#, '" + ApoloReason + "')";
Cmd.ExecuteNonQuery();
}
Response.Redirect("~/TrainerCourses.aspx");
}
catch (Exception ex)
{
LblState.Text = ex.Message;
LblState.Visible = true;
}
Con.Close();
}
private void addbtn_Click(object sender, EventArgs e)
{
if (nameTxt.Text.Length < 1 || lastNameTxt.Text.Length < 1 || addressTxt.Text.Length < 1 || phoneTxt.Text.Length < 1)
{
MessageBox.Show("All fields must be entered!", "Invalid Values", MessageBoxButtons.OK, MessageBoxIcon.Error);
return;
}
if (birthdayPicker.Value.AddYears(18) > DateTime.Today)
{
MessageBox.Show("Your employee must be atleast 18 years old!", "Invalid Birthday", MessageBoxButtons.OK, MessageBoxIcon.Error);
return;
}
Connection connection = new Connection();
connection.openConnection();
OleDbCommand employeeCommand = new OleDbCommand();
employeeCommand.Connection = connection.connection;
string name = nameTxt.Text;
string lastName = lastNameTxt.Text;
string address = addressTxt.Text;
string phone = phoneTxt.Text;
string birthday = birthdayPicker.Value.ToShortDateString();
string employment = DateTime.Now.ToString();
if (edit)
{
employeeCommand.CommandText = "UPDATE Employees SET First_Name = '" + name + "', Last_Name = '" + lastName + "', Address = '" + address + "', Phone = '" + phone + "', Birthday = #" + birthday + "# WHERE ID = " + employee.id;
employeeCommand.ExecuteNonQuery();
employee.name = name;
employee.lastName = lastName;
employee.address = address;
employee.phone = phone;
employee.birthday = birthdayPicker.Value.Date;
}
else
{
employeeCommand.CommandText = "INSERT INTO Employees (First_Name,Last_Name,Address,Phone,Birthday,Employmend,Fired) VALUES ('" + name + "', '" + lastName + "', '" + address + "', '" + phone + "', #" + birthday + "#, #" + employment + "#, false)";
employeeCommand.ExecuteNonQuery();
employeeCommand.CommandText = "Select @@Identity";
string idEmployee = employeeCommand.ExecuteScalar().ToString();
employee = new CEmployee(idEmployee, name, lastName, address, phone, birthdayPicker.Value.Date, DateTime.Now, false);
}
connection.closeConnection();
DialogResult = System.Windows.Forms.DialogResult.OK;
Close();
}
protected void BtnSaveResearch_Click(object sender, EventArgs e)
{
if (TxtResearchSubject.Text == string.Empty)
{
LblResearchError.Text = "من فضلك املاء البيانات";
LblResearchError.Visible = true;
return;
}
try
{
string search_code = string.Empty;
OleDbConnection Con = MySchool.LibraryConnection;
OleDbCommand CMD = new OleDbCommand("Select TOP 1 MAX(search_code) From search1", Con);
OleDbDataReader DR;
string SavingFileName = string.Empty;
if (FileUploadResearch.HasFile)
{
SavingFileName = DateTime.Today.Year.ToString() + DateTime.Today.Month.ToString() + DateTime.Today.Day.ToString() + DateTime.Today.Second.ToString() + DateTime.Today.Second.ToString() + DateTime.Today.Second.ToString() + FileUploadResearch.FileName.ToString();
FileUploadResearch.SaveAs(HttpContext.Current.Server.MapPath("ResearchsFiles\\") + SavingFileName);
SavingFileName = "~/ResearchsFiles/" + SavingFileName;
}
Con.Open();
DR = CMD.ExecuteReader();
if (DR.Read())
{
search_code = DR.GetValue(0).ToString();
DR.Close();
search_code = (Convert.ToInt16(search_code) + 1).ToString();
CMD.CommandText = "Insert Into search (search_code,SPOS,asase_code,[date],subject,aim_search,Show,RelatedFile) VALUES (" + search_code + "," + SPOS + "," + asase_code + ",#" + TxtResearchDate.Text + "#,'" + TxtResearchSubject.Text + "','" + TxtAim.Text + "',0,'" + SavingFileName + "')";
CMD.ExecuteNonQuery();
LblResearchError.Text = "تم اضافة البحث";
LblResearchError.ForeColor = System.Drawing.Color.Green;
LblResearchError.Visible = true;
}
else
{
search_code = "0";
CMD.CommandText = "Insert Into search (search_code,SPOS,asase_code,[date],subject,aim_search,Show,RelatedFile) VALUES (1," + SPOS + "," + asase_code + ",#" + TxtResearchDate.Text + "#,'" + TxtResearchSubject.Text + "','" + TxtAim.Text + "',0,'" + SavingFileName + "')";
CMD.ExecuteNonQuery();
LblResearchError.Text = "تم اضافة البحث";
LblResearchError.ForeColor = System.Drawing.Color.Green;
LblResearchError.Visible = true;
}
CMD.CommandText = "Insert Into search1 (search_code, code_name) VALUES (" + search_code + ", " + StuID + ")";
CMD.ExecuteNonQuery();
Con.Close();
}
catch (Exception ex)
{
LblResearchError.Text = ex.Message.ToString();
LblResearchError.ForeColor = System.Drawing.Color.Red;
LblResearchError.Visible = true;
}
}
private void save_result()
{
saveFlag = 1;
string files = Application.StartupPath + "\\DataBase.mdb";
string aConnectString = "Provider=Microsoft.Jet.OleDb.4.0;Data Source=";
aConnectString += files;
sqlCon.ConnectionString = aConnectString;
sqlCon.Open();
sqlCmd.Connection = sqlCon;
for (int ind = 0; ind < Grid1.RowCount; ind++)
{
if (Grid1.Rows[ind].Cells[0].Value == null)
{
continue;
}
int flagValue = changeFlag[int.Parse(Grid1.Rows[ind].Cells[0].Value.ToString())];
if (flagValue == 1)
{
string sqlstr = "select * from userData where 编号 = " + Grid1.Rows[ind].Cells[0].Value.ToString();
sqlAdp = new OleDbDataAdapter(sqlstr, sqlCon);
DataSet ds = new DataSet();
sqlAdp.Fill(ds, "userData");
if (ds.Tables[0].Rows.Count > 0)
{
sqlCmd.CommandText = UpdateStr(ind);
sqlCmd.ExecuteNonQuery();
}
else
{
sqlCmd.CommandText = InsertStr(ind);
sqlCmd.ExecuteNonQuery();
}
}
}
//记录需要删除的就删掉
foreach (var item in changeFlag)
{
if (item.Value == 2)
{
string sqlstr = "select * from userData where 编号 = " + item.Key;
sqlAdp = new OleDbDataAdapter(sqlstr, sqlCon);
DataSet ds = new DataSet();
sqlAdp.Fill(ds, "userData");
if (ds.Tables[0].Rows.Count > 0)
{
string delstr = "delete from userData where 编号 = " + item.Key;
sqlCmd.CommandText = delstr;
sqlCmd.ExecuteNonQuery();
}
}
}
sqlCon.Close();
}
private void btn_excel_db_check_Click(object sender, EventArgs e)
{
// //try
// //{
// for (int i = 1; i < db_excel_data.Rows.Count; i++)
// {
// myConn.Open();
// SqlDataAdapter adp_sales = new SqlDataAdapter("insert into satislar(urun_id, musteri_id, adet) values('" + Convert.ToInt32(db_excel_data.Rows[i].Cells[1].Value.ToString()) + "','" + Convert.ToInt32(db_excel_data.Rows[i].Cells[2].Value.ToString()) + "','" + Convert.ToInt32(db_excel_data.Rows[i].Cells[4].Value.ToString()) + "'", myConn);
// //SqlDataAdapter adp_sales = new SqlDataAdapter("exec proc_add_sale " + Convert.ToInt32(db_excel_data.Rows[i].Cells[1].Value.ToString()) + " , " + Convert.ToInt32(db_excel_data.Rows[i].Cells[2].Value.ToString()) + " , " + Convert.ToInt32(db_excel_yedek.Rows[i].Cells[3].Value.ToString()) + " , " + Convert.ToInt32(db_excel_yedek.Rows[i].Cells[4].Value.ToString()) + "", db);
// DataTable tbl_adp_sales = new DataTable();
// adp_sales.Fill(tbl_adp_sales);
// myConn.Close();
// }
// MessageBox.Show("Veriler başarılı bir şekilde eklendi !");
// //}
// //catch (Exception)
// //{
// // MessageBox.Show("Veriler eklenirken hata oluştu !");
// //}
if (textBox4.Text != string.Empty && textBox5.Text != string.Empty && textBox6.Text != string.Empty && db_excel_data.SelectedRows.Count != 0)
{
System.Data.OleDb.OleDbCommand myCommand = new System.Data.OleDb.OleDbCommand();
string sql = null;
string sql2 = null;
string sql3 = null;
OleDbConnection Baglanti = new OleDbConnection(@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source='" + lb_dosya_yolu.Text + "'; Extended Properties='Excel 12.0 xml;HDR=YES;'");
Baglanti.Open();
myCommand.Connection = Baglanti;
sql = "Update [Sheet1$] set urun_id=" + textBox4.Text + " where adet=" + satis_id;
sql2 = "Update [Sheet1$] set musteri_id=" + textBox5.Text + " where adet=" + satis_id;
sql3 = "Update [Sheet1$] set adet=" + textBox6.Text + " where adet=" + satis_id;
myCommand.CommandText = sql;
myCommand.ExecuteNonQuery();
myCommand.CommandText = sql2;
myCommand.ExecuteNonQuery();
myCommand.CommandText = sql3;
myCommand.ExecuteNonQuery();
Baglanti.Close();
MessageBox.Show("Güncellendi");
}
else
{
MessageBox.Show("Boş değer girilemez!");
}
}
private void updateBtn_Click(object sender, RoutedEventArgs e)
{
if (openedFilePath == string.Empty)
{
System.Windows.MessageBox.Show("No sheet opened!");
return;
}
File.Delete(openedFilePath);
string connectionString = getConnectionStr(openedFilePath, openedFileExtention);
using (System.Data.OleDb.OleDbConnection con = new System.Data.OleDb.OleDbConnection(connectionString))
{
con.Open();
StringBuilder strSQL = new StringBuilder();
System.Data.OleDb.OleDbCommand cmd;
System.Data.DataTable dt = ((System.Data.DataView)grdData.ItemsSource).Table;
// create sheet/table
strSQL.Append("CREATE TABLE ").Append("[" + dt.TableName + "]");
strSQL.Append("(");
for (int i = 0; i < dt.Columns.Count; i++)
{
strSQL.Append("[" + dt.Columns[i].ColumnName + "] text,");
}
strSQL = strSQL.Remove(strSQL.Length - 1, 1);
strSQL.Append(")");
cmd = new System.Data.OleDb.OleDbCommand(strSQL.ToString(), con);
cmd.ExecuteNonQuery();
// insert data line by line
for (int i = 0; i < dt.Rows.Count; i++)
{
if (dt.Rows[i].RowState == System.Data.DataRowState.Deleted)
{
continue;
}
strSQL.Clear();
StringBuilder strvalue = new StringBuilder();
for (int j = 0; j < dt.Columns.Count; j++)
{
strvalue.Append("'" + dt.Rows[i][j].ToString() + "'");
if (j != dt.Columns.Count - 1)
{
strvalue.Append(",");
}
}
cmd.CommandText = strSQL.Append(" insert into [" + dt.TableName + "] values (").Append(strvalue).Append(")").ToString();
cmd.ExecuteNonQuery();
}
con.Close();
}
System.Windows.Forms.MessageBox.Show("Update successfully!");
}
/// <summary>
/// DataTable转换为Excel，支持xls，不支持xlsx
/// </summary>
/// <param name="dtSource"></param>
/// <param name="strPath"></param>
/// <param name="strSheetName"></param>
public static void ToExcel(this DataTable dtSource, string strPath, string strSheetName = "Sheet1")
{
//http://flyspirit99.blogspot.com/2007/07/export-more-than-255-characters-into.html-
System.Data.OleDb.OleDbConnection OleDb_Conn = new System.Data.OleDb.OleDbConnection();
OleDb_Conn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Extended Properties='Excel 8.0;HDR=No';" + "Data Source=\"" + strPath + "\"";
try
{
OleDb_Conn.Open();
System.Data.OleDb.OleDbCommand OleDb_Comm = new System.Data.OleDb.OleDbCommand();
OleDb_Comm.Connection = OleDb_Conn;
string strCmd;
try
{
strCmd = "drop table [" + strSheetName + "]";
OleDb_Comm.CommandText = strCmd;
OleDb_Comm.ExecuteNonQuery();
}
catch
{
}
strCmd = "create Table [" + strSheetName + "]("; foreach (DataColumn dc in dtSource.Columns)
{
strCmd += "[" + dc.ColumnName + "] memo,";
}
strCmd = strCmd.Trim().Substring(0, strCmd.Length - 1);
strCmd += ")";
OleDb_Comm.CommandText = strCmd;
OleDb_Comm.ExecuteNonQuery();
foreach (DataRow dr in dtSource.Rows)
{
if (dr.RowState != System.Data.DataRowState.Deleted)
{
strCmd = "insert into [" + strSheetName + "] values(";
foreach (DataColumn dc in dtSource.Columns)
{
strCmd += "'" + dr[dc.ColumnName].ToString().Trim().Replace("'", "") + "',";
}
strCmd = strCmd.Substring(0, strCmd.Length - 1);
strCmd += ")"; OleDb_Comm.CommandText = strCmd;
OleDb_Comm.ExecuteNonQuery();
}
}
OleDb_Conn.Close();
}
catch (Exception ex)
{
throw ex;
}
finally
{
OleDb_Conn.Close();
}
}
private void Guncelle()
{
using (OleDbConnection conn = new OleDbConnection(@"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=hesap.mdb"))
{
conn.Open();
OleDbCommand komut = new OleDbCommand();
komut.Connection = conn;
komut.CommandText = "Select * from hesap"; // sorgu / komut cumlemi yazıyorum.
komut.ExecuteNonQuery(); // insert , updateiçin gerekli satir sayisi donduruyoruz.
OleDbDataReader dr = komut.ExecuteReader(); // datareader olusturup komut sorgulayıp veritabaninda okuma işlemini tanıtıyoruz
while (dr.Read()) // datareader ile okuyoruz.
{
string hadi = dr["hadi"].ToString();
string kadi = dr["kadi"].ToString();
string q;
using (WebClient asd = new WebClient())
{
asd.Encoding = Encoding.UTF8;
q = asd.DownloadString("http://gdata.youtube.com/feeds/api/users/" + kadi + "/uploads?v=2&alt=jsonc&max-results=0");
}
string[] adet1 = q.Split(new string[] { "totalItems\":" }, StringSplitOptions.None);
string[] adet2 = adet1[1].Split(',');
listView1.Items.Add(new ListViewItem(new string[] { hadi, "Adet: "+adet2[0] }));
}
dr.Close();
komut.ExecuteNonQuery(); // insert , updateiçin gerekli satir sayisi donduruyoruz.
dr = komut.ExecuteReader(); // datareader olusturup komut sorgulayıp veritabaninda okuma işlemini tanıtıyoruz
while (dr.Read()) // datareader ile okuyoruz.
{
string kadi = dr["hadi"].ToString(); // veritabanimdaki "kadi" alanımdaki veriyi alip kadi değişkenine atıyorum(yukarıda string olusturmustum)
string sifre = dr["hsifresi"].ToString(); // aynı durum söz konusu
string devkey = dr["devkey"].ToString();
Random a = new Random();
string id = a.Next(100000, 999999).ToString();
YouTubeRequestSettings settings = new YouTubeRequestSettings(id, devkey, kadi,sifre);
YouTubeRequest request = new YouTubeRequest(settings);
string feedUrl = "https://gdata.youtube.com/feeds/api/users/default/uploads";
Feed<Video> videoFeed = request.Get<Video>(new Uri(feedUrl));
foreach (Video entry in videoFeed.Entries)
{
string vid_thumb ="http://img.youtube.com/vi/"+entry.VideoId+"/0.jpg";
int izlenme = entry.ViewCount;
if(izlenme == -1)
izlenme = 0;
listView1.Items.Add(new ListViewItem(new string[] { kadi,entry.YouTubeEntry.Title.Text,izlenme.ToString() }));
}
}
}
}
static void Main(string[] args)
{
_dt = new DataTable();
_ds = new DataSet();
_repository = new Repository();
_helpers = new Helpers();
_oldFile = @"E:\Development\Projects\Transfer data exel\1.xlsx";
_newFile = @"E:\Development\Projects\Transfer data exel\empty.xlsx";
//Path to location file after instaling application
//string path = Path.Combine(Environment.CurrentDirectory, @"Data\empty.xlsx");
_ds = _repository.GetDataSetFromExcelFile(_oldFile);
_helpers.Ds = _ds;
_thicknessOfMaterials = _helpers.GetThicknessOfMaterials();
System.Data.OleDb.OleDbCommand myCommand = new System.Data.OleDb.OleDbCommand();
OleDbConnection MyConnection = new OleDbConnection(_repository.GetConnectionString(_newFile));
MyConnection.Open();
myCommand.Connection = MyConnection;
foreach (string thkness in _thicknessOfMaterials)
{
_dt = _helpers.GetAllPartWithThikness(thkness);
myCommand.Parameters.Add(new OleDbParameter("@thikness", string.Format("Материал с толщиной {0}мм", thkness.ToString())));
_sql = "insert into [Распил$C:C" + _count + "] values (@thikness)";
myCommand.CommandText = _sql;
myCommand.ExecuteNonQuery();
myCommand.Parameters.Clear();
_count++;
foreach (DataRow dr in _dt.Rows)
{
_sql = "insert into [Распил$A:J" + _count + "] values (@sectionNumber, @numberOfDetail, @nameOfDitails, '', '', '', '', @x, @y, @number)";
myCommand.CommandText = _sql;
myCommand.Parameters.Add(new OleDbParameter("@sectionNumber", dr["SectionNumber"].ToString()));
myCommand.Parameters.Add(new OleDbParameter("@numberOfDetail", _count.ToString()));
myCommand.Parameters.Add(new OleDbParameter("@nameOfDitails", dr["NameOfDitails"].ToString()));
myCommand.Parameters.Add(new OleDbParameter("@x", dr["X"].ToString()));
myCommand.Parameters.Add(new OleDbParameter("@y", dr["Y"].ToString()));
myCommand.Parameters.Add(new OleDbParameter("@number", dr["Number"].ToString()));
myCommand.ExecuteNonQuery();
myCommand.Parameters.Clear();
_count++;
}
_count += 2;
}
MyConnection.Close();
}
//清除用户
private void Btn_Delete_User_Click(object sender, EventArgs e)
{
OleDbConnection oleDB = new OleDbConnection(@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=dac.accdb");
oleDB.Open();
OleDbCommand cmd = new OleDbCommand();
cmd.Connection = oleDB;
cmd.CommandText = "delete from [主体信息表] where 主体名称=" + "'" + this.ComboBox_UserName.Text.ToLower() + "'";
cmd.ExecuteNonQuery();
cmd.CommandText = "delete from [用户-角色指派信息表] where 用户名称=" + "'" + this.ComboBox_UserName.Text.ToLower() + "'";
cmd.ExecuteNonQuery();
oleDB.Close();
MessageBox.Show(this, "删除用户成功", "成功", MessageBoxButtons.OK, MessageBoxIcon.Information);
Refresh_Users_Roles(0);
}
private void btnReset_Click(object sender, EventArgs e)
{
if (connection.State.Equals(ConnectionState.Closed))
{
connection = new OleDbConnection();
connection.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\\DB\\UC.mdb;Persist Security Info=False";
connection.Open();
}
OleDbCommand cmd = new OleDbCommand();
cmd.CommandText = "DELETE * FROM InputTable";
cmd.Connection = connection;
int a = cmd.ExecuteNonQuery();
cmd.CommandText = "DELETE * FROM OutputTable";
a = cmd.ExecuteNonQuery();
}
private void addbtn_Click(object sender, EventArgs e)
{
if (nameTxt.Text.Length < 1 || lastNameTxt.Text.Length < 1 || addressTxt.Text.Length < 1 || phoneTxt.Text.Length < 1)
{
MessageBox.Show("All fields must be entered!", "Invalid Values", MessageBoxButtons.OK, MessageBoxIcon.Error);
return;
}
Connection connection = new Connection();
connection.openConnection();
OleDbCommand customerCommand = new OleDbCommand();
customerCommand.Connection = connection.connection;
string name = nameTxt.Text;
string lastName = lastNameTxt.Text;
string address = addressTxt.Text;
string phone = phoneTxt.Text;
string birthday = birthdayPicker.Value.ToShortDateString();
if (edit)
{
customerCommand.CommandText = "UPDATE Customers SET First_Name = '" + name + "', Last_Name = '" + lastName + "', Address = '" + address + "', Phone = '" + phone + "', Birthday = #" + birthday + "# WHERE ID = " + customer.id;
customerCommand.ExecuteNonQuery();
customer.name = name;
customer.lastName = lastName;
customer.address = address;
customer.phone = phone;
customer.birthday = birthdayPicker.Value.Date;
}
else
{
customerCommand.CommandText = "INSERT INTO Customers (First_Name,Last_Name,Address,Phone,Birthday) VALUES ('" + name + "', '" + lastName + "', '" + address + "', '" + phone + "', #" + birthday + "#)";
customerCommand.ExecuteNonQuery();
customerCommand.CommandText = "Select @@Identity";
string idCustomer = customerCommand.ExecuteScalar().ToString();
customer = new CCustomer(idCustomer, name, lastName, address, phone, birthdayPicker.Value.Date);
}
connection.closeConnection();
DialogResult = System.Windows.Forms.DialogResult.OK;
Close();
}
public bool set_on(string dev)
{
try
{
string sets = "[" + dev + "]=1";//,[Switch_Toshiba1(7/9:40/2)]=0,[Switch_Toshiba2(8/10:42/2)]=0,[Switch_Toshiba3(9/11:44/2)]=0,[Switch_Toshiba4(10/12:46/2)]=0,[Switch_Toshiba5(16/13:48/2)]=0,[Fan(22/9:39/2)]=0,[Humidity_BLed(26/15:33/2)]=0,[Humidity_GLed(25/15:35/2)]=0,[Humidity_RLed(24/15:37/2)]=0,[Niazy Lamp1(11/9:41/2)]=0,[Niazy Lamp2(12/10:43/2)]=0,[Niazy Lamp3(13/11:45/2)]=0,[Niazy Lamp4(14/12:47/2)]=0,[Niazy Lamp5(15/13:49/2)]=0,[LDR(20/15:14/2)]=0,[DHT11(21/15:15/2)]=0";
System.Data.OleDb.OleDbCommand cmd = new System.Data.OleDb.OleDbCommand();
string SQL = null;
ExcelCon = new System.Data.OleDb.OleDbConnection("provider=Microsoft.Jet.OLEDB.4.0;Data Source='" + filename + "';Extended Properties=Excel 8.0;");
ExcelCon.Open();
cmd.Connection = ExcelCon;
SQL = "Update [" + Title + "] set " + sets + " WHERE [RecDateTime] =(SELECT min([RecDateTime]) FROM [" + Title + "] WHERE [RecDateTime] BETWEEN @p1 AND @p2)";
cmd.CommandText = SQL;
string date1 = DateTime.Parse("01/01/2015 " + DateTime.Now.Hour + ":" + DateTime.Now.Minute + ":" + DateTime.Now.Second + "").ToString("dd/MM/yyyy HH:mm:ss"); //DateTime.Now.ToString("dd/MM/yyyy HH:mm");
string date2 = DateTime.Parse("01/01/2016 " + DateTime.Now.Hour + ":" + DateTime.Now.Minute + ":" + DateTime.Now.Second + "").ToString("dd/MM/yyyy HH:mm:ss"); //DateTime.Now.ToString("dd/MM/yyyy HH:mm");
cmd.Parameters.Add("@p1", OleDbType.Date).Value = date1;
cmd.Parameters.Add("@p2", OleDbType.Date).Value = date2;
cmd.CommandText = SQL;
int x = cmd.ExecuteNonQuery();
ExcelCon.Close();
return(true);
}
catch { return(false); }
}
protected void Button1_Click(object sender, EventArgs e)
{
String fName = TextBox1.Text.ToString();
String lName = TextBox2.Text.ToString();
String email = TextBox3.Text.ToString();
String country = TextBox4.Text.ToString();
string gender = rb.SelectedItem.Value.ToString();
OleDbConnection conn = new OleDbConnection();
//Use a string variable to hold the ConnectionString.
conn.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;"
+ "Data Source=C:\\Users\\Intag\\Documents\\GitHub\\Form\\Forms\\App_Data\\Database3.accdb";
//new
String query = @"INSERT INTO Users (FirstName,LastName,Email,Country,Gender) VALUES(@fName,@lname,@email,@country,@gender)";
System.Data.OleDb.OleDbCommand cmd = new System.Data.OleDb.OleDbCommand(query, conn);
cmd.Parameters.AddWithValue("@fName", fName);
cmd.Parameters.AddWithValue("@lName", lName);
cmd.Parameters.AddWithValue("@email", email);
cmd.Parameters.AddWithValue("@country", country);
cmd.Parameters.AddWithValue("@gender", gender);
conn.Open();
cmd.ExecuteNonQuery();
Response.Write("<script>alert('Form Successfully Submitted');</script>");
}
public int insertDb(string queryString)
{
System.Data.OleDb.OleDbConnection conn = new
System.Data.OleDb.OleDbConnection();
// TODO: Modify the connection string and include any
// additional required properties for your database.
conn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0; Data source = szglDB1.mdb";
try
{
System.Data.OleDb.OleDbCommand command = new System.Data.OleDb.OleDbCommand(queryString);
command.Connection = conn;
conn.Open();
command.ExecuteNonQuery();
this.Hide();
return(0);
//insert(conn, command);
//return 0;
}
catch (Exception ex)
{
MessageBox.Show("Failed to connect to data source" + ex.ToString());
return(1);
}
finally
{
conn.Close();
}
}
/// <summary>
/// Generic method for issuing simple Update commands to the current VWA4 database,
/// based on provided connection.
/// </summary>
/// <param name="sql">SQL query string to execute.</param>
/// <param name="conn">OleDbConnection to desired database.</param>
/// <param name="trans"></param>
/// <returns>true if successful.</returns>
public static bool Update(string sql, System.Data.OleDb.OleDbConnection conn, System.Data.OleDb.OleDbTransaction trans)
{
// Connect up to the database
int iSqlStatus;
try
{
// Tee up the command
System.Data.OleDb.OleDbCommand cmd = new System.Data.OleDb.OleDbCommand(sql, conn);
if (trans != null)
{
cmd.Transaction = trans;
}
//
// Issue the command
//
iSqlStatus = cmd.ExecuteNonQuery();
//Now check the status
if (iSqlStatus < 0)
{
//DO your failed messaging here
MessageBox.Show(null, "VWA4.NET UPDATE ERROR: Query failed to update with status: " + iSqlStatus, "Update Error");
}
}
catch (Exception ex)
{
MessageBox.Show(null, "VWA4.NET UPDATE Exception: Error " + ex.Message + "!", "Update Error",
MessageBoxButtons.OK, MessageBoxIcon.Error);
return(false);
}
return(iSqlStatus == 0);
}
//刪除項目
private void deldata_btn_Click(object sender, EventArgs e)
{
// === 對 Access 資料庫下SQL語法 ===
//// Transact-SQL 陳述式
String strSQL = "DELETE FROM [WindowsTarget] WHERE id =" + int.Parse(ShowTargetID.Text);
System.Data.OleDb.OleDbConnection oleConn =
new System.Data.OleDb.OleDbConnection("Provider=Microsoft.Jet.OleDb.4.0;Data Source=FreeSpaceEyesDB.mdb");
//// 開啟資料庫連接。
oleConn.Open();
//// OleDbCommand (String, OleDbConnection) : 使用查詢的文字和 OleDbConnection，初始化 OleDbCommand 類別的新執行個體。
System.Data.OleDb.OleDbCommand oleCmd =
new System.Data.OleDb.OleDbCommand(strSQL, oleConn);
oleCmd.ExecuteNonQuery();
//// 關閉資料庫連接。
oleConn.Close();
//重設ui 並且青空 list
ShowTargetID.Text = "_";
ShowTargetName.Text = "";
ShowTargetIP.Text = "";
ShowTargetDomain.Text = "";
ShowTargetUser.Text = "";
ShowTargetPassword.Text = "";
ShowTargetDeviceID.Text = "_";
ShowTargetAlert.Text = "";
ShowTargetRes.Text = "";
ShowTargetWarning.Text = "";
TargetListView.Items.Clear();
//關閉更新即刪除
updata_btn.Enabled = false;
deldata_btn.Enabled = false;
}
protected void Button1_Click(object sender, EventArgs e)
{
if (TextBox1.Text != "" && TextBox2.Text != "" && TextBox3.Text != "" && TextBox4.Text != "" && TextBox5.Text != "" && TextBox6.Text != "")
{
string fname = TextBox1.Text;
string lname = TextBox2.Text;
string uname = TextBox5.Text;
string pass = TextBox6.Text;
string ph = TextBox3.Text;
string addr = TextBox4.Text;
System.Data.OleDb.OleDbConnection conn = new System.Data.OleDb.OleDbConnection();
conn.ConnectionString = @"Provider=Microsoft.ACE.OLEDB.12.0;" +
@"Data source= C:\Users\OK\Documents\Ecafe.accdb";
conn.Open();
String sql = "insert into Users ([Firstname], [Lastname], [Username], [Password],[Type],[Address],[PhoneNo]) values ('" + fname + "','" + lname + "','" + uname + "','" + pass + "','Customer','" + addr + "','" + Convert.ToInt64(ph) + "')";
System.Data.OleDb.OleDbCommand cmd1 = new System.Data.OleDb.OleDbCommand(sql, conn);
var dr1 = cmd1.ExecuteNonQuery();
MessageBox.Show("Successfully Registered.");
Server.Transfer("Login.aspx");
}
else
{
MessageBox.Show("Fill in the form properly");
}
}
public bool set_off(string dev)
{
try
{
string sets = "[" + dev + "]=0";
string y0 = DateTime.Parse("14/12/2014 18:" + DateTime.Now.Minute + "").ToString("dd/MM/yyyy HH:mm");
string y1 = DateTime.Parse("01/01/2015 " + DateTime.Now.ToLocalTime().Hour + ":" + DateTime.Now.ToLocalTime().Minute + "").ToString("dd/MM/yyyy HH:mm");
System.Data.OleDb.OleDbCommand cmd = new System.Data.OleDb.OleDbCommand();
string SQL = null;
ExcelCon = new System.Data.OleDb.OleDbConnection("provider=Microsoft.Jet.OLEDB.4.0;Data Source='" + filename + "';Extended Properties=Excel 8.0;");
ExcelCon.Open();
cmd.Connection = ExcelCon;
SQL = "Update [" + Title + "] set " + sets + " WHERE [RecDateTime] =(SELECT min([RecDateTime]) FROM [" + Title + "] WHERE [RecDateTime] BETWEEN @p1 AND @p2)";
cmd.CommandText = SQL;
string date1 = DateTime.Parse("01/01/2015 " + DateTime.Now.Hour + ":" + DateTime.Now.Minute + ":" + DateTime.Now.Second + "").ToString("dd/MM/yyyy HH:mm:ss"); //DateTime.Now.ToString("dd/MM/yyyy HH:mm");
string date2 = DateTime.Parse("01/01/2016 " + DateTime.Now.Hour + ":" + DateTime.Now.Minute + ":" + DateTime.Now.Second + "").ToString("dd/MM/yyyy HH:mm:ss"); //DateTime.Now.ToString("dd/MM/yyyy HH:mm");
cmd.Parameters.Add("@p1", OleDbType.Date).Value = date1;
cmd.Parameters.Add("@p2", OleDbType.Date).Value = date2;
cmd.CommandText = SQL;
int x = cmd.ExecuteNonQuery();
ExcelCon.Close();
return(true);
}
catch { return(false); }
}
/// <summary>
/// Generic method for issuing simple Insert commands to the current VWA4 database,
/// based on provided connection.
/// </summary>
/// <param name="sql">SQL query string to execute.</param>
/// <param name="conn">OleDbConnection to desired database.</param>
/// <param name="trans"></param>
/// <returns>PK/ID of inserted record.</returns>
public static int Insert(string sql, System.Data.OleDb.OleDbConnection conn, System.Data.OleDb.OleDbTransaction trans)
{
// Connect up to the database
int id = -1;
try
{
// Tee up the command
System.Data.OleDb.OleDbCommand cmd = new System.Data.OleDb.OleDbCommand(sql, conn);
//
// Issue the command
//
if (trans != null)
{
cmd.Transaction = trans;
}
int iSqlStatus = cmd.ExecuteNonQuery();
//Now check the status
if (iSqlStatus <= 0)
{
//DO your failed messaging here
MessageBox.Show(null, "VWA4.NET INSERT ERROR: Query Failed to insert with status: " + iSqlStatus, "Insert Error");
}
cmd.CommandText = "SELECT @@Identity";
id = (int)cmd.ExecuteScalar();
}
catch (Exception ex)
{
MessageBox.Show(null, "VWA4.NET INSERT Exception: Error " + ex.Message + "!", "Insert Error",
MessageBoxButtons.OK, MessageBoxIcon.Error);
}
return(id);
}
public static bool ExecuteNonQuery(System.Data.OleDb.OleDbCommand sqlCmd)
{
bool bCloseConnection = false;
try
{
sqlCmd.Connection = Connection;
if (Transaction != null)
{
sqlCmd.Transaction = Transaction;
}
Cursor.Current = Cursors.WaitCursor;
if (sqlCmd.Connection.State == ConnectionState.Closed)
{
sqlCmd.Connection.Open();
bCloseConnection = true;
}
sqlCmd.ExecuteNonQuery();
return(true);
}
catch (Exception ex)
{
Cursor.Current = Cursors.Default;
MsgBoxX.Show(ex.Message);
return(false);
}
finally
{
Cursor.Current = Cursors.Default;
if (bCloseConnection)
{
sqlCmd.Connection.Close();
}
}
}
/// <summary>
/// 执行SQL语句 返回影响的记录数
/// </summary>
/// <param name="SQLString"></param>
/// <returns></returns>
public int ExecuteSql(string SQLString)
{
System.Data.OleDb.OleDbCommand Cmd = new System.Data.OleDb.OleDbCommand();
Cmd.Connection = _Con;
string sql = "";
try
{
if (_sTrAccessPath == "")
{
sql = SQLString.Replace("#", "'");//如果是SQL数据库则需要将#替换为'
}
else
{
sql = SQLString;
}
Cmd.CommandText = sql;
int i = Cmd.ExecuteNonQuery();
Cmd = null;
return(i);
}
catch (OleDbException e)
{
CLDC_DataCore.Const.GlobalUnit.Logger.Fatal("向数据库插入数据失败:" + sql, e);
return(0);
}
catch (Exception ex)
{
CLDC_DataCore.Const.GlobalUnit.Logger.Fatal("向数据库插入数据失败:", ex);
return(0);
}
}
public override bool CreateTable()
{
if (m_pOleDbConnection != null)
{
try
{
base.CreateTable();
string strCommand = "Create Table " + TableName_TempTable + "("
+ FieldName_IndexID + " int primary key,"
+ FieldName_LineNodeID + " int)";
OleDbCommand oleDbCommand = new OleDbCommand(strCommand, m_pOleDbConnection);
oleDbCommand.ExecuteNonQuery();
nIndex = 0;
return true;
}
catch (Exception ex)
{
LogAPI.WriteErrorLog(ex);
}
}
return false;
}
public override string guardar(Entidades.PlatosCartaPlatos platosCarta)
{
string mensaje = "";
try
{
int cont = 0;
OleDbConnection conexion = Entidades.Conexion.GetInstancia().crearConexion();
OleDbCommand cmdUpdate = new OleDbCommand("Update CartaPlatoPlatos Set Activo = @activo Where IdCartaPlato = @idCartaPlato and IdPlato = @idPlato", conexion);
cmdUpdate.Parameters.AddWithValue("@activo", platosCarta.Activo);
cmdUpdate.Parameters.AddWithValue("@idCartaPlato", platosCarta.IdCartaPlato);
cmdUpdate.Parameters.AddWithValue("@idProducto", platosCarta.IdPlato);
cont = cmdUpdate.ExecuteNonQuery();
mensaje = "Actualizado con exito.";
if (cont == 0)
{
OleDbCommand cmd = new OleDbCommand("Insert Into CartaPlatoPlatos(IdCartaPlato,IdPlato,Activo) Values(@idCartaPlato,@idPlato,@activo)", conexion);
cmd.Parameters.AddWithValue("@idCartaPlato", platosCarta.IdCartaPlato);
cmd.Parameters.AddWithValue("@idPlato", platosCarta.IdPlato);
cmd.Parameters.AddWithValue("@activo", platosCarta.Activo);
cmd.ExecuteNonQuery();
mensaje = "Guardado con exito.";
}
}
catch (Exception ex)
{
mensaje = "Ocurrio un problema al guardar " + ex.Message;
}
return mensaje;
}
protected void addbutton_Click(object sender, EventArgs e)
{
host temphost = new host(hostipform.Text, hostnameform.Text, System.Int32.Parse(hostscanint.Text), grouptextbox.Text);
string strAccessSelect = "SELECT Код FROM hosts";
int lastnum = 0;
OleDbConnection connection = new OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + dbaddress);
OleDbCommand cmd = new OleDbCommand(strAccessSelect, connection);
cmd.CommandText = strAccessSelect;
cmd.Connection = connection;
connection.Open();
OleDbDataReader readID = cmd.ExecuteReader(); // Выполняем команду
while (readID.Read())
{
lastnum = Convert.ToInt32(readID["Код"]); // Присваиваем таймеру значение максимального id
}
readID.Close();
lastnum++;
string strAccessInsert = "INSERT INTO hosts (код,ip,name,mac,scanint,grp) values (" + lastnum + ",'" + temphost.Ip + "','" + temphost.Name + "','" + temphost.Mac + "','" + temphost.Scanint + "','" + temphost.Group + "')";
cmd.CommandText = strAccessInsert;
cmd.ExecuteNonQuery();
connection.Close();
}
/// <summary>
///执行Sql语句
/// </summary>
/// <param name="str_Sql">要执行的Sql语句</param>
public string ExeSql(string str_Sql)
{
string errorstring = Open();
if (errorstring != "OK")
{
return(errorstring);
}
//myCommand = new SqlCommand(str_Sql,myConnection);
myCommand = new System.Data.OleDb.OleDbCommand(str_Sql, myConnection);
try
{
myCommand.ExecuteNonQuery();
}
catch (SqlException e)
{
string errorMessage = e.Message;
return(errorMessage);
}
finally
{
myCommand.Dispose();
}
return("OK");
}
private bool CreateIndex(System.Data.OleDb.OleDbCommand poCmd, string psIdxName, string psTableName, List <string> psColumns, out string psErrMsg)
{
string xsErrMsg = "", xsSQL = "";
xsSQL = "CREATE INDEX [" + psIdxName.Trim() + "] ON [" + psTableName.Trim() + "] (";
for (int xiColm = 0; xiColm < psColumns.Count; xiColm++)
{
if (xiColm > 0)
{
xsSQL += ", ";
}
xsSQL += "[" + psColumns[xiColm].Trim() + "]";
}
xsSQL += ")";
try
{
poCmd.CommandText = xsSQL;
poCmd.CommandType = System.Data.CommandType.Text;
poCmd.ExecuteNonQuery();
}
catch (Exception xoExc)
{
xsErrMsg = xoExc.Message + " (" + xsSQL + ")";
}
psErrMsg = xsErrMsg;
return(xsErrMsg.Length == 0);
}
private void button1_Click(object sender, EventArgs e)
{
if (comboBox1.SelectedIndex != -1)
{
int billno = int.Parse(comboBox1.SelectedItem.ToString());
OleDbConnection cnon = new System.Data.OleDb.OleDbConnection();
cnon.ConnectionString = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=D:\Database.accdb";
System.Data.OleDb.OleDbCommand command = new System.Data.OleDb.OleDbCommand();
command.CommandText = "DELETE FROM Purchase_Bill where Bill_No=" + billno;
cnon.Open();
command.Connection = cnon;
command.ExecuteNonQuery();
OleDbConnection cnon1 = new System.Data.OleDb.OleDbConnection();
cnon1.ConnectionString = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=D:\Database.accdb";
System.Data.OleDb.OleDbCommand command1 = new System.Data.OleDb.OleDbCommand();
command1.CommandText = "DELETE FROM Purchase_Inventory where Bill_No=" + billno;
cnon1.Open();
command1.Connection = cnon1;
command1.ExecuteNonQuery();
MessageBox.Show("Purchase Entry Deleted");
cnon.Close();
this.Dispose();
}
}
// Add new breed to database
public static Boolean addBreed(string[,] breedData)
{
OleDbConnection myConnection = GetConnection();
string myQuery = "INSERT INTO breeds (name, spieces_id, food_cost, housing_cost) VALUES (?, ?, ?, ?)";
OleDbCommand myCommand = new OleDbCommand(myQuery, myConnection);
myCommand.Parameters.Add("@name", OleDbType.VarWChar, 50).Value = breedData[0, 1];
myCommand.Parameters.Add("@spieces_id", OleDbType.Integer, 5).Value = breedData[1, 1];
myCommand.Parameters.Add("@food_cost", OleDbType.Double, 5).Value = breedData[2, 1];
myCommand.Parameters.Add("@housing_cost", OleDbType.Double, 5).Value = breedData[3, 1];
try
{
myConnection.Open();
myCommand.ExecuteNonQuery();
return true;
}
catch (Exception ex)
{
Console.WriteLine("Exception in DBHandler", ex);
return false;
}
finally
{
myConnection.Close();
}
}
/// <summary>執行 SQL 指令</summary>
/// <param name="strSQL">欲執行的 SQL 指令</param>
/// <param name="OleDbConn">OleDbConnection 連線物件(不指定表使用預設連線)</param>
/// <returns>Boolean</returns>
/// <remarks></remarks>
public static bool execOleDbCommand(string strSQL, System.Data.OleDb.OleDbConnection OleDbConn)
{
try
{
if (OleDbConn == null)
{
OleDbConn = createOleDbConnection();
}
System.Data.OleDb.OleDbCommand cmd = new System.Data.OleDb.OleDbCommand(strSQL, OleDbConn);
if (!(OleDbConn.State == ConnectionState.Open))
{
OleDbConn.Open();
}
cmd.ExecuteNonQuery();
}
catch (Exception Ex)
{
//Message.alertMessage("C0002", null, Ex.Message.ToString(), null);
return(false);
}
finally
{
if (!(OleDbConn.State == ConnectionState.Closed))
{
OleDbConn.Close();
}
}
return(true);
}
private void LoadItems()
{
string query = "";
OleDbConnection myConn = new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + Server.MapPath("../App_Data/items.mdb"));
myConn.Open();
for (int index = 0; index <= 24; index ++)
{
query = "update Items set Items = rnd() * 10 where Id = " + index.ToString();
OleDbCommand myComm1 = new OleDbCommand(query, myConn);
myComm1.ExecuteNonQuery();
query = "update Items set Price = rnd() * 100 where Id = " + index.ToString();
OleDbCommand myComm2 = new OleDbCommand(query, myConn);
myComm2.ExecuteNonQuery();
}
query = "SELECT Id, Name, Items, Price FROM Items";
OleDbCommand myComm = new OleDbCommand(query, myConn);
OleDbDataReader myReader = myComm.ExecuteReader();
gridItems.DataSource = myReader;
gridItems.DataBind();
myReader.Close();
}
public static void WriteInExcel(string testerName, string placeName, string placeArea, float slope, string date, string testResult)
{
OleDbConnection excelConnection = new OleDbConnection(OutputFileConnectionString);
using (excelConnection)
{
excelConnection.Open();
DataTable tableSchema = excelConnection.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
string sheetName = tableSchema.Rows[0]["TABLE_NAME"].ToString();
OleDbCommand excelDbCommand = new OleDbCommand(
"INSERT INTO [" + sheetName + "] VALUES (@TesterName, @PlaceName, @PlaceArea, @Slope, @Date, @TestResult)", excelConnection);
excelDbCommand.Parameters.AddWithValue("@TesterName", testerName);
excelDbCommand.Parameters.AddWithValue("@PlaceName", placeName);
excelDbCommand.Parameters.AddWithValue("@PlaceArea", placeArea);
excelDbCommand.Parameters.AddWithValue("@Slope", slope);
excelDbCommand.Parameters.AddWithValue("@Date", date);
excelDbCommand.Parameters.AddWithValue("@testResult", testResult);
excelDbCommand.ExecuteNonQuery();
Console.WriteLine("Added new row in Excel output file!");
}
}
protected void Page_Load(object sender, EventArgs e)
{
if (!this.IsPostBack)
{
string name = Request["name"].ToString();
OleDbConnection con = DB.createDB();
con.Open();
OleDbCommand cmd = new OleDbCommand();//声明一个OleDbCommand的 cmd对象，并将其实例化
cmd.Connection = con;
cmd.CommandText = "select * from tb_qianfei where name='" + name + "'";
cmd.ExecuteNonQuery();
OleDbDataReader sdr = cmd.ExecuteReader();
sdr.Read();
if (name!= "")
{
this.lblqfname.Text = sdr.GetString(0).ToString();
this.lblzz.Text = sdr.GetString(1);
this.lbllb.Text = sdr.GetString(2);
this.lblrqzs.Text = sdr.GetDateTime(3).ToShortDateString();
this.lblzzsj.Text = sdr.GetDateTime(4).ToShortDateString();
this.lblje.Text = sdr.GetString(5).ToString();
this.Lblzt.Text = sdr.GetString(6).ToString();
this.lbljsr.Text = sdr.GetString(7).ToString();
}
else
{
Response.Write("暂无主题,不能显示");
Response.Redirect("Default.aspx");//将该页跳转到指定的页面中
}
}
}
public void AccessGuideJoinExcel(string Access, string AccTable, string Excel)
{
try
{
string tem_sql = ""; //定义字符串
string connstr = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + Access + ";Persist Security Info=True"; //记录连接Access的语句
System.Data.OleDb.OleDbConnection tem_conn = new System.Data.OleDb.OleDbConnection(connstr); //连接Access数据库
System.Data.OleDb.OleDbCommand tem_comm; //定义OleDbCommand类
tem_conn.Open(); //打开连接的Access数据库
tem_sql = "select Count(*) From " + AccTable; //设置SQL语句，获取记录个数
tem_comm = new System.Data.OleDb.OleDbCommand(tem_sql, tem_conn); //实例化OleDbCommand类
int RecordCount = (int)tem_comm.ExecuteScalar(); //执行SQL语句，并返回结果
//每个Sheet只能最多保存65536条记录。
tem_sql = @"select top 65535 * into [Excel 8.0;database=" + Excel + @".xls].[Sheet2] from 帐目"; //记录连接Excel的语句
tem_comm = new System.Data.OleDb.OleDbCommand(tem_sql, tem_conn); //实例化OleDbCommand类
tem_comm.ExecuteNonQuery(); //执行SQL语句，将数据表的内容导入到Excel中
tem_conn.Close(); //关闭连接
tem_conn.Dispose(); //释放资源
tem_conn = null;
MessageBox.Show("导入完成");
}
catch (Exception ex)
{
MessageBox.Show(ex.Message, "提示！");
}
}
private void button8_Click(object sender, EventArgs e)
{
string strFilePath = "Provider=Microsoft.ACE.OLEDB.12.0;Data source=" + Application.StartupPath + "\\makemoney.mdb";
System.Data.OleDb.OleDbConnection con = new OleDbConnection(strFilePath);
try
{
string strRow = dataGridView1.CurrentCell.Value.ToString();
string sql = "delete from 工程参数表 Where 参数名称 = '" + strRow + "'";
con.Open();
System.Data.OleDb.OleDbDataAdapter da = new OleDbDataAdapter(sql, con);
OleDbCommand thisCommand = new System.Data.OleDb.OleDbCommand(sql, con);
thisCommand.ExecuteNonQuery();
//this.工程参数表TableAdapter.Fill(this.makemoneyDataSet1.工程参数表);
}
catch (Exception ex)
{
throw new Exception(ex.ToString());
}
finally
{
con.Close();
con.Dispose();
}
}
static void Main()
{
var excelConn = new OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=..\\..\\TestBook.xlsx; " +
"Extended Properties=\"Excel 12.0 Xml;HDR=YES;\"");
excelConn.Open();
using (excelConn)
{
// Task 6
Console.WriteLine("\n\nName and Score Cells:");
var cmdScores = new OleDbCommand("SELECT * FROM [Scores$]", excelConn);
var reader = cmdScores.ExecuteReader();
using (reader)
{
while (reader.Read())
{
string name = reader["Name"].ToString();
string score = reader["Score"].ToString();
Console.WriteLine("{0,-15} - {1}", name, score);
}
}
// Task 7
Console.WriteLine("\n\nInserting new row into the excel sheet Scores...");
string scName = "Some Student";
double scScore = 21;
var cmdInsertProduct = new OleDbCommand("INSERT INTO [Scores$]([Name],[Score]) VALUES (@scName, @scScore)", excelConn);
cmdInsertProduct.Parameters.AddWithValue("@scName", scName);
cmdInsertProduct.Parameters.AddWithValue("@scScore", scScore);
cmdInsertProduct.ExecuteNonQuery();
Console.WriteLine("Name: {0} - Score: {1}", scName, scScore);
}
}
public static int ExecuteSql(string SQLString, string content)
{
int result;
using (System.Data.OleDb.OleDbConnection oleDbConnection = new System.Data.OleDb.OleDbConnection(DbHelperOleDb.connectionString))
{
System.Data.OleDb.OleDbCommand oleDbCommand = new System.Data.OleDb.OleDbCommand(SQLString, oleDbConnection);
System.Data.OleDb.OleDbParameter oleDbParameter = new System.Data.OleDb.OleDbParameter("@content", System.Data.OleDb.OleDbType.VarChar);
oleDbParameter.Value = content;
oleDbCommand.Parameters.Add(oleDbParameter);
try
{
oleDbConnection.Open();
int num = oleDbCommand.ExecuteNonQuery();
result = num;
}
catch (System.Data.OleDb.OleDbException ex)
{
throw new Exception(ex.Message);
}
finally
{
oleDbCommand.Dispose();
oleDbConnection.Close();
}
}
return(result);
}
public bool DeleteSurplusRows()
{
if (m_pOleDbConnection != null)
{
try
{
//删除反向次数为偶数的记录记录
string strCommand = "Delete * From " + TableName_TempTable + " Where " + FieldName_LineNodeID
+ " in ( Select " + FieldName_LineNodeID + " From ("
+ " Select " + FieldName_LineNodeID + ",Count(*) as RowCount From " + TableName_TempTable + " Group by "+FieldName_LineNodeID
+" ) Where RowCount Mod 2=0 )";
OleDbCommand oleDbCommand = new OleDbCommand(strCommand, m_pOleDbConnection);
oleDbCommand.ExecuteNonQuery();
//删除重复记录（只保留一个）
strCommand = "Delete * From " + TableName_TempTable + " Where " + FieldName_IndexID
+ " in( Select a." + FieldName_IndexID + " From " + TableName_TempTable + " As a," + TableName_TempTable + " As b Where a." + FieldName_LineNodeID + "=b." + FieldName_LineNodeID + " And a." + FieldName_IndexID + "<b." + FieldName_IndexID + ")";
oleDbCommand = new OleDbCommand(strCommand, m_pOleDbConnection);
oleDbCommand.ExecuteNonQuery();
return true;
}
catch (Exception ex)
{
LogAPI.WriteErrorLog(ex);
}
}
return false;
}
public static int ExecuteSqlInsertImg(string strSQL, byte[] fs)
{
int result;
using (System.Data.OleDb.OleDbConnection oleDbConnection = new System.Data.OleDb.OleDbConnection(DbHelperOleDb.connectionString))
{
System.Data.OleDb.OleDbCommand oleDbCommand = new System.Data.OleDb.OleDbCommand(strSQL, oleDbConnection);
System.Data.OleDb.OleDbParameter oleDbParameter = new System.Data.OleDb.OleDbParameter("@fs", System.Data.OleDb.OleDbType.Binary);
oleDbParameter.Value = fs;
oleDbCommand.Parameters.Add(oleDbParameter);
try
{
oleDbConnection.Open();
int num = oleDbCommand.ExecuteNonQuery();
result = num;
}
catch (System.Data.OleDb.OleDbException ex)
{
throw new Exception(ex.Message);
}
finally
{
oleDbCommand.Dispose();
oleDbConnection.Close();
}
}
return(result);
}
public static int Roles_CreateRole (DbConnection connection, string applicationName, string rolename)
{
string appId = (string) DerbyApplicationsHelper.Applications_CreateApplication (connection, applicationName);
if (appId == null)
return 1;
string querySelect = "SELECT RoleName FROM aspnet_Roles WHERE ApplicationId = ? AND LoweredRoleName = ?";
OleDbCommand cmdSelect = new OleDbCommand (querySelect, (OleDbConnection) connection);
AddParameter (cmdSelect, "ApplicationId", appId);
AddParameter (cmdSelect, "LoweredRoleName", rolename.ToLowerInvariant ());
using (OleDbDataReader reader = cmdSelect.ExecuteReader ()) {
if (reader.Read ())
return 2; // role already exists
}
string queryInsert = "INSERT INTO aspnet_Roles (ApplicationId, RoleId, RoleName, LoweredRoleName) VALUES (?, ?, ?, ?)";
OleDbCommand cmdInsert = new OleDbCommand (queryInsert, (OleDbConnection) connection);
AddParameter (cmdInsert, "ApplicationId", appId);
AddParameter (cmdInsert, "RoleId", Guid.NewGuid ().ToString ());
AddParameter (cmdInsert, "RoleName", rolename);
AddParameter (cmdInsert, "LoweredRoleName", rolename.ToLowerInvariant ());
cmdInsert.ExecuteNonQuery ();
return 0;
}
public static void ExecuteSqlTran(Hashtable SQLStringList)
{
using (System.Data.OleDb.OleDbConnection oleDbConnection = new System.Data.OleDb.OleDbConnection(DbHelperOleDb.connectionString))
{
oleDbConnection.Open();
using (System.Data.OleDb.OleDbTransaction oleDbTransaction = oleDbConnection.BeginTransaction())
{
System.Data.OleDb.OleDbCommand oleDbCommand = new System.Data.OleDb.OleDbCommand();
try
{
foreach (DictionaryEntry dictionaryEntry in SQLStringList)
{
string cmdText = dictionaryEntry.Key.ToString();
System.Data.OleDb.OleDbParameter[] cmdParms = (System.Data.OleDb.OleDbParameter[])dictionaryEntry.Value;
DbHelperOleDb.PrepareCommand(oleDbCommand, oleDbConnection, oleDbTransaction, cmdText, cmdParms);
int num = oleDbCommand.ExecuteNonQuery();
oleDbCommand.Parameters.Clear();
oleDbTransaction.Commit();
}
}
catch
{
oleDbTransaction.Rollback();
throw;
}
}
}
}
// Add new pet to database
public static Boolean addPet(string[,] petData)
{
OleDbConnection myConnection = GetConnection();
string myQuery = "INSERT INTO pets (name, breed_id, spieces_id, sanctuary_id, age, gender, weight, bills, rescue_date, picture_path) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";
OleDbCommand myCommand = new OleDbCommand(myQuery, myConnection);
myCommand.Parameters.Add("@name", OleDbType.VarWChar, 50).Value = petData[0, 1];
myCommand.Parameters.Add("@breed_id", OleDbType.Integer, 5).Value = petData[1, 1];
myCommand.Parameters.Add("@spieces_id", OleDbType.Integer, 5).Value = petData[2, 1];
myCommand.Parameters.Add("@sanctuary_id", OleDbType.Integer, 5).Value = petData[3, 1];
myCommand.Parameters.Add("@age", OleDbType.Integer, 3).Value = petData[4, 1];
myCommand.Parameters.Add("@gender", OleDbType.Integer, 1).Value = petData[5, 1];
myCommand.Parameters.Add("@weight", OleDbType.Double, 5).Value = petData[6, 1];
myCommand.Parameters.Add("@bills", OleDbType.Double, 5).Value = petData[7, 1];
myCommand.Parameters.Add("@rescue_date", OleDbType.DBDate).Value = petData[8, 1];
myCommand.Parameters.Add("@picture_path", OleDbType.VarWChar, 50).Value = petData[9, 1];
try
{
myConnection.Open();
myCommand.ExecuteNonQuery();
return true;
}
catch (Exception ex)
{
Console.WriteLine("Exception in DBHandler", ex);
return false;
}
finally
{
myConnection.Close();
}
}
public static void ExecuteSqlTran(ArrayList SQLStringList)
{
using (System.Data.OleDb.OleDbConnection oleDbConnection = new System.Data.OleDb.OleDbConnection(DbHelperOleDb.connectionString))
{
oleDbConnection.Open();
System.Data.OleDb.OleDbCommand oleDbCommand = new System.Data.OleDb.OleDbCommand();
oleDbCommand.Connection = oleDbConnection;
System.Data.OleDb.OleDbTransaction oleDbTransaction = oleDbConnection.BeginTransaction();
oleDbCommand.Transaction = oleDbTransaction;
try
{
for (int i = 0; i < SQLStringList.Count; i++)
{
string text = SQLStringList[i].ToString();
if (text.Trim().Length > 1)
{
oleDbCommand.CommandText = text;
oleDbCommand.ExecuteNonQuery();
}
}
oleDbTransaction.Commit();
}
catch (System.Data.OleDb.OleDbException ex)
{
oleDbTransaction.Rollback();
throw new Exception(ex.Message);
}
}
}
protected void BtnLackRelease_Click(object sender, EventArgs e)
{
string objid = HidObjId.Value;
string rowversion = HidRowversion.Value;
string action = HidAction.Value;
decimal releaseQty = Convert.ToDecimal(HidReleaseQty.Value);
if (objid == "" || rowversion == "" || action == "")
{
Misc.Message(this.GetType(), ClientScript, "�´�ʧ�ܣ���������");
return;
}
if (DdlLackReason.SelectedValue == "0")
{
Misc.Message(this.GetType(), ClientScript, "����ʧ�ܣ���ѡ��ȱ��ԭ��");
return;
}
if (action == "cancel")
{
using (OleDbConnection conn = new OleDbConnection(DBHelper.OleConnectionString))
{
if (conn.State != ConnectionState.Open) conn.Open();
OleDbCommand cmd = new OleDbCommand();
cmd.Connection = conn;
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = "jp_pkg_requisition_api.cancel_";
cmd.Parameters.Add("v_objid", OleDbType.VarChar).Value = objid;
cmd.Parameters.Add("v_rowversion", OleDbType.VarChar).Value = rowversion;
cmd.Parameters.Add("v_lack_msg", OleDbType.VarChar).Value = DdlLackReason.SelectedItem.Text;
cmd.Parameters.Add("v_user", OleDbType.VarChar).Value = ((Authentication.LOGININFO)Session["USERINFO"]).UserID;
cmd.ExecuteNonQuery();
GVDataDataBind();
}
return;
}
if (action == "release")
{
using (OleDbConnection conn = new OleDbConnection(DBHelper.OleConnectionString))
{
if (conn.State != ConnectionState.Open) conn.Open();
OleDbCommand cmd = new OleDbCommand();
cmd.Connection = conn;
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = "jp_pkg_requisition_api.confirm_";
cmd.Parameters.Add("v_objid", OleDbType.VarChar).Value = objid;
cmd.Parameters.Add("v_rowversion", OleDbType.VarChar).Value = rowversion;
cmd.Parameters.Add("v_lack_msg", OleDbType.VarChar).Value = DdlLackReason.SelectedItem.Text;
cmd.Parameters.Add("v_released_qty", OleDbType.Decimal).Value = releaseQty;
cmd.Parameters.Add("v_user", OleDbType.VarChar).Value = ((Authentication.LOGININFO)Session["USERINFO"]).UserID;
cmd.ExecuteNonQuery();
GVDataDataBind();
}
return;
}
}
// DB Structure creation method
public static void DBStructCreate()
{
if (connect.State == System.Data.ConnectionState.Closed) // If no connection to DB
{
Console.WriteLine("No DB connection! Connecting");
DBConnect();
}
int z = 1; // Query counter
string[] SqlQuery = new string[] // Sql query array
{
"CREATE TABLE [tfr_account_statement] ([id] counter, [stock_ticker] string, [trade_status] string, [trade_direction] string, [trade_opend_date] date, [trade_open_price] double, [trade_closed_date] date, [stock_quantity] int, [trade_close_price] double, [profit_per_stock] double, [profit_per_quantity] double, [account_balance] double, [trade_profit_prcnt] double, [accumulated_sum_prcnt] string, [open_message] string, [close_message] string)"
};
foreach (string i in SqlQuery)
{
Console.WriteLine("DB create structure. Query №: " + z);
var command = new oledb.OleDbCommand(i, connect);
try
{
command.ExecuteNonQuery(); // Execute query
Console.WriteLine("DB create structure. Table created! ");
}
catch (Exception err)
{
Console.WriteLine("DB create structure. Table is not created! " + err);
}
z++; // Increase counter
}
}
public static void RegisterUser(string firstName, string LastName, string Phone, string Initials, string OrgCode, string Corr)
{
//System.DateTime DateAdded = System.DateTime.Today;
if (Initials.Length == 0)
{
Initials = "";
}
if (OrgCode.Length == 0)
{
OrgCode = "";
}
if (Corr.Length == 0)
{
Corr = "";
}
if (Phone.Length == 0)
{
Phone = "";
}
try {
System.Data.OleDb.OleDbConnection ConnReg = new System.Data.OleDb.OleDbConnection();
System.Data.OleDb.OleDbCommand CmdReg = new System.Data.OleDb.OleDbCommand();
ConnReg.ConnectionString = ConnectionString.CSUsersTables(); //"Provider=VFPOLEDB.1;Data Source=c:\\data;Database='';Mode=Share Deny None;Extended Properties='';User ID='';Password='';Mask Password=False;Cache Authentication=False;Encrypt Password=False;Collating Sequence=MACHINE";
CmdReg.CommandText = Queries.RegisterNewUser(firstName, LastName, Phone, Initials, OrgCode, Corr); //"INSERT into Users (Fname,Lname,Phone,Initials,Orgcode,Corr,Dateadded) values ('" + firstName + "','" + LastName + "','" + Phone + "','" + Initials + "','" + OrgCode + "','" + Corr + "', Date())";
CmdReg.Connection = ConnReg;
CmdReg.Connection.Open();
CmdReg.ExecuteNonQuery();
}
catch (System.Exception ex) {
Console.Write(ex.Message);
}
}//end of method
public void updateProductPrice(ProductInBag Product)
{
OleDbConnection myConn = new OleDbConnection(Connstring.getConnectionString());
OleDbCommand myCmd;
OleDbDataAdapter myDataAdapter = new OleDbDataAdapter();
try
{
myCmd = new OleDbCommand("UpdateUnitPrice", myConn);
myCmd.CommandType = CommandType.StoredProcedure;
OleDbParameter objParam;
objParam = myCmd.Parameters.Add("@UnitPrice", OleDbType.Decimal);
objParam.Direction = ParameterDirection.Input;
objParam.Value = Product.Price;
objParam = myCmd.Parameters.Add("@ProductID", OleDbType.Integer);
objParam.Direction = ParameterDirection.Input;
objParam.Value = Product.ProdID;
myConn.Open();
myCmd.ExecuteNonQuery();
}
catch (Exception ex)
{
throw ex;
}
finally
{
myConn.Close();
}
}
public static int AffectData(string TSQL, IDbConnection myConn, IDbTransaction myTrans, List<IDbDataParameter> myParams)
{
bool mustClose = false;
if (myConn == null)
{
mustClose = true;
myConn = clsConn.getConnOLE();
}
if (myConn.State != ConnectionState.Open)
myConn.Open();
OleDbCommand myCMD = new OleDbCommand();
//
myCMD.Connection = myConn as OleDbConnection;
if (myTrans != null)
myCMD.Transaction = myTrans as OleDbTransaction;
//
myCMD.CommandType = CommandType.Text;
myCMD.CommandText = TSQL;
myCMD.CommandTimeout = 180000;//3 phut
//
if (myParams != null)
AttachParameters(myCMD, myParams);
int CMDResult = myCMD.ExecuteNonQuery();
//
if (mustClose) myConn.Close();
return CMDResult;
}
private void addToDatabase(string sql)
{
OleDbConnection db = null;
try
{
db = new OleDbConnection();
db.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data source=" + mFileName;
db.Open();
OleDbCommand command = new OleDbCommand(sql, db);
command.ExecuteNonQuery();
}
catch (Exception ex)
{
showErrorMessage(ex.Message);
}
finally
{
if (db != null)
{
db.Close();
}
}
}
protected void Button1_Click(object sender, EventArgs e)
{
try
{
OleDbConnectionStringBuilder sb = new OleDbConnectionStringBuilder();
sb.Provider = "Microsoft.ACE.OLEDB.12.0";
sb.DataSource = Server.MapPath("/vedb01/uploads/db1.accdb");
OleDbConnection conn = new OleDbConnection(sb.ConnectionString);
conn.Open();
string updateQuery = "UPDATE Bag SET BagName=@name, Supplier=@supplier, Color=@color, Category=@category, Price=@price, Description=@description WHERE BagID= @ID";
OleDbCommand com = new OleDbCommand(updateQuery, conn);
com.Parameters.AddWithValue("@ID", txtBagId.Text);
com.Parameters.AddWithValue("@name", txtBagName.Text);
com.Parameters.AddWithValue("@supplier", txtSupplier.Text);
com.Parameters.AddWithValue("@color", txtColor.Text);
com.Parameters.AddWithValue("@category", txtCategory.Text);
com.Parameters.AddWithValue("@price", txtPrice.Text);
com.Parameters.AddWithValue("@description", txtDescription.Text);
com.ExecuteNonQuery();
lblMessage.Text = "The bag No " + txtBagId.Text +" was updated successfully " + txtBagName.Text + " !";
conn.Close();
}
catch (Exception ex)
{
Response.Write("Error: " + ex.ToString());
lblMessage.Text = "Error !";
}
}
protected void btnAddBag_Click(object sender, EventArgs e)
{
try
{
OleDbConnectionStringBuilder sb = new OleDbConnectionStringBuilder();
sb.Provider = "Microsoft.ACE.OLEDB.12.0";
sb.DataSource = Server.MapPath("/vedb01/uploads/db1.accdb");
OleDbConnection conn = new OleDbConnection(sb.ConnectionString);
conn.Open();
string insertQuery = "insert into Bag ( [BagName], [Supplier], [Color], [Category], [Price], [Description]) values (@name ,@supplier ,@color ,@category ,@price ,@description)";
OleDbCommand com = new OleDbCommand(insertQuery, conn);
com.Parameters.AddWithValue("@name", txtBagName.Text);
com.Parameters.AddWithValue("@supplier", txtSupplier.Text);
com.Parameters.AddWithValue("@color", txtColor.Text);
com.Parameters.AddWithValue("@category", txtCategory.Text);
com.Parameters.AddWithValue("@price", txtPrice.Text);
com.Parameters.AddWithValue("@description", txtDescription.Text);
com.ExecuteNonQuery();
lblMessage.Text = "The bag was added successfully " + txtBagName.Text + " !";
conn.Close();
}
catch (Exception ex)
{
Response.Write("Error: " + ex.ToString());
lblMessage.Text = "Error" + txtBagName.Text + " !";
}
}
/// <summary>
/// 执行多条SQL语句，实现数据库事务。
/// </summary>
/// <param name="SQLStringList">多条SQL语句</param>
public static void ExecuteSqlTran(ArrayList SQLStringList)
{
using (OleDbConnection conn = new OleDbConnection(connectionString))
{
conn.Open();
OleDbCommand cmd = new OleDbCommand();
cmd.Connection = conn;
OleDbTransaction tx = conn.BeginTransaction();
cmd.Transaction = tx;
try
{
for (int n = 0; n < SQLStringList.Count; n++)
{
string strsql = SQLStringList[n].ToString();
if (strsql.Trim().Length > 1)
{
cmd.CommandText = strsql;
cmd.ExecuteNonQuery();
}
}
tx.Commit();
}
catch (System.Data.OleDb.OleDbException E)
{
tx.Rollback();
throw new Exception(E.Message);
}
}
}
protected void ImageButton1_Click(object sender, ImageClickEventArgs e)
{
String fName = TextBox1.Text.ToString();
String lName = TextBox2.Text.ToString();
String userName = TextBox3.Text.ToString();
String pasword = TextBox4.Text.ToString();
OleDbConnection conn = new OleDbConnection();
//Use a string variable to hold the ConnectionString.
conn.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;"
+ "Data Source=C:\\Users\\Intag\\Documents\\GitHub\\Lab2new\\lms\\lms\\App_Data\\Database1.accdb";
//new
String query = @"INSERT INTO Users (FirstName,LastName,Username,Pasword) VALUES(@fName,@lname,@userName,@pasword)";
System.Data.OleDb.OleDbCommand cmd = new System.Data.OleDb.OleDbCommand(query, conn);
cmd.Parameters.AddWithValue("@fName", fName);
cmd.Parameters.AddWithValue("@lName", lName);
cmd.Parameters.AddWithValue("@userName", userName);
cmd.Parameters.AddWithValue("@pasword", pasword);
conn.Open();
cmd.ExecuteNonQuery();
Response.Write("<script>alert('Registration successful');</script>");
//Response.Redirect("search.aspx");
Response.Write("<script>window.location.href='search.aspx';</script>");
conn.Close();
}
private static int WriteToExcelFile(string connExcel, OleDbConnection dbCon)
{
var excelSchema = dbCon.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
var sheetName = excelSchema.Rows[0]["TABLE_NAME"].ToString();
OleDbCommand command = new OleDbCommand("INSERT INTO [" + sheetName +
"] VALUES(@name, @score)", dbCon);
string name = "Evlogy Christov";
int score = 34;
command.Parameters.AddWithValue("@name", name);
command.Parameters.AddWithValue("@score", score);
int queryResult = command.ExecuteNonQuery();
command.Parameters.Clear();
name = "Christian Zaklev";
score = 32;
command.Parameters.AddWithValue("@name", name);
command.Parameters.AddWithValue("@score", score);
queryResult += command.ExecuteNonQuery();
// a ready-made way to "invent" new parameters:
for (int i = 5; i < 10; i++)
{
command.Parameters.Clear();
command.Parameters.AddWithValue("@name", "Student" + i);
command.Parameters.AddWithValue("@score", i);
queryResult += command.ExecuteNonQuery();
}
return queryResult;
}
public static int SaveRecord(string sql)
{
const int rv = 0;
try
{
string connectionString = ConfigurationManager.ConnectionStrings["LA3Access"].ConnectionString;
using (var conn = new OleDbConnection(connectionString))
{
conn.Open();
var cmGetID = new OleDbCommand("SELECT @@IDENTITY", conn);
var comm = new OleDbCommand(sql, conn) { CommandType = CommandType.Text };
comm.ExecuteNonQuery();
var ds = new DataSet();
var adapt = new OleDbDataAdapter(cmGetID);
adapt.Fill(ds);
adapt.Dispose();
cmGetID.Dispose();
return int.Parse(ds.Tables[0].Rows[0][0].ToString());
}
}
catch (Exception)
{
}
return rv;
}