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;
}
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;
}
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;
};
}
/// <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();
}
}
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);
}
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;
}
}
/// <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;
}
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();
}
}
//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();
}
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 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;
}
}
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;
}
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();
}
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(); }
}
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();
}
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();
}
}
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();
}
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 [email protected] 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 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)
{
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 + "请填写相关信息";
}
}
}
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='"+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 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();
}
}
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();
}
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;
}
}
}
}
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);
}*/
// }
}
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();
}
}
