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;
}
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 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("项目修改成功！", "提示");
}
}
}
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();
}
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 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 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();
}
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() }));
}
}
}
}
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 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 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 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 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;
}
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);
}
}
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");//将该页跳转到指定的页面中
}
}
}
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();
}
// 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();
}
}
// 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();
}
}
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();
}
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;
}
}
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;
}
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!");
}
}
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;
}
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();
}
}
}
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;
}
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 [email protected], [email protected], [email protected], [email protected], [email protected], [email protected] 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);
}
}
}
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;
}
