public void NapolniFormaProizvod()
{
String komanda = "EXECUTE spPodatociProizvod";
OleDbConnection OleCn = new System.Data.OleDb.OleDbConnection(konekcija);
OleDbCommand OleCm = new System.Data.OleDb.OleDbCommand(komanda, OleCn);
OleCm.Parameters.Add(new OleDbParameter("@Code", this.vnesiTexBox.Text.Trim()));
OleDbDataReader dt;
try
{
OleCn.Open();
dt = OleCm.ExecuteReader();
if (dt.Read())
{
groupLbl.Text = dt["Ime_PG"].ToString();
descriptionLbl.Text = dt["Description"].ToString();
Sifra_Proizvod = dt["Sifra_Pro"].ToString();
}
dt.Close();
OleCn.Close();
}
catch (Exception ex)
{
OleCn.Close();
MessageBox.Show(ex.Message);
}
}
/// <summary>
/// 读取Excel的方法
/// </summary>
/// <param name="FilePath">xls文件路径</param>
/// <param name="SelectSQL">查询SQL语句</param>
/// <returns>DataSet</returns>
public static DataSet SelectFromXLS(string FilePath, string SelectSQL)
{
string connStr = "Provider = Microsoft.Jet.OLEDB.4.0 ; Data Source = '" + FilePath + "';Extended Properties=\"Excel 8.0; HDR=YES; IMEX=1;\"";
System.Data.OleDb.OleDbConnection conn = new System.Data.OleDb.OleDbConnection(connStr);
System.Data.OleDb.OleDbDataAdapter da = null;
DataSet ds = new DataSet();
try
{
conn.Open();
da = new System.Data.OleDb.OleDbDataAdapter(SelectSQL, conn);
da.Fill(ds, "SelectResult");
}
catch (Exception e)
{
conn.Close();
Logger.LogHelper.LogServiceWebError("iTrackStar.MYHM.Utility.ExcelHandler.SelectFromXLS()" + e.ToString());
//throw e;
}
finally
{
conn.Close();
}
return(ds);
}
public nouveau_etudiant(string l)
{
login = l;
InitializeComponent();
string chaine = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:/Users/dhib/Desktop/Professeur.accdb";
OleDbConnection con = new OleDbConnection(chaine);
OleDbCommand cmd = con.CreateCommand();
cmd.CommandText = "select module_ID,nom_module from module1 where login_enseignant='"+login+"'";
try
{
con.Open();
OleDbDataReader dr = cmd.ExecuteReader();
while (dr.Read())
{
id = (int)dr.GetValue(0);
nom = (string)dr.GetValue(1);
hash.Add(nom, id);
checkedListBox1.Items.Add(nom);
}
dr.Close();
con.Close();
}
catch (Exception ex) { MessageBox.Show("Erreur : " + ex.Message); con.Close(); }
}
protected void Button1_Click(object sender, EventArgs e)
{
Label6.Visible = false;
string id = "";
string isim = "";
string durum = "";
OleDbConnection cnn = new OleDbConnection("Provider=Microsoft.Jet.Oledb.4.0; Data Source=" + Server.MapPath("App_Data/database.mdb"));
cnn.Open();
OleDbCommand cmd = new OleDbCommand("select * from uyeler where uyeadi='" + TextBox1.Text + "' and parola='" + TextBox2.Text + "'", cnn);
OleDbDataReader rdr = cmd.ExecuteReader();
if (rdr.Read() == false)
{
Label6.Visible = true;
Label6.Text = "Üzgünüz ama hatalı kullanıcı adı ya da parola girdiniz.";
}
else
{
id = rdr[0].ToString();
isim = rdr[3].ToString();
durum = rdr[5].ToString();
cnn.Close();
Session.Add("oturumid", id);
Session.Add("oturumadi", isim);
Session.Add("oturumdurum", durum);
Response.Redirect("uyepaneli.aspx");
}
cnn.Close();
}
//Create an Excel file with 2 columns: name and score:
//Write a program that reads your MS Excel file through
//the OLE DB data provider and displays the name and score row by row.
static void Main()
{
OleDbConnection dbConn = new OleDbConnection(@"Provider=Microsoft.ACE.OLEDB.12.0;" +
@"Data Source=D:\Telerik\DataBases\HW\ADONET\06. ReadExcel\Table.xlsx;Extended Properties=""Excel 12.0 XML;HDR=Yes""");
OleDbCommand myCommand = new OleDbCommand("select * from [Sheet1$]", dbConn);
dbConn.Open();
//First way
//using (dbConn) - I think it is better to use dbConn in using clause, but for the demo issues i dont use using.
//{
OleDbDataReader reader = myCommand.ExecuteReader();
while (reader.Read())
{
string name = (string)reader["Name"];
double score = (double)reader["Score"];
Console.WriteLine("{0} - score: {1}", name, score);
}
//}
dbConn.Close();
//Second way
dbConn.Open();
Console.WriteLine();
Console.WriteLine("Second Way");
Console.WriteLine("----------");
DataTable dataSet = new DataTable();
OleDbDataAdapter adapter = new OleDbDataAdapter("select * from [Sheet1$]", dbConn);
adapter.Fill(dataSet);
foreach (DataRow item in dataSet.Rows)
{
Console.WriteLine("{0}|{1}", item.ItemArray[0], item.ItemArray[1]);
}
dbConn.Close();
}
/// <summary>
/// Gathers existing customer information from the database to display in the GUI
/// </summary>
public List <String> getCustomerInfo(String email)
{
List <String> customerInformation = new List <String>();
try
{
connection.Open();
command.CommandText = "SELECT CustomerID FROM Customer WHERE CustomerEmail ='" + email + "'";
customerInformation.Add(command.ExecuteScalar().ToString());
command.CommandText = "SELECT FirstName FROM Customer WHERE CustomerEmail ='" + email + "'";
customerInformation.Add(command.ExecuteScalar().ToString());
command.CommandText = "SELECT LastName FROM Customer WHERE CustomerEmail ='" + email + "'";
customerInformation.Add(command.ExecuteScalar().ToString());
command.CommandText = "SELECT CustomerAddress FROM Customer WHERE CustomerEmail ='" + email + "'";
customerInformation.Add(command.ExecuteScalar().ToString());
command.CommandText = "SELECT CustomerNumber FROM Customer WHERE CustomerEmail ='" + email + "'";
customerInformation.Add(command.ExecuteScalar().ToString());
command.CommandText = "SELECT CreditCardNumber FROM Customer WHERE CustomerEmail ='" + email + "'";
customerInformation.Add(command.ExecuteScalar().ToString());
}
catch (Exception)
{
throw;
}
finally
{
if (connection != null)
{
connection.Close();
}
}
return(customerInformation);
}
protected void LinkButton5_Click(object sender, EventArgs e)
{
OleDbConnection baglanti = new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source="+Server.MapPath(@"App_Data\db.mdb")+";Persist Security Info=True");
OleDbCommand cmd = new OleDbCommand("insert into siparis (UYEID,ADRES,TEL,DURUMU) values (@UYEID,@ADRES,@TEL,@DURUMU)",baglanti);
cmd.Parameters.AddWithValue("@UYEID",((DataTable)(Session["UYE"])).Rows[0][0].ToString());
cmd.Parameters.AddWithValue("@ADRES",TextBox1.Text);
cmd.Parameters.AddWithValue("@TEL",TextBox2.Text);
cmd.Parameters.AddWithValue("@DURUMU","Beklemede");
baglanti.Open();
cmd.ExecuteNonQuery();
baglanti.Close();
OleDbDataAdapter da = new OleDbDataAdapter("select ID from siparis where UYEID=@UYEID order by ID DESC",baglanti);
da.SelectCommand.Parameters.AddWithValue("@UYEID",((DataTable)(Session["UYE"])).Rows[0][0].ToString());
DataTable dt = new DataTable();
da.Fill(dt);
DataTable sdt=((DataTable)(Session["SEPET"]));
for (int i = 0; i < sdt.Rows.Count; i++)
{
OleDbCommand scmd = new OleDbCommand("insert into sepet (SIPID,URUNID,URUN,FIYAT,ADET,TOPLAM) VALUES (@SIPID,@URUNID,@URUN,@FIYAT,@ADET,@TOPLAM)",baglanti);
scmd.Parameters.Clear();
scmd.Parameters.AddWithValue("@SIPID",dt.Rows[0][0].ToString());
scmd.Parameters.AddWithValue("@URUNID",sdt.Rows[i]["ID"].ToString());
scmd.Parameters.AddWithValue("@URUN",sdt.Rows[i]["URUN"].ToString());
scmd.Parameters.AddWithValue("@FIYAT",sdt.Rows[i]["FIYAT"].ToString());
scmd.Parameters.AddWithValue("@ADET",sdt.Rows[i]["ADET"].ToString());
scmd.Parameters.AddWithValue("@TOPLAM", sdt.Rows[i]["TOPLAM"].ToString());
baglanti.Open();
scmd.ExecuteNonQuery();
baglanti.Close();
}
Label2.Text = "Sipariş Takip Numarasınız : "+dt.Rows[0][0].ToString();
}
public List <TTopic> getArchivedTopics(string course_id)
{
//--Data Base Access Variables--
System.Data.OleDb.OleDbConnection dbConnection = new System.Data.OleDb.OleDbConnection(connectionString);
System.Data.OleDb.OleDbCommand dbCommand = new OleDbCommand();
dbCommand.Connection = dbConnection;
System.Data.OleDb.OleDbDataReader dbDataReader;
//------------------------------
dbCommand.CommandText = "SELECT * FROM Topics WHERE course_id = '" + course_id + "' AND finishDateTime IS NOT NULL";
dbConnection.Close();
dbConnection.Open();
dbDataReader = dbCommand.ExecuteReader();
TTopic tTopicAux;
List <TTopic> startedTopics = new List <TTopic>();
while (dbDataReader.Read())
{
tTopicAux = new TTopic(getCourse(dbDataReader["course_id"].ToString()), dbDataReader["title"].ToString());
tTopicAux.id = Convert.ToInt32(dbDataReader["id"]);
tTopicAux.starterTeacher = getUser(dbDataReader["starterTeacher"].ToString());
tTopicAux.startDateTime = Convert.ToDateTime(dbDataReader["startDateTime"]);
tTopicAux.finishDateTime = Convert.ToDateTime(dbDataReader["finishDateTime"]);
startedTopics.Add(tTopicAux);
}
dbDataReader.Close();
dbConnection.Close();
return(startedTopics);
}
public void finishTopic(TTopic tTopic)
{
//--Data Base Access Variables--
System.Data.OleDb.OleDbConnection dbConnection = new System.Data.OleDb.OleDbConnection(connectionString);
System.Data.OleDb.OleDbCommand dbCommand = new OleDbCommand();
dbCommand.Connection = dbConnection;
System.Data.OleDb.OleDbTransaction dbTransaction;
//-----------------------------
dbConnection.Close();
dbConnection.Open();
dbTransaction = dbConnection.BeginTransaction();
dbCommand.Transaction = dbTransaction;
try
{
//---Begin Transaction---
tTopic.finishDateTime = DateTime.Now;
//UPDATE Topic
dbCommand.CommandText = "UPDATE Topics SET finishDateTime = '" + tTopic.finishDateTime.ToString() + "' WHERE id = " + tTopic.id.ToString();
dbCommand.ExecuteNonQuery();;
dbTransaction.Commit();
//----End Transaction----
}
catch
{
dbTransaction.Rollback();
dbCommand.Transaction = null;
dbTransaction = null;
dbConnection.Close();
}
dbCommand.Transaction = null;
dbTransaction = null;
dbConnection.Close();
}
}//end DBSetup()
/******************************SELECT METHOD***********************************************/
public void SelectDB(string id)
{
DBSetup();
cmd = "Select * from PositionInfo where EmpId = '" + id + "'";
OleDbDataAdapter.SelectCommand.CommandText = cmd;
OleDbDataAdapter.SelectCommand.Connection = OleDbConnection;
Console.WriteLine(cmd);
try
{
Console.WriteLine("Before Open");
OleDbConnection.Open();
Console.WriteLine("After Open");
System.Data.OleDb.OleDbDataReader dr;
dr = OleDbDataAdapter.SelectCommand.ExecuteReader();
Console.WriteLine("After Execute");
dr.Read();
EmpId = id;
JobTitle = dr.GetValue(1) + "";
JobDescription = dr.GetValue(2) + "";
Salary = Double.Parse(dr.GetValue(3) + "");
}
catch (Exception ex)
{
Console.WriteLine("hello" + ex);
}
finally
{
OleDbConnection.Close();
}
}//end SelectDB()
protected void Button2_Click(object sender, EventArgs e)
{
string s_add, r_add, message, heading,muid;
s_add = Session["us_name"].ToString();
r_add = TextBox1.Text;
heading = TextBox3.Text;
message = TextBox2.Text;
rec_mail te = new rec_mail();
conn = new OleDbConnection("Provider=MSDAORA;Data Source=orcl;Persist Security Info=True;Password=db_mail;User ID=db_mail");
conn.Open();
cmd = new OleDbCommand("insert into messages values(m_id.nextval,'" + heading + "','" + message + "'," + file_name + "',sysdate,sysdate,'Draft')", conn);
dr = cmd.ExecuteReader();
cmd = new OleDbCommand("insert into mail_exchange values('" + s_add + "',m_id.currval,sysdate,sysdate,'" + r_add + "')", conn);
dr = cmd.ExecuteReader();
cmd = new OleDbCommand("insert into draft values(m_id.currval)", conn);
dr = cmd.ExecuteReader();
dr.Close();
cmd = new OleDbCommand("select m_id.currval from dual", conn);
dr = cmd.ExecuteReader();
dr.Read();
muid = dr[0].ToString();
dr.Close();
conn.Close();
Label1.Text = "Message Saved. Your message id is " + muid;
Label1.Visible = true;
dr.Close();
conn.Close();
}
protected void Button1_Click(object sender, EventArgs e)
{
string b = RID;// Request["QUERY_STRING"];
string a = Session["mzj"].ToString().Trim();
if (a == "没中奖")
{
string url = Request.Url.ToString();
Response.Redirect(url);
}
else
{
string PathDataBase = System.IO.Path.Combine(Server.MapPath("~"), "USER_DIR\\SYSUSER\\LotteryTicket\\db.dll");//"USER_DIR\\SYSUSER\\SYSSET\\" +
String source = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + PathDataBase;
OleDbConnection con = new OleDbConnection(source);
//2、打开连接 C#操作Access之按列读取mdb
con.Open();
DbCommand com = new OleDbCommand();
com.Connection = con;
com.CommandText = "select jp from wx_ggk_jp where name='" + b + "'";
DbDataReader reader = com.ExecuteReader();
if (reader.Read())
{
string jp = reader["jp"].ToString();
bd.Style["Display"] = "None";//转盘层
csyw.Style["Display"] = "None";//次数用完次数
zjl.Style["Display"] = "Block";//中奖页面
zjjs.Style["Display"] = "None";//中奖结束页面
Label3.Text = "恭喜你以中奖" + jp + "请填写相关信息";
con.Close();
}
else
{
OleDbCommand comd = new OleDbCommand();
//连接字符串
comd.CommandText = "insert into wx_ggk_jp (name,jp,sjh) values ('" + b + "','" + a + "','手机号')";
comd.Connection = con;
//执行命令，将结果返回
int sm = comd.ExecuteNonQuery();
con.Close();
//释放资源
con.Dispose();
bd.Style["Display"] = "None";//转盘层
csyw.Style["Display"] = "None";//次数用完次数
zjl.Style["Display"] = "Block";//中奖页面
zjjs.Style["Display"] = "None";//中奖结束页面
Label3.Text = "恭喜你以中奖" + a + "请填写相关信息";
}
}
}
/// <summary>
/// ���뵽dataset
/// </summary>
/// <param name="Path"></param>
/// <param name="sheetname"></param>
/// <returns></returns>
public DataSet Getexcelds(string Path, String sheetname)
{
string strConn = string.Format("Provider=Microsoft.Jet.OLEDB.4.0;Data Source='" + Path + "';Extended Properties='Excel 8.0;HDR=YES;IMEX=1'");//��һ����Ϊ��
OleDbConnection conn = new OleDbConnection(strConn);
conn.Open();
try
{
string strExcel = "select * from [" + sheetname + "]";
if (sheetname.IndexOf('$') < 0)//if it have not $
{
strExcel = "select * from[" + sheetname + "$]";
}
OleDbDataAdapter da = new OleDbDataAdapter(strExcel, strConn);
DataSet ds = new DataSet();
da.Fill(ds, "��˾Ա��");
conn.Close();
return ds;
}
catch (Exception error)
{
return null;
}
finally
{
conn.Close();
}
}
public bool HaveDB()
{
string str_Sql_DB = "select * from sysdatabases where name='CityEduManage'";
//myAdapter = new SqlDataAdapter(str_Sql,myConnection)
myAdapter = new System.Data.OleDb.OleDbDataAdapter(str_Sql_DB, myConnection);
myAdapter.TableMappings.Add("Table", "TableIn");
ds = new DataSet();
try
{
myAdapter.Fill(ds);
}
catch (SqlException e)
{
string errorMessage = e.Message;
}
try
{
int count = ds.Tables[0].Rows.Count;
ds.Clear();
if (count == 1)
{
return(true);
}
}
catch
{
ds.Clear();
myConnection.Close();
return(false);
}
return(false);
}
/// <summary>
/// 取出sheet
/// </summary>
/// <param name="filePath"></param>
/// <returns></returns>
public string getFirstSheetName(string filePath)
{
string ret = "";
string strConn = sConnstring;
//"Extended Properties=Excel 8.0;";
strConn = string.Format(strConn, filePath);
System.Data.OleDb.OleDbConnection conn = new System.Data.OleDb.OleDbConnection(strConn);
conn.Open();
using (DataTable dt = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null))
{
////取得工作表數量，法一
//foreach (DataRow dr in dt.Rows)
//{
// Console.WriteLine((String)dr["TABLE_NAME"]);
//}
//取得工作表數量，法二
int TableCount = dt.Rows.Count;
for (int i = 0; i < TableCount; i++)
{
ret = dt.Rows[i][2].ToString().Trim().TrimEnd('$');
conn.Close();
return(ret);
}
}
conn.Close();
return(ret);
}
public Object[,] SorgulaDizi(String cmdSorgu)
{
Object[,] d = null;
try{
DataSet dS = SorgulaDataSet(cmdSorgu);
DataTable dT = dS.Tables[0];
d = new Object[dT.Rows.Count, dT.Columns.Count];
for (int i = 0; i < dT.Rows.Count; i++)
{
for (int j = 0; j < dT.Columns.Count; j++)
{
d[i, j] = dT.Rows[i][j];
}
}
}
catch (OleDbException ole)
{
MessageBox.Show(ole.Message);
}
catch (Exception e)
{
MessageBox.Show(e.Message);
}
finally { bglnti.Close(); }
return(d);
}
protected void Page_Load(object sender, EventArgs e)
{
if (Session["oturumid"] == null)
Response.Redirect("giris.aspx");
else if (Session["oturumdurum"].ToString() == "0")
Response.Redirect("uyepaneli.aspx");
else if (Request.QueryString["mid"] == null|| Request.QueryString["mid"] == "")
Response.Redirect("adminpaneli.aspx");
else
{
string mid = Request.QueryString["mid"];
OleDbConnection cnn = new OleDbConnection("Provider=Microsoft.Jet.Oledb.4.0; Data Source=" + Server.MapPath("App_Data/database.mdb"));
cnn.Open();
OleDbCommand cmd = new OleDbCommand("select * from mesajlar where mesajid=" + mid, cnn);
OleDbDataReader rdr = cmd.ExecuteReader();
if(rdr.Read()==false)
{
cnn.Close();
Response.Redirect("adminpaneli.aspx");
}
else
{
Label2.Text = rdr[1].ToString();
Label4.Text = rdr[2].ToString();
Label6.Text = rdr[3].ToString();
Label7.Text = rdr[4].ToString();
}
if(cnn.State==System.Data.ConnectionState.Open)
cnn.Close();
}
}
public DataSet Excel2003ToDataSet(string Path)
{
OleDbConnection conn = null;
try
{
string strConn = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + Path + ";" + "Extended Properties=Excel 8.0;";
conn = new OleDbConnection(strConn);
conn.Open();
System.Data.DataTable dtSheet = conn.GetOleDbSchemaTable(System.Data.OleDb.OleDbSchemaGuid.Tables, null);
string tableName = dtSheet.Rows[0][2].ToString();
string strExcel = "";
OleDbDataAdapter myCommand = null;
DataSet ds = null;
strExcel = "select * from [" + tableName + "]";
myCommand = new OleDbDataAdapter(strExcel, strConn);
ds = new DataSet();
myCommand.Fill(ds, tableName);
conn.Close();
conn = null;
return ds;
}
catch (Exception ex)
{
if (conn != null && conn.State == ConnectionState.Open)
conn.Close();
conn = null;
throw;
}
}
public string monitor(string dev)
{
string val = "no";
ExcelCon = new System.Data.OleDb.OleDbConnection("provider=Microsoft.Jet.OLEDB.4.0;Data Source='" + filename + "';Extended Properties=Excel 8.0;");
ExcelCon.Close();
ExcelCon.Open();
try
{
System.Data.OleDb.OleDbCommand cmd = new System.Data.OleDb.OleDbCommand();
cmd.Connection = ExcelCon;
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");
// string SQL = " select [" + dev + "] FROM [" + Title + "] WHERE [RecDateTime] =(SELECT min([RecDateTime]) FROM [" + Title + "] WHERE [RecDateTime] BETWEEN @p1 AND @p2)";
string SQL = " select [" + dev + "] FROM [" + Title + "] WHERE [RecDateTime] =(SELECT min([RecDateTime]) FROM [" + Title + "] WHERE [RecDateTime] BETWEEN @p1 AND @p2)";
cmd.CommandText = SQL;
cmd.Parameters.Add("@p1", OleDbType.Date).Value = date1;
cmd.Parameters.Add("@p2", OleDbType.Date).Value = date2;
OleDbDataReader DR = cmd.ExecuteReader();
DR.Read();
val = DR.GetValue(0).ToString();
// val = cmd.ExecuteScalar().ToString();
}
catch { }
ExcelCon.Close();
return(val);
}
public void setMessageDeletedByStudent(string message_id)
{
//--Data Base Access Variables--
System.Data.OleDb.OleDbConnection dbConnection = new System.Data.OleDb.OleDbConnection(connectionString);
System.Data.OleDb.OleDbCommand dbCommand = new OleDbCommand();
dbCommand.Connection = dbConnection;
System.Data.OleDb.OleDbTransaction dbTransaction;
//-----------------------------
dbConnection.Close();
dbConnection.Open();
dbTransaction = dbConnection.BeginTransaction();
dbCommand.Transaction = dbTransaction;
try
{
dbCommand.CommandText = "UPDATE Messages SET deletedByStudent = true WHERE id = " + message_id;
dbCommand.ExecuteNonQuery();
dbTransaction.Commit();
//End Transaction
}
catch
{
dbTransaction.Rollback();
dbCommand.Transaction = null;
dbTransaction = null;
dbConnection.Close();
}
dbCommand.Transaction = null;
dbTransaction = null;
dbConnection.Close();
}
/// <summary>
/// User details
/// </summary>
/// <param name="user_id"></param>
/// <returns></returns>
public TUser getUser(string user_id)
{
//--Data Base Access Variables--
System.Data.OleDb.OleDbConnection dbConnection = new System.Data.OleDb.OleDbConnection(connectionString);
System.Data.OleDb.OleDbCommand dbCommand = new OleDbCommand();
dbCommand.Connection = dbConnection;
System.Data.OleDb.OleDbDataReader dbDataReader;
//------------------------------
dbCommand.CommandText = "SELECT * FROM Users WHERE id = '" + user_id + "'";
dbConnection.Close();
dbConnection.Open();
try
{
dbDataReader = dbCommand.ExecuteReader();
TUser tUser;
dbDataReader.Read();
tUser = new TUser(dbDataReader["id"].ToString(), dbDataReader["user_name"].ToString(), dbDataReader["email"].ToString(), Convert.ToBoolean(dbDataReader["is_teacher"]));
dbDataReader.Close();
dbConnection.Close();
return(tUser);
}
catch
{
dbConnection.Close();
return(null);
}
}
public void exitTopic(TUser tUser)
{
//--Data Base Access Variables--
System.Data.OleDb.OleDbConnection dbConnection = new System.Data.OleDb.OleDbConnection(connectionString);
System.Data.OleDb.OleDbCommand dbCommand = new OleDbCommand();
dbCommand.Connection = dbConnection;
System.Data.OleDb.OleDbTransaction dbTransaction;
//-----------------------------
dbConnection.Close();
dbConnection.Open();
dbTransaction = dbConnection.BeginTransaction();
dbCommand.Transaction = dbTransaction;
try
{
//Begin Transaction
dbCommand.CommandText = "UPDATE User_Topic SET finishDateTime = '" + DateTime.Now.ToString() + "' WHERE topic_id = " + tUser.topic.id.ToString();
dbCommand.ExecuteNonQuery();
tUser.topic = null;
dbTransaction.Commit();
//End Transaction
}
catch
{
dbTransaction.Rollback();
dbCommand.Transaction = null;
dbTransaction = null;
dbConnection.Close();
}
dbCommand.Transaction = null;
dbTransaction = null;
dbConnection.Close();
}
private void button2_Click(object sender, EventArgs e)
{
string chaine = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:/Users/dhib/Desktop/Professeur.accdb";
OleDbConnection con = new OleDbConnection(chaine);
OleDbCommand cmd = con.CreateCommand();
cmd.CommandText = "select * from module1";
try
{
con.Open();
OleDbDataReader dr = cmd.ExecuteReader();
dataGridView1.Rows.Clear();
while (dr.Read())
{
dataGridView1.Rows.Add(dr.GetValue(1), dr.GetValue(2), dr.GetValue(3), dr.GetValue(4),dr.GetValue(0));
}
dr.Close();
con.Close();
}
catch (Exception ex) { MessageBox.Show("Erreur : " + ex.Message); con.Close(); }
// dataGridView1.CellClick += new DataGridViewCellEventHandler(dataGridView1_CellClick);
}
private void button2_Click(object sender, EventArgs e)
{
string chaine = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:/Users/dhib/Desktop/Professeur.accdb";
OleDbConnection con = new OleDbConnection(chaine);
OleDbCommand cmd = con.CreateCommand();
cmd.CommandText = "update enseignant set mdp='" + txt_g_password.Text + "',nom=' "+txt_g_family.Text+"',prenom='"+txt_g_name.Text+"', grade='"+txt_g_grade.Text+"' where login='******'";
try
{
con.Open();
cmd.ExecuteNonQuery();
MessageBox.Show("Modification Réussi");
txt_g_password.Text = "";
txt_g_family.Text = "";
txt_g_name.Text = "";
txt_g_grade.Text = "";
con.Close();
new acceuil(login).Show();
this.Hide();
}
catch (Exception ex)
{
MessageBox.Show("Erreur" + ex.Message);
con.Close();
}
}
protected void onClick_login(object sender, EventArgs e)
{
connection = new OleDbConnection(path);
connection.Open();
cmd = new OleDbCommand("SELECT COUNT(*) FROM tblProfiles WHERE user_name = '" + inputUsername.Text + "';", connection);
cmd.ExecuteNonQuery();
int compareUsername = Convert.ToInt32(cmd.ExecuteScalar().ToString());
connection.Close();
if (compareUsername == 1)
{
connection.Open();
cmd = new OleDbCommand("SELECT user_password FROM tblProfiles WHERE user_name = '" + inputUsername.Text + "';", connection);
cmd.ExecuteNonQuery();
string comparePassword = cmd.ExecuteScalar().ToString();
connection.Close();
if (comparePassword == inputPassword.Text)
{
Session["username"] = inputUsername.Text;
Server.Transfer("main.aspx");
}
else
errorPassword.Visible = true;
}
else
errorUsername.Visible = true;
}
public List <TUser> getUsersInTopic(int topic_id, bool teacher)
{
//--Data Base Access Variables--
System.Data.OleDb.OleDbConnection dbConnection = new System.Data.OleDb.OleDbConnection(connectionString);
System.Data.OleDb.OleDbCommand dbCommand = new OleDbCommand();
dbCommand.Connection = dbConnection;
System.Data.OleDb.OleDbDataReader dbDataReader;
//------------------------------
dbCommand.CommandText = "SELECT * FROM Users INNER JOIN User_Topic ON Users.id = User_Topic.user_id WHERE (User_Topic.topic_id = " + topic_id.ToString() + ") AND (Users.is_teacher = " + teacher + ") AND (User_Topic.finishDateTime IS NULL)";
dbConnection.Close();
dbConnection.Open();
dbDataReader = dbCommand.ExecuteReader();
TUser tUserAux;
List <TUser> usersInTopic = new List <TUser>();
while (dbDataReader.Read())
{
tUserAux = new TUser(dbDataReader["id"].ToString(), dbDataReader["user_name"].ToString(), dbDataReader["email"].ToString(), Convert.ToBoolean(dbDataReader["is_teacher"]));
usersInTopic.Add(tUserAux);
}
dbDataReader.Close();
dbConnection.Close();
return(usersInTopic);
}
private void MigrateExcelFile(string fileName,string excel_column_range,string tempTable)
{
string virtualColumns = "null as user_id";
OleDbCommand command = default(OleDbCommand);
OleDbDataReader rdr = default(OleDbDataReader);
OleDbConnection conn = default(OleDbConnection);
try
{
conn = new OleDbConnection(string.Format(excelConnectionString, fileName));
conn.Open();
if (virtualColumns != "") virtualColumns += ",";
command = conn.CreateCommand();
command.CommandText = string.Format("Select {0} * From [{1}]",virtualColumns, excel_column_range);
command.CommandType = CommandType.Text;
rdr = command.ExecuteReader();
if (rdr.HasRows)
{
SqlBulkCopy sqlBulk = new SqlBulkCopy(dbConnection.ConnectionString);
sqlBulk.DestinationTableName = tempTable;
sqlBulk.WriteToServer(rdr);
rdr.Close();
}
conn.Close();
}
catch (Exception ex)
{
if (conn.State == ConnectionState.Open) conn.Close();
throw ex;
};
}
public void activeTopic(int topic_id)
{
//--Data Base Access Variables--
System.Data.OleDb.OleDbConnection dbConnection = new System.Data.OleDb.OleDbConnection(connectionString);
System.Data.OleDb.OleDbCommand dbCommand = new OleDbCommand();
dbCommand.Connection = dbConnection;
System.Data.OleDb.OleDbTransaction dbTransaction;
//-----------------------------
dbConnection.Close();
dbConnection.Open();
dbTransaction = dbConnection.BeginTransaction();
dbCommand.Transaction = dbTransaction;
try
{
//---Begin Transaction---
//UPDATE Topic
dbCommand.CommandText = "UPDATE Topics SET finishDateTime = NULL WHERE id = " + topic_id;
dbCommand.ExecuteNonQuery();;
dbTransaction.Commit();
//----End Transaction----
}
catch
{
dbTransaction.Rollback();
dbCommand.Transaction = null;
dbTransaction = null;
dbConnection.Close();
}
dbCommand.Transaction = null;
dbTransaction = null;
dbConnection.Close();
}
public DataSet Select(string selstr)
{
DataSet ds = new DataSet();
OleDbConnection conn = new OleDbConnection();
conn.ConnectionString = @"Provider=Microsoft.Jet.OLEDB.4.0;" +
"Data Source =" + DBPath;
try
{
conn.Open();
OleDbDataAdapter da = new OleDbDataAdapter(selstr, conn);
da.Fill(ds);
}
catch (Exception)
{
conn.Close();
}
finally
{
conn.Close();
}
return ds;
}
private void button2_Click(object sender, EventArgs e)
{
date.CustomFormat = "dd/MM/yyyy";
string chaine = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:/Users/dhib/Desktop/Professeur.accdb";
OleDbConnection con = new OleDbConnection(chaine);
OleDbCommand cmd = con.CreateCommand();
cmd.CommandText = "Insert into module1(nom_module,coef,anne,semestre,login_enseignant) values ('" + txt_nv_module.Text + "','" + Convert.ToInt32(txt_coef.Text) + "','" + date.Text + "'," + Convert.ToInt32(txt_semestre.Text) + ",'" + login + "')";
try
{
con.Open();
cmd.ExecuteNonQuery();
MessageBox.Show("Ajout Module Réussi");
txt_nv_module.Text = "";
txt_coef.Text = "";
txt_semestre.Text = "";
con.Close();
new acceuil(login).Show();
this.Hide();
}
catch (Exception ex)
{
MessageBox.Show("Erreur" + ex.Message);
con.Close();
}
}
private void button2_Click(object sender, EventArgs e)
{
string chaine = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:/Users/dhib/Desktop/Professeur.accdb";
OleDbConnection con = new OleDbConnection(chaine);
OleDbCommand cmd = con.CreateCommand();
cmd.CommandText = "Insert into enseignant(login,mdp,nom,prenom,grade) values ('" + txt_insc_login.Text + "','" + txt_insc_password.Text + "','" + txt_insc_family.Text + "','" + txt_insc_name.Text + "','" + txt_insc_grade.Text + "')";
try
{
con.Open();
cmd.ExecuteNonQuery();
MessageBox.Show("Ajout Réussi");
txt_insc_login.Text = "";
txt_insc_password.Text = "";
txt_insc_family.Text = "";
txt_insc_name.Text = "";
txt_insc_grade.Text = "";
con.Close();
new Form1().Show();
this.Hide();
}catch(Exception ex)
{ MessageBox.Show("Erreur" + ex.Message);
con.Close();
}
}
/// <summary>
/// 执行查询
/// </summary>
/// <param name="ServerFileName">xls文件路径</param>
/// <param name="SelectSQL">查询SQL语句</param>
/// <returns>DataSet</returns>
public static System.Data.DataSet SelectFromXLS(string ServerFileName, string SelectSQL)
{
string connStr = "Provider = Microsoft.Jet.OLEDB.4.0 ; Data Source = '" + ServerFileName + "';Extended Properties=Excel 8.0";
OleDbConnection conn = new OleDbConnection(connStr);
OleDbDataAdapter da = null;
System.Data.DataSet ds = new System.Data.DataSet();
try
{
conn.Open();
da = new OleDbDataAdapter(SelectSQL, conn);
da.Fill(ds, "SelectResult");
}
catch (Exception e)
{
conn.Close();
throw e;
}
finally
{
conn.Close();
}
return ds;
}
public void NapolniForma()
{
String komanda = "EXECUTE spPodatociProizvod";
OleDbConnection OleCn = new System.Data.OleDb.OleDbConnection(konekcija);
OleDbCommand OleCm = new System.Data.OleDb.OleDbCommand(komanda, OleCn);
OleCm.Parameters.Add(new OleDbParameter("@Code", this.codeTextBox.Text.Trim()));
OleDbDataReader dt;
try
{
OleCn.Open();
dt = OleCm.ExecuteReader();
if (dt.Read())
{
groupComboBox.SelectedIndex = groupComboBox.Items.IndexOf(dt["Ime_PG"].ToString());
description1TextBox.Text = dt["Description"].ToString();
Sifra_Proizvod = Convert.ToInt32(dt["Sifra_Pro"].ToString());
}
dt.Close();
OleCn.Close();
this.codeTextBox.Enabled = false;
}
catch (Exception ex)
{
OleCn.Close();
MessageBox.Show(ex.Message);
}
}
public List <TUser> getUsersCourse(String course_id, bool current)
{
//--Data Base Access Variables--
System.Data.OleDb.OleDbConnection dbConnection = new System.Data.OleDb.OleDbConnection(connectionString);
System.Data.OleDb.OleDbCommand dbCommand = new OleDbCommand();
dbCommand.Connection = dbConnection;
System.Data.OleDb.OleDbDataReader dbDataReader;
//------------------------------
dbCommand.CommandText = "SELECT * FROM Users INNER JOIN User_Course_Group ON Users.id = User_Course_Group.user_id WHERE (User_Course_Group.course_id = '" + course_id + "') AND (User_Course_Group.is_current = " + current + ")";
dbConnection.Close();
dbConnection.Open();
dbDataReader = dbCommand.ExecuteReader();
TUser tUserAux;
List <TUser> usersCourse = new List <TUser>();
while (dbDataReader.Read())
{
tUserAux = new TUser(dbDataReader["id"].ToString(), dbDataReader["user_name"].ToString(), dbDataReader["email"].ToString(), Convert.ToBoolean(dbDataReader["is_teacher"]));
usersCourse.Add(tUserAux);
}
dbDataReader.Close();
dbConnection.Close();
return(usersCourse);
}
public List <TCourse> getCourses(string user_id, bool current)
{
//--Data Base Access Variables--
System.Data.OleDb.OleDbConnection dbConnection = new System.Data.OleDb.OleDbConnection(connectionString);
System.Data.OleDb.OleDbCommand dbCommand = new OleDbCommand();
dbCommand.Connection = dbConnection;
System.Data.OleDb.OleDbDataReader dbDataReader;
//------------------------------
dbCommand.CommandText = "SELECT id, course_name, group_id, user_id, is_current, responsable_teacher FROM Courses INNER JOIN User_Course_Group ON (Courses.id = User_Course_Group.course_id) WHERE (user_id = '" + user_id + "') AND (is_current = " + current + ")";
dbConnection.Close();
dbConnection.Open();
dbDataReader = dbCommand.ExecuteReader();
List <TCourse> tCourseList = new List <TCourse>();
TCourse tCourseAux;
while (dbDataReader.Read())
{
TUser responsableTeacher = null;
if (dbDataReader["responsable_teacher"] != null)
{
responsableTeacher = getUser(dbDataReader["responsable_teacher"].ToString());
}
else
{
responsableTeacher = null;
}
tCourseAux = new TCourse(dbDataReader["id"].ToString(), dbDataReader["course_name"].ToString(), dbDataReader["group_id"].ToString(), responsableTeacher);
tCourseList.Add(tCourseAux);
}
dbDataReader.Close();
dbConnection.Close();
return(tCourseList);
}
/// <summary>
/// 取出sheet
/// </summary>
/// <param name="filePath"></param>
/// <returns></returns>
public string getFirstSheetName(string filePath)
{
string ret = "";
string strConn = "Driver={Microsoft Text Driver (*.txt; *.csv)}; " +
"Dbq= " + filePath + ";Extensions=csv,txt ";
strConn = string.Format(strConn, filePath);
System.Data.OleDb.OleDbConnection conn = new System.Data.OleDb.OleDbConnection(strConn);
conn.Open();
using (DataTable dt = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null))
{
////取得工作表數量，法一
//foreach (DataRow dr in dt.Rows)
//{
// Console.WriteLine((String)dr["TABLE_NAME"]);
//}
//取得工作表數量，法二
int TableCount = dt.Rows.Count;
for (int i = 0; i < TableCount; i++)
{
ret = dt.Rows[i][2].ToString().Trim().TrimEnd('$');
conn.Close();
return(ret);
}
}
conn.Close();
return(ret);
}
public String getUserPassword(string user_id)
{
//--Data Base Access Variables--
System.Data.OleDb.OleDbConnection dbConnection = new System.Data.OleDb.OleDbConnection(connectionString);
System.Data.OleDb.OleDbCommand dbCommand = new OleDbCommand();
dbCommand.Connection = dbConnection;
System.Data.OleDb.OleDbDataReader dbDataReader;
//------------------------------
dbCommand.CommandText = "SELECT user_password FROM Users WHERE id = '" + user_id + "'";
dbConnection.Close();
dbConnection.Open();
try
{
dbDataReader = dbCommand.ExecuteReader();
dbDataReader.Read();
string userPassword = dbDataReader["user_password"].ToString();
dbDataReader.Close();
dbConnection.Close();
return(userPassword);
}
catch
{
dbConnection.Close();
return(null);
}
}
public TCourse getCourse(string course_id)
{
//--Data Base Access Variables--
System.Data.OleDb.OleDbConnection dbConnection = new System.Data.OleDb.OleDbConnection(connectionString);
System.Data.OleDb.OleDbCommand dbCommand = new OleDbCommand();
dbCommand.Connection = dbConnection;
System.Data.OleDb.OleDbDataReader dbDataReader;
//------------------------------
dbCommand.CommandText = "SELECT * FROM Courses INNER JOIN User_Course_Group ON Courses.id = User_Course_Group.course_id WHERE (id = '" + course_id + "')";
dbConnection.Close();
dbConnection.Open();
dbDataReader = dbCommand.ExecuteReader();
TCourse tCourseAux;
dbDataReader.Read();
TUser responsableTeacher = null;
if (dbDataReader["responsable_teacher"] != null)
{
responsableTeacher = getUser(dbDataReader["responsable_teacher"].ToString());
}
else
{
responsableTeacher = null;
}
tCourseAux = new TCourse(dbDataReader["id"].ToString(), dbDataReader["course_name"].ToString(), dbDataReader["group_id"].ToString(), responsableTeacher);
dbDataReader.Close();
dbConnection.Close();
return(tCourseAux);
}
protected void FormView1_ItemCommand(object sender, FormViewCommandEventArgs e)
{
if (e.CommandName == "Add to Cart")
{
FormViewRow row = this.FormView1.Row;
string strPartid = Request.QueryString["prod"];
TextBox tboxQty = (TextBox)row.FindControl("txtQty");
Int32 intQty = Convert.ToInt32(tboxQty.Text);
string sqlString;
OleDbConnection myConn = new OleDbConnection();
myConn.ConnectionString = this.SqlDataSource1.ConnectionString;
if (myConn.State == ConnectionState.Closed) myConn.Open();
sqlString = "insert into Cart_LineItems values (" + Session["cartid"] + ", '" + strPartid + "', '" + intQty.ToString() + "');";
OleDbCommand myCommand = new OleDbCommand(sqlString, myConn);
try
{
myCommand.ExecuteNonQuery();
myConn.Close();
this.lblAdMessage.Text = "Added to Cart.";
}
catch (Exception ex)
{
this.lblErMessage.Text = "Cannot add record" + ex.Message;
}
myConn.Close();
}
}
public void check_privillage()
{
string Select_SQL, Select_SQL2, Select_SQL3, Update_SQL1;
OleDbConnection SqlConnection1 = new OleDbConnection();
SqlConnection1.ConnectionString = AccessDataSource1.ConnectionString;
Select_SQL = "select module_name,page_name,user_name from module_user_mng,user_mng,module_mng where module_mng.module_id = module_user_mng.module_id and user_mng.user_id = module_user_mng.user_id and page_name='" + System.IO.Path.GetFileName(Request.PhysicalPath) + "' and user_name='" + Session["xsctintranet"] + "'";
SqlConnection1.Open();
OleDbCommand SqlCommand = SqlConnection1.CreateCommand();
SqlCommand.CommandText = Select_SQL;
OleDbDataReader SqlDataReader = SqlCommand.ExecuteReader();
if (SqlDataReader.HasRows)
{
while (SqlDataReader.Read())
{
OleDbCommand SqlCommand2 = SqlConnection1.CreateCommand();
Update_SQL1 = "insert into visit_mng (visit_module_name,visit_user_name,visit_date,visit_ip,visit_module_page) VALUES ('" + SqlDataReader.GetString(0) + "','" + SqlDataReader.GetString(2) + "','" + DateTime.Now.ToString() + "','" + Page.Request.UserHostAddress + "','" + SqlDataReader.GetString(1) + "')";
SqlCommand2.CommandText = Update_SQL1;
SqlCommand2.ExecuteNonQuery();
}
}
else
{
OleDbCommand SqlCommand2 = SqlConnection1.CreateCommand();
Select_SQL2 = "select * from module_mng where page_name='" + System.IO.Path.GetFileName(Request.PhysicalPath) + "'";
SqlCommand2.CommandText = Select_SQL2;
OleDbDataReader SqlDataReader2 = SqlCommand2.ExecuteReader();
if (SqlDataReader2.HasRows)
{
Response.Write("您没有访问该页面的权限！");
SqlDataReader2.Close();
SqlDataReader.Close();
SqlConnection1.Close();
Response.End();
}
else
{
OleDbCommand SqlCommand3 = SqlConnection1.CreateCommand();
Select_SQL3 = "select * from user_mng where user_name='" + Session["xsctintranet"] + "'";
SqlCommand3.CommandText = Select_SQL3;
OleDbDataReader SqlDataReader3 = SqlCommand3.ExecuteReader();
if (SqlDataReader3.HasRows)
{ }
else
{
Response.Write("您没有访问该页面的权限！");
SqlDataReader3.Close();
SqlDataReader2.Close();
SqlDataReader.Close();
SqlConnection1.Close();
Response.End();
}
SqlDataReader3.Close();
}
SqlDataReader2.Close();
}
SqlDataReader.Close();
SqlConnection1.Close();
}
private static int ExcelMaxRow = 999999999; //Convert.ToInt32(ConfigurationSettings.AppSettings["ExcelMaxRow"]);
#endregion Fields
#region Methods
/// <summary>
/// ����Excel ��ȡExcel���� ������DataSet���ݼ���
/// </summary>
/// <param name="filepath">Excel������·��</param>
/// <param name="tableName">Excel������</param>
/// <returns></returns>
public static System.Data.DataSet ExcelSqlConnection(string filepath, string tableName,string sheetName)
{
string errMsg = string.Empty;
//string strCon = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + filepath + ";Extended Properties='Excel 8.0;HDR=YES;IMEX=1'";
////OleDbConnection ExcelConn = new OleDbConnection(strCon);
//try
//{
// string strCom = string.Format("SELECT * FROM [Sheet1$]");
// DataSet ds = new DataSet();
// ds = DBdb2.RunDataSet(strCom, out errMsg);
// return ds;
//}
//catch
//{
// return null;
//}
string strCon = "Provider=Microsoft.Ace.OleDb.12.0;" + "data source=" + filepath + ";Extended Properties='Excel 12.0; HDR=Yes; IMEX=1'";
OleDbConnection ExcelConn = new OleDbConnection(strCon);
try
{
string strCom = string.Format("SELECT * FROM ["+sheetName+"$]");
ExcelConn.Open();
OleDbDataAdapter myCommand = new OleDbDataAdapter(strCom, ExcelConn);
DataSet ds = new DataSet();
myCommand.Fill(ds, "[" + tableName + "$]");
ExcelConn.Close();
return ds;
}
catch
{
ExcelConn.Close();
return null;
}
}
protected void btnInstall_Click(object sender, EventArgs e)
{
string ConnectionString = string.Format("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=|DataDirectory|\\{0}.mdb;",
txtDatabaseName.Text);
OleDbConnection OConn = new OleDbConnection(ConnectionString);
StreamReader Sr = new StreamReader(Server.MapPath("~/Setup/Scripts/Access.sql"));
try
{
File.Copy(Server.MapPath("~/Setup/Scripts/Blogsa.mdb"), Server.MapPath(string.Format("~/App_Data/{0}.mdb", txtDatabaseName.Text)));
//Update WebSite Url
string strUrl = Request.Url.AbsoluteUri.Substring(0
, Request.Url.AbsoluteUri.IndexOf(Request.Url.AbsolutePath) + (Request.ApplicationPath.Equals("/") ? 0 : Request.ApplicationPath.Length)) + "/";
OConn.Open();
while (!Sr.EndOfStream)
{
//Create DB
string Commands = Sr.ReadLine().ToString();
if (!Commands.StartsWith("/*"))
{
OleDbCommand OComm = new OleDbCommand(Commands, OConn);
OComm.ExecuteNonQuery();
OComm.Dispose();
}
}
Sr.Close();
string strLang = (string)Session["lang"];
string strRedirectPage = String.Format("Completed.aspx?Setup={0}&lang={1}", BSHelper.SaveWebConfig(ConnectionString, "System.Data.OleDb"), strLang);
Response.Redirect(strRedirectPage, false);
}
catch (Exception ex)
{
BSLog l = new BSLog();
l.CreateDate = DateTime.Now;
l.LogType = BSLogType.Error;
l.LogID = Guid.NewGuid();
l.RawUrl = Request.RawUrl;
l.Source = ex.Source;
l.StackTrace = ex.StackTrace;
l.TargetSite = ex.TargetSite;
l.Url = Request.Url.ToString();
l.Save();
divError.Visible = true;
lblError.Text = ex.Message;
if (OConn.State == ConnectionState.Open)
{
OConn.Close();
}
File.Delete(Server.MapPath("~/App_Data/" + txtDatabaseName.Text));
}
finally
{
if (OConn.State == ConnectionState.Open)
OConn.Close();
Sr.Close();
}
}
private void FrmMain_Load(object sender, EventArgs e)
{
this.SetStyle(ControlStyles.OptimizedDoubleBuffer | ControlStyles.AllPaintingInWmPaint | ControlStyles.UserPaint, true);
string strPath = System.IO.Directory.GetCurrentDirectory() + BGimage;
this.BackgroundImage = Image.FromFile(strPath);
BtnStart.Image = Image.FromFile(System.IO.Directory.GetCurrentDirectory() + "\\images\\btn_start.png");
//从数据库里读出所有可抽奖人员
OleDbConnection odcConnection = new OleDbConnection(strConn);
odcConnection.Open();
OleDbCommand odCommand = odcConnection.CreateCommand();
odCommand.CommandText = "select count(*) as result from seedlist";
OleDbDataReader odrCount = odCommand.ExecuteReader();
odrCount.Read();
int allcount = Int32.Parse(odrCount[0].ToString());
odrCount.Close();
if (allcount < 9)
{
odcConnection.Close();
MessageBox.Show("可供抽奖的人数不足,无法抽奖");
this.Close();
}
else
{
//读取所有记录到数据
SeedsList.Items.Clear();
odCommand.CommandText = "select id,employee_dept,employee_name,employee_no from Seedlist where award_flag = '0' order by id asc";
OleDbDataReader odrReader = odCommand.ExecuteReader();
while (odrReader.Read())
{
SeedsList.Items.Add(odrReader[0].ToString() + ";" + odrReader[1].ToString() + ";" + odrReader[2].ToString() + ";" + odrReader[3].ToString());
}
odrReader.Close();
odcConnection.Close();
}
}
public DataSet ExecuteReader(string q)
{
q = CleanQuery(q);
Logger.CreateLog("ExecuteReaderQuery", q, null, EnumLogLevel.INFO);
DataSet dataset = null;
OleDbConnection oConn = new OleDbConnection(GetConnexionString());
oConn.Open();
try
{
OleDbCommand oCmd = new OleDbCommand(q, oConn);
OleDbDataReader reader = oCmd.ExecuteReader();
dataset = this.ConvertDataReaderToDataSet(reader);
}
catch (Exception ex)
{
oConn.Close();
ex.HelpLink = q;
throw ex;
}
oConn.Close();
return dataset;
}
internal static DataTable exceldata(string filePath)
{
DataTable dtexcel = new DataTable();
bool hasHeaders = false;
string HDR = hasHeaders ? "Yes" : "No";
string strConn;
if (filePath.Substring(filePath.LastIndexOf('.')).ToLower() == ".xlsx")
{
strConn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + filePath + ";Extended Properties=\"Excel 12.0;HDR=" + HDR + ";IMEX=0\"";
}
else
{
strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + filePath + ";Extended Properties=\"Excel 8.0;HDR=" + HDR + ";IMEX=0\"";
}
System.Data.OleDb.OleDbConnection MyConnection;
System.Data.DataSet DtSet;
System.Data.OleDb.OleDbDataAdapter MyCommand;
MyConnection = new System.Data.OleDb.OleDbConnection(strConn);
MyCommand = new System.Data.OleDb.OleDbDataAdapter("select * from [Sheet1$]", MyConnection);
MyCommand.TableMappings.Add("Table", "TestTable");
DtSet = new System.Data.DataSet();
MyCommand.Fill(DtSet);
dtexcel = DtSet.Tables[0];
MyConnection.Close();
MyConnection.Close();
return(dtexcel);
}
private void button1_Click(object sender, EventArgs e)
{
dataGridView1.Rows.Clear();
string chaine = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:/Users/dhib/Desktop/Professeur.accdb";
OleDbConnection con = new OleDbConnection(chaine);
OleDbCommand cmd = con.CreateCommand();
OleDbCommand cmd1 = con.CreateCommand();
cmd.CommandText = "select cin from assiste where id_module=" + label1.Text + " group by cin";
try
{
con.Open();
OleDbDataReader dr = cmd.ExecuteReader();
while (dr.Read())
{
//dataGridView1.Rows.Add(dr.GetValue(0), dr.GetValue(2), dr.GetValue(3), dr.GetValue(4), dr.GetValue(0));
cmd1.CommandText="select nom,prenom from etudiant where cin="+dr.GetValue(0);
try { OleDbDataReader dr1 = cmd1.ExecuteReader(); dr1.Read(); dataGridView1.Rows.Add(dr.GetValue(0), dr1.GetValue(0), dr1.GetValue(1),0); }
catch (Exception ex) { }
}
dr.Close();
con.Close();
}
catch (Exception ex) { MessageBox.Show("Erreur : " + ex.Message); con.Close(); }
}
protected void Button1_Click(object sender, EventArgs e)
{
Label6.Visible = false;
OleDbConnection cnn = new OleDbConnection("Provider=Microsoft.Jet.Oledb.4.0; Data Source=" + Server.MapPath("App_Data/database.mdb"));
cnn.Open();
OleDbCommand cmd = new OleDbCommand("select * from uyeler where uyeadi='" + TextBox1.Text + "'",cnn);
OleDbDataReader rdr = cmd.ExecuteReader();
if(rdr.Read()==true)
{
Label6.Visible = true;
Label6.Text = "Bu kullanıcı adı zaten alınmış.";
}
else
{
cmd.Dispose();
string sorgu = "insert into uyeler(uyeadi,parola,adsoyad,mail,durum) values('" + TextBox1.Text + "', '" + TextBox4.Text + "', '" + TextBox2.Text + "', '" + TextBox3.Text + "', '0')";
cmd = new OleDbCommand(sorgu, cnn);
cmd.ExecuteNonQuery();
cnn.Close();
Server.Transfer("yenikayit.aspx");
}
cnn.Close();
}
public List <TTopic> getStartedTopics(string user_id)
{
//--Data Base Access Variables--
System.Data.OleDb.OleDbConnection dbConnection = new System.Data.OleDb.OleDbConnection(connectionString);
System.Data.OleDb.OleDbCommand dbCommand = new OleDbCommand();
dbCommand.Connection = dbConnection;
System.Data.OleDb.OleDbDataReader dbDataReader;
//------------------------------
dbCommand.CommandText = "SELECT * FROM Topics INNER JOIN User_Course_Group ON User_Course_Group.course_id = Topics.course_id AND User_Course_Group.group_id = Topics.group_id WHERE (User_Course_Group.user_id = '" + user_id + "') AND (Topics.finishDateTime IS NULL)";
/*AND (User_Course_Group.is_current = True)*/
dbConnection.Close();
dbConnection.Open();
dbDataReader = dbCommand.ExecuteReader();
TTopic tTopicAux;
List <TTopic> startedTopics = new List <TTopic>();
while (dbDataReader.Read())
{
tTopicAux = new TTopic(getCourse(dbDataReader["Topics.course_id"].ToString()), dbDataReader["title"].ToString());
tTopicAux.id = Convert.ToInt32(dbDataReader["id"]);
tTopicAux.starterTeacher = getUser(dbDataReader["starterTeacher"].ToString());
tTopicAux.startDateTime = Convert.ToDateTime(dbDataReader["startDateTime"]);
startedTopics.Add(tTopicAux);
}
dbDataReader.Close();
dbConnection.Close();
return(startedTopics);
}
//------------------------------------------------------------------------------------------------------------------//
//---------------------------------- Write to Snapshot Database ----------------------------------------------------//
//------------------------------------------------------------------------------------------------------------------//
public short WriteToSnapshot(List <List <string> > buffer)
{
string[] tempArray = new string[buffer.Count];
short ret = 0;
char[] delim = { ',' };
tempArray = buffer[buffer.Count - 1][buffer[buffer.Count - 1].Count - 1].Split(delim);
List <string> tempSplit = new List <string>();
tempSplit = tempArray.ToList();
try
{
conn = new OleDbConnection(@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\DB\OnsrudLog.accdb;Persist Security Info=False;");
conn.Open();
System.Data.OleDb.OleDbCommand cmd = new OleDbCommand(@"INSERT into dump(drives_ok, Alarm1, Alarm2, Alarm3, Alarm4, Alarm5, Alarm6, Alarm7, Alarm8, Alarm9, Alarm10, XPos, YPos, ZPos, APos, CPos, XLoad, YLoad, ZLoad, ALoad, CLoad, ULoad, Program, Sub_Program, Block, Line, Tool, Selected_Table, Process) values(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)", conn);
cmd.Parameters.Add("drives_ok", tempSplit[0][1]);
cmd.Parameters.Add("Alarm1", tempSplit[1][1]);
cmd.Parameters.Add("Alarm2", tempSplit[2][1]);
cmd.Parameters.Add("Alarm3", tempSplit[3][1]);
cmd.Parameters.Add("Alarm4", tempSplit[4][1]);
cmd.Parameters.Add("Alarm5", tempSplit[5][1]);
cmd.Parameters.Add("Alarm6", tempSplit[6][1]);
cmd.Parameters.Add("Alarm7", tempSplit[7][1]);
cmd.Parameters.Add("Alarm8", tempSplit[8][1]);
cmd.Parameters.Add("Alarm9", tempSplit[9][1]);
cmd.Parameters.Add("Alarm10", tempSplit[10][1]);
cmd.Parameters.Add("XPos", tempSplit[11][1]);
cmd.Parameters.Add("YPos", tempSplit[12][1]);
cmd.Parameters.Add("ZPos", tempSplit[13][1]);
cmd.Parameters.Add("APos", tempSplit[14][1]);
cmd.Parameters.Add("CPos", tempSplit[15][1]);
cmd.Parameters.Add("XLoad", tempSplit[16][1]);
cmd.Parameters.Add("YLoad", tempSplit[17][1]);
cmd.Parameters.Add("ZLoad", tempSplit[18][1]);
cmd.Parameters.Add("ALoad", tempSplit[19][1]);
cmd.Parameters.Add("CLoad", tempSplit[20][1]);
cmd.Parameters.Add("ULoad", tempSplit[21][1]);
cmd.Parameters.Add("Program", tempSplit[22][1]);
cmd.Parameters.Add("Sub_Program", tempSplit[23][1]);
//cmd.Parameters.Add("Cycle_Time", tempSplit[24][1]);
cmd.Parameters.Add("Block", tempSplit[25][1]);
cmd.Parameters.Add("Line", tempSplit[26][1]);
cmd.Parameters.Add("Tool", tempSplit[27][1]);
cmd.Parameters.Add("Selected_Table", tempSplit[28][1]);
cmd.Parameters.Add("Process", tempSplit[29][1]);
cmd.ExecuteNonQuery();
conn.Close();
}
catch (Exception e)
{
conn.Close();
MessageBox.Show(e.ToString());
}
return(ret);
}
private void Availability_Check_Load(object sender, EventArgs e)
{
//check for recovery Avar
Back_End.SecondaryDatabase.notifyRecovery();
{
//pulls flight destinations from database
OleDbCommand command = new OleDbCommand();
connection.ConnectionString = @"Provider=Microsoft.Jet.OLEDB.4.0; Data Source=PrimaryDB.mdb";
command.Connection = connection;
connection.Open();
string query = "SELECT Destination FROM Flights";
command.CommandText = query;
OleDbDataReader reader = command.ExecuteReader();
while (reader.Read())
{
Locationinput.Items.Add(reader["Destination"].ToString());
}
connection.Close();
//pulls car rental service names from database
connection.Open();
string query2 = "SELECT CarRentalCompany FROM Cars";
command.CommandText = query2;
OleDbDataReader reader2 = command.ExecuteReader();
while (reader2.Read())
{
Carinput.Items.Add(reader2["CarRentalCompany"].ToString());
}
connection.Close();
//pulls hotel names from database
connection.Open();
string query3 = "SELECT HotelName FROM Hotel";
command.CommandText = query3;
OleDbDataReader reader3 = command.ExecuteReader();
while (reader3.Read())
{
Hotelinput.Items.Add(reader3["HotelName"].ToString());
}
connection.Close();
}
}
private void btBrow_Click ( object sender, EventArgs e )
{
if (FileDialog.ShowDialog ( ) == DialogResult.OK)
{
FileDialog.Filter = "Excel Sheet(*.xlsx)|*.xlsx|Tất cả(*.*)|*.*";
OleDbConnection excelConn = new OleDbConnection ( );
//OracleConnection excelConn = new OracleConnection();
try
{
string filelocation = FileDialog.FileName;
// lay ten file
string[] array = filelocation.Split ( '\\' );
string path = array[array.Length - 1].ToString ( );
path = path.Substring ( path.IndexOf ( '.' )+1 );
dtSaving.Clear ( );
dtColla.Clear ( );
OleDbCommand excelCommandSv = new OleDbCommand ( );
OleDbCommand excelCommandCl = new OleDbCommand ( );
//OracleCommand excelCommand = new OracleCommand();
OleDbDataAdapter excelDataAdapterSv = new OleDbDataAdapter ( );
OleDbDataAdapter excelDataAdapterCl = new OleDbDataAdapter ( );
//OracleDataAdapter excelDataAdapter = new OracleDataAdapter();
tbFilepath.Text = filelocation;
string excelConnStr = "";
if (path.Equals ( "xlsx" ))
excelConnStr = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + filelocation + ";Extended Properties=Excel 12.0;";
else
excelConnStr = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" + filelocation + "; Extended Properties =Excel 8.0;";
excelConn.ConnectionString = excelConnStr;
excelConn.Open ( );
//---------Load Elecra
excelCommandSv = new OleDbCommand ( "SELECT * FROM [" + GetExcelSheetNames ( excelConnStr )[0] + "]", excelConn );
excelCommandCl = new OleDbCommand ( "SELECT * FROM [" + GetExcelSheetNames ( excelConnStr )[1] + "]", excelConn );
//excelCommand = new OracleCommand("SELECT * FROM [BHD$]", excelConn);
excelDataAdapterSv.SelectCommand = excelCommandSv;
excelDataAdapterCl.SelectCommand = excelCommandCl;
//dgv_error.DataSource = null;
excelDataAdapterSv.Fill ( dtSaving );
excelDataAdapterCl.Fill ( dtColla );
dtgvSaving.DataSource = dtSaving;
dtgvColla.DataSource = dtColla;
//Xử lý Insert
MessageBox.Show ( "Load File Successful" );
excelConn.Close ( );
}
catch (Exception ex)
{
MessageBox.Show ( ex.Message );
return;
}
if (excelConn.State == ConnectionState.Open)
excelConn.Close ( );
}
}
private void buttonConnection_Click(object sender, EventArgs e)
{
/*conn.ConnectionString =
"Provider=MySqlProv;" +
"Data Source=ServerName;" +
"User id=UserName;" +
"Password=Secret;"*/
//Provider=sqloledb;Data Source=yourServername\\yourInstance;Initial Catalog=databaseName;Integrated Security=SSPI;
// using (OleDbConnection sqlConn = new OleDbConnection("Provider=MySqlProv; Data Source= 127.0.0.1\test"))
// {//new OleDbConnection("Provider=MySqlProv; Data Source= " + textBoxConnection.Text)
//Provider=MySQLProv;Data Source=127.0.0.1;User Id=root;Password=;
//Server=127.0.0.1;Uid=root;Pwd=12345;Database=test;
//MySQL Provider
//Provider=MySQL Provider;
//OleMySql.MySqlSource.1
//Server=myServerAddress;Database=myDataBase;Uid=myUsername;Pwd=myPassword;
/*string strConnect = "Provider=MySQL Provider;" +
"Server=127.0.0.1;" +
// "Initial Catalog=test;" +
"Database=test;" +
"Uid=root;" +
"Pwd=;";*/
// string strConnect = "Provider=MySQL Provider; Data Source=127.0.0.1; User ID =root; Password=; Initial Catalog=test;";
string strConnect = "Provider=MySQL Provider;Data Source=" + textBoxConnection.Text;
var sqlConn = new OleDbConnection(strConnect);
//OleDbConnection sqlConn = new OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source= " + textBoxConnection.Text);
//OleDbConnection sqlConn = new OleDbConnection("Provider=MySQL Provider;Data Source= " + textBoxConnection.Text);
try
{
sqlConn.Open();
//FormSelectionAction formActionSelection = new FormSelectionAction("Provider=Microsoft.ACE.OLEDB.12.0;Data Source= "+textBoxConnection.Text);
FormSelectionAction formActionSelection = new FormSelectionAction(strConnect);
sqlConn.Close();
this.Hide();
formActionSelection.Show();
}
catch (TypeInitializationException error)
{
//MessageBox.Show(error.Message, "Внимание!", MessageBoxButtons.OK, MessageBoxIcon.Information);
FormSelectionAction formActionSelection = new FormSelectionAction(strConnect);
//FormSelectionAction formActionSelection = new FormSelectionAction("Provider=Microsoft.ACE.OLEDB.12.0;Data Source= " + textBoxConnection.Text);
sqlConn.Close();
this.Hide();
formActionSelection.Show();
}
/* catch (Exception)
{
MessageBox.Show("Не удалось выполнить подключение, проверьте корректность введенных данных", "Проверка подключения", MessageBoxButtons.OK);
}*/
// }
}
private void btBrowse_Click ( object sender, EventArgs e )
{
DialogResult result = MessageBox.Show ( "Tắt file excel trước khi import !! Xác nhận?", "Lưu ý", MessageBoxButtons.OKCancel, MessageBoxIcon.Warning );
if (result == DialogResult.OK)
{
this.Cursor = Cursors.WaitCursor;
if (FileDialog.ShowDialog ( ) == DialogResult.OK)
{
FileDialog.Filter = "Excel Sheet(*.xlsx)|*.xlsx|Tất cả(*.*)|*.*";
OleDbConnection excelConn = new OleDbConnection ( );
try
{
this.Cursor = Cursors.WaitCursor;
string filelocation = FileDialog.FileName;
// lay ten file
string[] array = filelocation.Split ( '\\' );
string path = array[array.Length - 1].ToString ( );
path = path.Substring ( path.Length - 4 );
OleDbCommand excelCommand = new OleDbCommand ( );
OleDbDataAdapter excelDataAdapter = new OleDbDataAdapter ( );
tbFilePath.Text = filelocation;
string excelConnStr = "";
if (path.Equals ( "xlsx" ))
excelConnStr = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + filelocation + ";Extended Properties=Excel 12.0;";
else
excelConnStr = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" + filelocation + "; Extended Properties =Excel 8.0;";
excelConn.ConnectionString = excelConnStr;
excelConn.Open ( );
//---------Load Elecra
excelCommand = new OleDbCommand ( "SELECT * FROM [" + GetExcelSheetNames ( excelConnStr )[0] + "]", excelConn );
excelDataAdapter.SelectCommand = excelCommand;
//dgv_error.DataSource = null;
//Xử lý Insert
excelDataAdapter.Fill ( dtDetail );
dtgvDetail.DataSource = dtDetail;
lbTotal.Text = "Total : " + dtDetail.Rows.Count;
if (dtDetail.Rows.Count > 0)
{
Enable ( true );
excelConn.Close ( );
if (excelConn.State == ConnectionState.Open)
excelConn.Close ( );
}
this.Cursor = Cursors.Default;
}
catch (Exception ex)
{
MessageBox.Show ( ex.Message );
this.Cursor = Cursors.Default;
return;
}
}
}
}
protected void Page_Load(object sender, EventArgs e)
{
// Check whether the user is authorized
string user = HttpContext.Current.User.Identity.Name.Split("\\".ToCharArray())[1];
if (Session["user"] != null) {
user = Session["user"].ToString();
}
Session["user"] = user;
if (!PrintingServices.Validate.isStaff(user)) {
Response.Write("Not Authorized.");
Response.End();
}
List<Dictionary<string, string>> schools = new List<Dictionary<string, string>>();
OleDbConnection conn = new OleDbConnection(@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=\\miso\shares\Groups\DCP\PS Data\PS5_be.accdb");
try {
conn.Open();
// Get all catalog items
string query = "SELECT * FROM Letterhead_Schools";
OleDbCommand cmd = new OleDbCommand(query, conn);
OleDbDataReader reader = cmd.ExecuteReader();
while (reader.Read()) {
Dictionary<string, string> school = new Dictionary<string, string>();
school.Add("name", reader.GetString(reader.GetOrdinal("SchoolName")));
if (!reader.IsDBNull(reader.GetOrdinal("Address"))) {
school.Add("address", reader.GetString(reader.GetOrdinal("Address")));
} else {
school.Add("address", "");
}
if (!reader.IsDBNull(reader.GetOrdinal("Phone"))) {
school.Add("phone", reader.GetString(reader.GetOrdinal("Phone")));
} else {
school.Add("phone", "");
}
if (!reader.IsDBNull(reader.GetOrdinal("Fax"))) {
school.Add("fax", reader.GetString(reader.GetOrdinal("Fax")));
} else {
school.Add("fax", "");
}
schools.Add(school);
}
reader.Close();
// Send data
JavaScriptSerializer serializer = new JavaScriptSerializer();
string schoolsJson = serializer.Serialize(schools);
Response.Write(schoolsJson);
conn.Close();
} catch (Exception err) {
conn.Close();
Response.Write(err.Message);
}
}
public void addGame(Game obj, int num)
{
try
{
database = new OleDbConnection(connectionString);
database.Open();
string queryString = "INSERT INTO Game (Title, Description, Publisher, ReleaseDate, Rating, PurchasePrice, Copies) " +
"VALUES ('" + obj.Title + "', '" + obj.Description + "', '" + obj.Publisher + "', '" + obj.ReleaseDate + "', '" +
obj.Rating + "', '" + obj.Price + "', '" + obj.Copies + "')";
string queryString2 = "SELECT * FROM Game WHERE Title = '" + obj.Title + "'";
string queryString3;
OleDbCommand cmd = new OleDbCommand(queryString, database);
OleDbCommand cmd2 = new OleDbCommand(queryString2, database);
count = cmd.ExecuteNonQuery();
reader = cmd2.ExecuteReader();
Game temp = new Game();
reader.Read();
temp.Id = Int32.Parse(reader["ID"].ToString());
temp.Title = reader["Title"].ToString();
temp.Description = reader["Description"].ToString();
temp.Publisher = reader["Publisher"].ToString();
temp.ReleaseDate = reader["ReleaseDate"].ToString();
temp.Rating = reader["Rating"].ToString();
temp.Price = Double.Parse(reader["PurchasePrice"].ToString());
temp.Copies = Int32.Parse(reader["Copies"].ToString());
queryString3 = "INSERT INTO GameCopy (CheckedOut, GameID) " +
"VALUES (false, '" + temp.Id + "')";
OleDbCommand cmd3 = new OleDbCommand(queryString3, database);
for (int i = 0; i < num; i++)
count2 = cmd3.ExecuteNonQuery();
if (count >= 1)
MessageBox.Show(obj.Title + " has been added!");
else
MessageBox.Show("Error: Could not add game!");
reader.Close();
database.Close();
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
reader.Close();
database.Close();
}
}
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();
}
/// <summary>
/// 关闭数据库和清除DateSet对象
/// </summary>
public void Close()
{
if (ds != null) // 清除DataSet对象
{
ds.Clear();
}
if (myConnection != null)
{
myConnection.Close(); // 关闭数据库
}
}
/// <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 button4_Click(object sender, EventArgs e)
{
System.Data.OleDb.OleDbConnection con = null;
OleDbCommand command = null;
InputBoxValidation validation = delegate(string val) {
if (val == "")
{
return("Value cannot be empty.");
}
if (!val.Equals("zuojian"))
{
return("password is not correct!");
}
else
{
return("");
}
};
string value = "*****@*****.**";
if (InputBox.Show("請輸入密碼後刪除資料", "密碼:", ref value, validation) == DialogResult.OK)
{
DialogResult dialogResult = MessageBox.Show("刪除資料之前,請先備份,以及關閉\"檢測條碼視窗\"\r\n確定刪除嗎?", "刪除資料", MessageBoxButtons.YesNo);
if (dialogResult == DialogResult.Yes)
{
try
{
con = this.bAR_CODE_SCAN_HISTORYTableAdapter.Connection;
if (con.State != ConnectionState.Open)
{
con.Open();
}
command = new OleDbCommand("DELETE FROM BAR_CODE_SCAN_HISTORY", con);
command.ExecuteNonQuery();
MessageBox.Show("Delete Successful");
if (con != null && con.State == ConnectionState.Open)
{
con.Close();
}
}
catch (Exception eeee)
{
if (con != null && con.State == ConnectionState.Open)
{
con.Close();
}
}
}
else if (dialogResult == DialogResult.No)
{
}
}
}
//------------------------------------------------------------------------------------------------------------------//
//---------------------------------- Write To The Events Database --------------------------------------------------//
//------------------------------------------------------------------------------------------------------------------//
public short WriteToEvent(List <List <string> > buffer)
{
string[] bufferArray = GetChanged(buffer);
short ret = 0;
char[] delim = { ',' };
string[][] bufferSplit = new string[bufferArray.Length][];
bool changedValues = false;
for (int i = 0; i < bufferArray.Length; i++)
{
if (bufferArray[i] != null)
{
bufferSplit[i] = bufferArray[i].Split(delim);
changedValues = true;
}
}
if (changedValues == true)
{
try
{
conn = new OleDbConnection(@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\DB\OnsrudLog.accdb;Persist Security Info=False;");
conn.Open();
for (int i = 0; i < bufferSplit.Length; i++)
{
if (bufferSplit[i] != null && bufferSplit[i][1] != "0")
{
string header = bufferSplit[i][0];
string content = bufferSplit[i][1];
System.Data.OleDb.OleDbCommand cmd = new OleDbCommand(@"INSERT into events(header, content) values(?,?)", conn);
cmd.Parameters.Add("header", header);
cmd.Parameters.Add("content", content);
cmd.ExecuteNonQuery();
}
}
conn.Close();
}
catch (Exception e)
{
conn.Close();
MessageBox.Show("Could not open Database! Error: " + e.ToString());
ret = -1;
return(ret);
}
return(ret);
}
return(ret);
}
