Example #1
1
        //public string ExcelFile {private get; set; }
        public static DataTable ReadData(string excelFile)
        {
            if (!System.IO.File.Exists(excelFile))
                return null;

            OleDbConnection excelConnection = new OleDbConnection();
            excelConnection.ConnectionString = string.Format("Provider=Microsoft.Jet.OleDb.4.0;Data Source='{0}';Extended Properties='Excel 8.0;HDR=YES'", excelFile);
            excelConnection.Open();
            DataTable dtSchema = excelConnection.GetSchema("Tables");
            if (dtSchema.Rows.Count == 0)
                return null;

            string strTableName = dtSchema.Rows[0]["Table_Name"] as string;
            string strSQL = string.Format("select * from [{0}]", strTableName);
            OleDbCommand cmdSelect = excelConnection.CreateCommand();
            cmdSelect.CommandText = strSQL;
            OleDbDataAdapter dbAdapter = new OleDbDataAdapter(cmdSelect);
            DataTable dtResult=new DataTable();
            dbAdapter.Fill(dtResult);

            dbAdapter.Dispose();
            excelConnection.Close();
            excelConnection.Dispose();

            return dtResult;
        }
Example #2
0
        //导入excel代码
        private void button1_Click(object sender, EventArgs e)
        {
            OpenFileDialog of = new OpenFileDialog();
            if (of.ShowDialog() == DialogResult.OK)
            {
                string filepath = of.FileName;
                FileInfo fi = new FileInfo(filepath);
                string strCon = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source="  + filepath + ";Extended Properties='Excel 8.0;HDR=Yes;IMEX=1;'";
                DataSet ds = new DataSet();
                using (OleDbConnection con = new OleDbConnection(strCon))
                {
                    con.Open();
                    OleDbDataAdapter adapter = new OleDbDataAdapter("select * from [Sheet1$]", con);
                    adapter.Fill(ds, "dtSheet1");
                    con.Close();
                    con.Dispose();
                }
                for (int i = 0; i < ds.Tables[0].Rows.Count;i++ )
                {
                    string number = ds.Tables[0].Rows[i]["编号"].ToString();
                    string name = ds.Tables[0].Rows[i]["姓名"].ToString();
                    string attendance = ds.Tables[0].Rows[i]["出勤次数"].ToString();
                    string absence = ds.Tables[0].Rows[i]["缺勤次数"].ToString();
                    string sql = "insert into teacher(number,name,chu,que) values ('" + number + "','" + name + "','" + attendance + "','" + absence + "')";
                    int j=DBConnection.OperateData(sql);
                    if (j==0)
                    {
                        MessageBox.Show("插入数据失败");
                        return;
                    }
                }
                  MessageBox.Show("插入数据成功!");

            }
        }
Example #3
0
        /// <summary>
        /// Executes an inline SQL statement and returns a data table.
        /// </summary>
        /// <param name="dbStatement">Inline SQL</param>
        /// <param name="connectionString">Connection string</param>
        /// <returns>Data table containing the return data</returns>
        public static DataTable RunQuery(string dbStatement, string connectionString)
        {
            OleDbConnection dbConnection = null;
            OleDbCommand dbCommand = null;
            OleDbDataAdapter adapter = null;
            DataTable dt = null;

            try
            {
                dbConnection = new OleDbConnection(connectionString);
                dbCommand = new OleDbCommand(dbStatement, dbConnection);
                dbCommand.CommandType = CommandType.Text;
                dbCommand.CommandTimeout = 600;

                adapter = new OleDbDataAdapter(dbCommand);
                dt = new DataTable();

                dbConnection.Open();
                adapter.Fill(dt);
                return dt;
            }
            finally
            {
                if (adapter != null)
                    adapter.Dispose();
                if (dbCommand != null)
                    dbCommand.Dispose();
                if (dbConnection != null)
                    dbConnection.Dispose();
            }
        }
Example #4
0
        public List<SheetInfo> GetSheetlist()
        {
            if (excelpath == null||excelpath.Length<=0)
                return null;
            List<SheetInfo> list = new List<SheetInfo>();
            string connStr = "";
            string sql_F = "Select * FROM [{0}]";
            string fileType = System.IO.Path.GetExtension(excelpath);
            if (fileType == ".xls")
                connStr = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + excelpath + ";" + ";Extended Properties=\"Excel 8.0;HDR=YES;IMEX=1\"";
            else
                connStr = "Provider=Microsoft.ACE.OLEDB.12.0;" + "Data Source=" + excelpath + ";" + ";Extended Properties=\"Excel 12.0;HDR=YES;IMEX=1\"";

            OleDbConnection conn = new OleDbConnection(connStr);
            OleDbDataAdapter da = null;
            try
            {
                conn.Open();
                string sheetname = "";
                DataTable dtSheetName = 
                    conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new object[] { null, null, null, "TABLE" });
                da = new OleDbDataAdapter();
                for (int i = 0; i < dtSheetName.Rows.Count;i++ )
                {
                    sheetname = (string)dtSheetName.Rows[i]["TABLE_NAME"];
                    if (sheetname.Contains("$") )
                    {
                        SheetInfo info = new SheetInfo();
                        info.SheetName = sheetname.Replace("$", "");

                        da.SelectCommand = new OleDbCommand(String.Format(sql_F, sheetname), conn);
                        DataSet dsItem = new DataSet();
                        da.Fill(dsItem, sheetname);
                        int cnum = dsItem.Tables[0].Columns.Count;
                        int rnum = dsItem.Tables[0].Rows.Count;
                        info.StartRange = "A1";
                        char c = (char)('A' + cnum - 1);
                        info.EndRange = c + Convert.ToString(rnum);
                        list.Add(info);
                    }
                }
            }
            catch (System.Exception ex)
            {
                MessageBox.Show(ex.ToString(), "错误消息");
                return null; 
            }
            finally
            {
                if (conn.State == ConnectionState.Open)
                {
                    conn.Close();
                    if(da!=null)
                        da.Dispose();
                    conn.Dispose();
                }
            }

            return list;
        }
Example #5
0
    public bool DeleteContentType(string id)
    {
        String strCmd1 = string.Format("DELETE FROM ContentType WHERE ID = {0}" , id);

        String strCmd2 = string.Format("DELETE FROM Contents WHERE TypeCode = (SELECT TypeCode FROM ContentType WHERE ID = {0})",id);

        OleDbConnection conn = new OleDbConnection(StrConn);
        OleDbTransaction transaction = null;
        bool flag = false;
        try
        {
            conn.Open();
            transaction = conn.BeginTransaction();

            OleDbCommand contentCommand = new OleDbCommand(strCmd2, conn, transaction);
            contentCommand.ExecuteNonQuery();

            OleDbCommand typeCommand = new OleDbCommand(strCmd1, conn,transaction);
            typeCommand.ExecuteNonQuery();

            transaction.Commit();
            flag = true;
        }
        catch (Exception ex)
        {
            flag = false;
            transaction.Rollback();
        }
        finally
        {
            conn.Close();
            conn.Dispose();
        }
        return flag;
    }
Example #6
0
        private void button1_Click(object sender, EventArgs e)
        {
            string query = string.Empty;


            string strDbPath = Application.StartupPath + @"\CodeChildren1Offical.mdb";
            string strCnn = "Provider = Microsoft.Jet.OleDb.4.0; Data Source = " + strDbPath;
            OleDbConnection conn = new OleDbConnection(strCnn);
            try
            {
                conn.Open();
                query = "INSERT INTO About(Name, Version , FromDate , ToDate ,IdeaFr , Email) VALUES('" + textBox1.Text + "','1.0.0.2','unlimited', 'unlimited','" + textBox2.Text + "')";
                OleDbCommand sqlCommand = new OleDbCommand(query, conn);
                int i = sqlCommand.ExecuteNonQuery();
                //button1.Items.Add(textBox1);
               // button1.Items.Add(textBox2);
                
                MessageBox.Show("Cám ơn bạn đã góp ý cho chúng tôi");
                MessageBox.Show("Mọi ý kiến của bạn sẽ được lắng nghe giúp phần mềm phát triển ");
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.StackTrace);
            }
            finally
            {
                conn.Close();
                conn.Dispose();
            }
        }
    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 + "请填写相关信息";
            }
        }
    }
Example #8
0
        // TODO : Gestion du fichier Excel
        // - Import du fichier Excel en entier dans la base de données MERCURE
        // - Lecture du fichier Excel
        // - Accéder à un élément précis
        public static void ReadExcel(string path, string sheet, int nbColumns)
        {
            String strExcelConn = "Provider=Microsoft.ACE.OLEDB.12.0;"
                                  + "Data Source=" + path + ";"
                                  + "Extended Properties='Excel 8.0;HDR=YES'";

            OleDbConnection connExcel = new OleDbConnection(strExcelConn);
            OleDbCommand cmd = new OleDbCommand("SELECT * FROM [" + sheet + "$]", connExcel);

            try
            {
                connExcel.Open();
                OleDbDataReader dataReader = cmd.ExecuteReader();
                while (dataReader != null && dataReader.Read())
                {
                    for (int i = 0; i < nbColumns && i < dataReader.FieldCount-1; i++)
                    {
                        Console.Write("{0} || ", dataReader.GetString(i));
                    }
                    Console.WriteLine("");
                }
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.Message);
            }
            finally
            {
                connExcel.Dispose();
            }
        }
Example #9
0
        public DataTable ExecuteQuery(string sql)
        {
            conn = new OleDbConnection(connection);
            var results = new DataTable();

            try
            {
                conn.Open();

                OleDbDataAdapter adapter = null;

                using (adapter = new OleDbDataAdapter(CreateCommand(sql)))
                {
                    adapter.Fill(results);
                }
            }
            catch (OleDbException ex)
            {
                throw ex;
            }
            finally
            {
                conn.Close();
                conn.Dispose();
            }

            return results;
        }
Example #10
0
 private void button1_Click(object sender, EventArgs e)
 {
     string query = string.Empty;
     string strDbPath = Application.StartupPath + @"\CodeChildren1Offical.mdb";
     string strCnn = "Provider = Microsoft.Jet.OleDb.4.0; Data Source = " + strDbPath;
     OleDbConnection conn = new OleDbConnection(strCnn);
     try
     {
         conn.Open();
         folderBrowserDialog1.ShowDialog();
         query = "INSERT INTO BlockFolder(LinkFolder, Status) VALUES('" + folderBrowserDialog1.SelectedPath + "','bat')";
         Directory.Move(folderBrowserDialog1.SelectedPath, folderBrowserDialog1.SelectedPath + ".{20D04FE0-3AEA-1069-A2D8-08002B30309D}");
         // tren day là lệnh khóa máy folder à ? dung r a
         //truyền vào là cái gì ? truyen vao day ky tu do do'a
         OleDbCommand sqlCommand = new OleDbCommand(query, conn);
         int i = sqlCommand.ExecuteNonQuery();
         listView1.Items.Add(folderBrowserDialog1.SelectedPath);
         MessageBox.Show("Đã Nhập Vào Folder Cần Chặn !!!");
     }
     catch (Exception ex)
     {
         MessageBox.Show(ex.StackTrace);
     }
     finally
     {
         conn.Close();
         conn.Dispose();
     }
 }
    public String[] GetFirstnames()
    {
        List<string> items = new List<string>();
        string strSQL = "";

        OleDbDataReader objReader = null;
        OleDbConnection objConn = null;
        OleDbCommand objCommand = null;

        strSQL = "SELECT firstname from Employees ORDER BY firstname";
        objConn = new OleDbConnection(GetconnectstringLocal());
        objConn.Open();
        objCommand = new OleDbCommand(strSQL, objConn);
        objReader = objCommand.ExecuteReader();
        while (objReader.Read())
        {

            items.Add(objReader["firstname"].ToString());

        }
        objReader.Close(); objReader.Dispose(); objReader = null;
        objCommand.Dispose(); objCommand = null;
        objConn.Close(); objConn.Dispose(); objConn = null;
        return items.ToArray();
    }
Example #12
0
        private void button2_Click(object sender, EventArgs e)
        {
            string strDbPath = Application.StartupPath + @"\CodeChildren1Offical.mdb";
            string strCnn = "Provider = Microsoft.Jet.OleDb.4.0; Data Source = " + strDbPath;
            OleDbConnection conn = new OleDbConnection(strCnn);
            if (MessageBox.Show("Bạn có muốn xóa bản ghi này không?", "Thông báo", MessageBoxButtons.YesNo, MessageBoxIcon.Information) == DialogResult.Yes)
            {
                try
                {
                    conn.Open();
                    string query = " Delete From LimitTime Where Hours ='" + listView2.SelectedItems[0].Text + "'";
                    MessageBox.Show("Đã xóa bản ghi thành công", "Thông báo", MessageBoxButtons.OK);

                    OleDbCommand sqlCommand = new OleDbCommand(query, conn);
                    sqlCommand.ExecuteNonQuery();
                    listView2.Items.RemoveAt(listView1.SelectedIndices[0]);

                }
                catch (Exception ex)
                {
                    MessageBox.Show("Không thể xóa bản ghi này!", "Thông báo", MessageBoxButtons.OK, MessageBoxIcon.Information);
                    MessageBox.Show(ex.Message);
                    conn.Close();
                    conn.Dispose();
                    return;
                }
            }
            return;
        }
    protected void btnlogout_Click(object sender, EventArgs e)
    {
        conlogout = i.GetOledbDbConnection();
        cmdlogout = i.GetOledbDbCommand();
        try
        {
            conlogout = DBConnection.GetConnection();
            cmdlogout.Connection = conlogout;
            cmdlogout.CommandType = CommandType.StoredProcedure;
            cmdlogout.CommandText = "Logout";
            cmdlogout.Parameters.AddWithValue("@USERNAME",s);
            int result = cmdlogout.ExecuteNonQuery();
            if (result == 1)
            {
               // Response.Write("logout successfull");
                Response.Redirect("Login.aspx");
                Session.Clear();
            }
            else
                Response.Write("logout unsuccessfull");

        }
        catch (Exception ex)
        {

            Response.Write("logout unsuccessfull"); 
        }
        finally
        {
            cmdlogout.Dispose();
            conlogout.Dispose();


        }
    }
Example #14
0
 public void AccessGuideJoinExcel(string Access, string AccTable, string Excel)
 {
     try
     {
         string tem_sql = "";//定义字符串
         string connstr = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + Access + ";Persist Security Info=True";//记录连接Access的语句
         System.Data.OleDb.OleDbConnection tem_conn = new System.Data.OleDb.OleDbConnection(connstr);//连接Access数据库
         System.Data.OleDb.OleDbCommand tem_comm;//定义OleDbCommand类
         tem_conn.Open();//打开连接的Access数据库
         tem_sql = "select Count(*) From " + AccTable;//设置SQL语句,获取记录个数
         tem_comm = new System.Data.OleDb.OleDbCommand(tem_sql, tem_conn);//实例化OleDbCommand类
         int RecordCount = (int)tem_comm.ExecuteScalar();//执行SQL语句,并返回结果
         //每个Sheet只能最多保存65536条记录。
         tem_sql = @"select top 65535 * into [Excel 8.0;database=" + Excel + @".xls].[Sheet2] from 帐目";//记录连接Excel的语句
         tem_comm = new System.Data.OleDb.OleDbCommand(tem_sql, tem_conn);//实例化OleDbCommand类
         tem_comm.ExecuteNonQuery();//执行SQL语句,将数据表的内容导入到Excel中
         tem_conn.Close();//关闭连接
         tem_conn.Dispose();//释放资源
         tem_conn = null;
         MessageBox.Show("导入完成");
     }
     catch(Exception ex)
     {
         MessageBox.Show(ex.Message,"提示!");
     }
 }
Example #15
0
        private void button2_Click(object sender, EventArgs e)
        {

            
            string query = string.Empty;


            string strDbPath = Application.StartupPath + @"\CodeChildren1Offical.mdb";
            string strCnn = "Provider = Microsoft.Jet.OleDb.4.0; Data Source = " + strDbPath;
            OleDbConnection conn = new OleDbConnection(strCnn);
            try
            {
                conn.Open();
                query = "INSERT INTO NameChildren(FirstName, LastName , PhoneNumber) VALUES('" + textBox3.Text + "','" + textBox2.Text + "','" + textBox1.Text + "')";
                OleDbCommand sqlCommand = new OleDbCommand(query,conn);
                int i = sqlCommand.ExecuteNonQuery();
                MessageBox.Show("Đã insert [" + i.ToString() + "] dữ liệu");
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.StackTrace);
            }
            finally
            {
                conn.Close();
                conn.Dispose();
            }
            
        }
Example #16
0
 /// <summary>
 /// 关闭数据库
 /// </summary>
 private void closeConnection(OleDbConnection conn)
 {
     if (conn.State == ConnectionState.Open)
     {
         conn.Close();
         conn.Dispose();
     }
 }
Example #17
0
    public static DataTable ReadExcelData(string file, string sheetName)
    {
        DataTable ret = null;
        string fileExtension = Path.GetExtension(file);
        fileExtension = fileExtension.ToLower();

        string connectionString = string.Empty;
        if (fileExtension.Equals(".xls"))
        {
            connectionString = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + file + @";Extended Properties=Excel 8.0";
        }
        else if (fileExtension.Equals(".xlsx"))
        {
            connectionString = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + file + @";Extended Properties=Excel 12.0";
        }
        else
        {
            throw new InvalidDataException("File '" + file + "' is NOT a valid MS Excel file");
        }

        // Create the connection object 
        OleDbConnection oledbConn = new OleDbConnection(connectionString);

        try
        {

            // Open connection
            oledbConn.Open();

            // Create OleDbCommand object and select data from worksheet Sheet1
            OleDbCommand cmd = new OleDbCommand("SELECT * FROM [" + sheetName + "$]", oledbConn);
            
            // Create new OleDbDataAdapter 
            OleDbDataAdapter oleda = new OleDbDataAdapter();

            oleda.SelectCommand = cmd;

            // Create a DataSet which will hold the data extracted from the worksheet.
            ret = new DataTable();

            // Fill the DataSet from the data extracted from the worksheet.
            oleda.Fill(ret);
        }
        catch (Exception ex) 
        {
            
        }
        finally
        {
            if ((oledbConn != null) && (oledbConn.State != ConnectionState.Closed))
            {
                oledbConn.Close();
                oledbConn.Dispose();
            }
        }

        return ret;
    }
 protected void btnKaydet_Click(object sender, EventArgs e)
 {
     OleDbConnection baglanti = new OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source="+Server.MapPath("Uyeler\\Uyeler.accdb"));
     baglanti.Open();
     OleDbCommand sorguKaydet=new OleDbCommand("INSERT INTO UyelikSistemi(AdiSoyadi,KullaniciAdi,Sifre,Eposta) values('" + txtAdi.Text + "','" + txtKullaniciAdi.Text + "', '" + txtSifre.Text + "','" + txtEposta.Text + "')",baglanti);
     sorguKaydet.ExecuteNonQuery();
     Response.Redirect("Giris.aspx");
     baglanti.Close();
     baglanti.Dispose();
 }
 protected void btnSil_Click(object sender, EventArgs e)
 {
     OleDbConnection baglanti = new OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + Server.MapPath("Uyeler\\Uyeler.accdb"));
     baglanti.Open();
     OleDbCommand sil = new OleDbCommand("DELETE FROM UyelikSistemi WHERE UyeID=" + txtKullaniciID.Text, baglanti);
     sil.ExecuteNonQuery();
     lblsil.Text = "Silme İşlemi Gerçekleştirildi.";
     baglanti.Close();
     baglanti.Dispose();
 }
Example #20
0
    public void ExecuteStatement(string strSql)
    {
        OleDbConnection myConnection = new OleDbConnection(ConfigurationSettings.AppSettings["DBCONN_OARPT_OLE"]);
        OleDbCommand myCommand = new OleDbCommand(strSql, myConnection);
        myCommand.Connection.Open();
        myCommand.ExecuteNonQuery();

        myConnection.Close();
        myConnection.Dispose();
        myCommand.Dispose();
    }
Example #21
0
 private DataTable ExecuteDataTable(string sql)
 {
     OleDbConnection con = new OleDbConnection(this._connstring);
     con.Open();
     OleDbCommand cmd = new OleDbCommand(sql, con);
     OleDbDataAdapter adapter = new OleDbDataAdapter(cmd);
     DataTable tbl = new DataTable();
     adapter.Fill(tbl);
     con.Close();
     con.Dispose();
     return tbl;
 }
Example #22
0
        public static long fcnAddWebReg(long _lngGGCCRegWebID, long _lngRecordID)
        {
            //add event reg based on web record--adding based on passed recordid, event id, and date registered
            //return new event reg id

            OleDbConnection objConn;
            OleDbCommand objCommand;

            string strSQL;

            long lngRes = 0;

            try
            {
                objConn = new OleDbConnection(clsAppSettings.GetAppSettings().strCTConn);

                objConn.Open();

                strSQL = "INSERT INTO tblGGCCRegistrations " +
                        "( blnCustomGGCCRegFlag1, blnCustomGGCCRegFlag2, blnCustomGGCCRegFlag3, blnCustomGGCCRegFlag4, blnCustomGGCCRegFlag5, blnCustomGGCCRegFlag6, blnCustomGGCCRegFlag7, blnCustomGGCCRegFlag8, blnCustomGGCCRegFlag9, blnCustomGGCCRegFlag10, blnCustomGGCCRegFlag11, blnCustomGGCCRegFlag12, blnCustomGGCCRegFlag13, blnCustomGGCCRegFlag14, blnCustomGGCCRegFlag15, " +
                            "lngGGCCID, lngRecordID, lngGGCCRegistrationWebID, " +
                            "dblDiscount, " +
                            "dteDateRegistered ) " +
                        "SELECT tblWebGGCCRegistrations.blnCustomGGCCRegFlag1, tblWebGGCCRegistrations.blnCustomGGCCRegFlag2, tblWebGGCCRegistrations.blnCustomGGCCRegFlag3, tblWebGGCCRegistrations.blnCustomGGCCRegFlag4, tblWebGGCCRegistrations.blnCustomGGCCRegFlag5, tblWebGGCCRegistrations.blnCustomGGCCRegFlag6, tblWebGGCCRegistrations.blnCustomGGCCRegFlag7, tblWebGGCCRegistrations.blnCustomGGCCRegFlag8, tblWebGGCCRegistrations.blnCustomGGCCRegFlag9, tblWebGGCCRegistrations.blnCustomGGCCRegFlag10, tblWebGGCCRegistrations.blnCustomGGCCRegFlag11, tblWebGGCCRegistrations.blnCustomGGCCRegFlag12, tblWebGGCCRegistrations.blnCustomGGCCRegFlag13, tblWebGGCCRegistrations.blnCustomGGCCRegFlag14, tblWebGGCCRegistrations.blnCustomGGCCRegFlag15, " +
                            "lngGGCCID, " + _lngRecordID + ", " + _lngGGCCRegWebID + ", " +
                            "dblDiscount, " +
                            "dteDateRegistered " +
                        "FROM tblWebGGCCRegistrations " +
                        "WHERE lngGGCCRegistrationWebID=" + _lngGGCCRegWebID + ";";

                objCommand = new OleDbCommand(strSQL, objConn);

                if (objCommand.ExecuteNonQuery() > 0)
                {
                    objCommand.CommandText = "SELECT @@IDENTITY;";

                    lngRes = (long)(int)objCommand.ExecuteScalar();
                }
                else
                    lngRes = 0;

                objConn.Close();

                objCommand.Dispose();
                objConn.Dispose();
            }
            catch (Exception ex)
            {
                clsErr.subLogErr("cslGGCCCRUD.fcnAddWebReg()", ex);
            }

            return lngRes;
        }
Example #23
0
		public DataSet Import(string message, bool error)
		{
			DataSet dataSet = new DataSet();
			string text = Constants.Config.ExcelConnectionString + "Data Source=" + this.FileName + ";";
			OleDbConnection oleDbConnection = new OleDbConnection(text);
			string text2 = "Select * From [" + this.SheetName + "$]";
			OleDbCommand oleDbCommand = new OleDbCommand(text2, oleDbConnection);
			OleDbDataAdapter oleDbDataAdapter = new OleDbDataAdapter(oleDbCommand);
			oleDbDataAdapter.Fill(dataSet);
			oleDbConnection.Dispose();
			return dataSet;
		}
Example #24
0
 // 查询教师名单
 private void button1_Click(object sender, EventArgs e)
 {
     string ConStr = "Provider=Microsoft.Jet.OleDb.4.0;";
     ConStr += @"Data Source=People.mdb";
     OleDbConnection oleCon = new OleDbConnection(ConStr);
     OleDbDataAdapter oleDap = new OleDbDataAdapter("select * from t_teacher", oleCon);
     DataSet ds = new DataSet();
     oleDap.Fill(ds, "t_teacher");
     this.dataGridView1.DataSource = ds.Tables[0].DefaultView;
     oleCon.Close();
     oleCon.Dispose();
     dataGridView1Style();                                      //调用Gridview方法
 }
        private void btnContinue_Click(object sender, EventArgs e)
        {
            //add record, get id, close form
            OleDbConnection objConn;
            OleDbCommand objCommand;

            string strSQL;

            try
            {
                objConn = new OleDbConnection(clsAppSettings.GetAppSettings().strCTConn);

                objConn.Open();

                strSQL = "INSERT INTO tblRecords " +
                        "( blnCamper, " +
                            "lngStateID, " +
                            "strFirstName, strLastCoName, strCompanyName, strAddress, strCity, strZip, strHomePhone, strWorkPhone, strCellPhone, strEmail ) " +
                        "SELECT 1 AS blnCamper, " +
                            ((clsCboItem)cboState.SelectedItem).ID + ", " +
                            "\"" + txtFName.Text + "\", \"" + txtLName.Text + "\", \"" + txtCompany.Text + "\", \"" + txtAddress.Text + "\", \"" + txtCity.Text + "\", \"" + txtZip.Text + "\", \"" + txtHomePhone.Text + "\", \"" + txtWorkPhone.Text + "\", @strCellPhone, \"" + txtEMail.Text + "\";";

                objCommand = new OleDbCommand(strSQL, objConn);

                objCommand.Parameters.AddWithValue("@strCellPhone", txtCellPhone.Text);

                if (objCommand.ExecuteNonQuery() > 0)
                {
                    objCommand.CommandText = "SELECT @@IDENTITY;";
                    objCommand.Parameters.Clear();

                    lngRecordID = int.Parse(objCommand.ExecuteScalar().ToString());
                }
                else
                {
                    lngRecordID = 0;
                }

                objConn.Close();

                objCommand.Dispose();
                objConn.Dispose();
            }
            catch (Exception ex)
            {
                clsErr.subLogErr("frmAddIRForGGCCWebReg.btnContinue", ex);
                lngRecordID = 0;
            }

            this.Close();
        }
    public string FillLegal()
    {
        string tbl = "admin";
        string sqlStr = "SELECT * FROM " + tbl;
        string legal = string.Empty;

        try
        {
            OleDbConnection cnn = new OleDbConnection(Base.cnnStr);
            OleDbDataAdapter oda = new OleDbDataAdapter(sqlStr, cnn);
            OleDbCommand cmd = new OleDbCommand(sqlStr, cnn);
            cnn.Open();
            OleDbDataReader drr = cmd.ExecuteReader();

            DataSet ds = new DataSet();

            oda.Fill(ds, "admin");

            while (drr.Read())
            {
                legal = EncDec.Decrypt(drr["legal"].ToString(), Base.hashKey);
                break;
            }

            drr.Close();
            cnn.Close();

            cmd.Dispose();
            drr.Dispose();
            ds.Dispose();
            oda.Dispose();
            cnn.Dispose();

            cmd = null;
            drr = null;
            ds = null;
            oda = null;
            cnn = null;
        }
        catch
        {
            legal = errInvalidLegal;
        }
        finally
        {
            tbl = null;
            sqlStr = null;
        }

        return legal;
    }
 /// <summary>
 /// 读取excel文件数据
 /// </summary>
 /// <param name="filepath">文件物理路径</param>
 /// <param name="fields">字段映射</param>
 /// <returns></returns>
 public DataTable CallExcel(string filepath, string Worksheet, Dictionary<string, string> fields)
 {
     string strConn = GetExcelConnectionString(filepath);
     OleDbConnection objConn = new OleDbConnection(strConn);
     objConn.Open();
     string sql = "select * from ["+Worksheet+"$]";
     OleDbDataAdapter adapter = new OleDbDataAdapter(sql, objConn);
     DataTable dt = new DataTable();
     adapter.Fill(dt);
     dt = ExchangeColName(dt, fields);
     objConn.Close();
     objConn.Dispose();
     return dt;
 }
Example #28
0
 private void AccessToExcel(string P_str_Access,string P_str_Table,string P_str_Excel)
 {
     string P_str_Con = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + P_str_Access + ";Persist Security Info=True";//记录连接Access的语句
     string P_str_Sql = "";//存储要执行的SQL语句
     OleDbConnection oledbcon = new OleDbConnection(P_str_Con);//实例化OLEDB连接对象
     OleDbCommand oledbcom;//定义OleDbCommand对象
     oledbcon.Open();//打开数据库连接
     //向Excel工作表中导入数据
     P_str_Sql = @"select * into [Excel 12.0;database=" + P_str_Excel + "]." + "[Sheet1]" + " from " + P_str_Table + "";//记录连接Excel的语句
     oledbcom = new System.Data.OleDb.OleDbCommand(P_str_Sql, oledbcon);//实例化OleDbCommand对象
     oledbcom.ExecuteNonQuery();//执行SQL语句,将数据表的内容导入到Excel中
     oledbcon.Close();//关闭数据库连接
     oledbcon.Dispose();//释放资源
 }
Example #29
0
 private void Frm_Main_Load(object sender, EventArgs e)
 {
     string ConStr = string.Format(//设置数据库连接字符串
         @"Provider=Microsoft.Jet.OLEDB.4.0;Data source='{0}\test.mdb'",
         Application.StartupPath);
     OleDbConnection oleCon = new OleDbConnection(ConStr);//创建数据库连接对象
     OleDbDataAdapter oleDap = new OleDbDataAdapter(//创建数据适配器对象
         "select * from 帐目", oleCon);
     DataSet ds = new DataSet();//创建数据集
     oleDap.Fill(ds, "帐目");//填充数据集
     this.dataGridView1.DataSource =//设置数据源
         ds.Tables[0].DefaultView;
     oleCon.Close();//关闭数据库连接
     oleCon.Dispose();//释放连接资源
 }
Example #30
0
        //파일에서 불러오기
        private void buttonAddByFile_Click(object sender, EventArgs e)
        {
            //xls파일 불러와서 테이블에 저장
            string strConnection = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=Data.xls;Extended Properties='Excel 8.0;HDR=No'";
            DataTable dTable = new DataTable();
            int rCnt=0;

            //SQL문을 통한 OleDb로 자료 읽기
            try
            {
                OleDbConnection oleDbCon = new OleDbConnection(strConnection);
                oleDbCon.Open();
                string strSQL = "SELECT * FROM [Course$]";
                OleDbCommand dbCommand = new OleDbCommand(strSQL, oleDbCon);
                OleDbDataAdapter dataAdapter = new OleDbDataAdapter(dbCommand);

                dataAdapter.Fill(dTable);
                dTable.Dispose();
                dataAdapter.Dispose();
                dbCommand.Dispose();
                oleDbCon.Close();
                oleDbCon.Dispose();
            }
            catch (SystemException ex)
            {
                MessageBox.Show(ex.Message);
            }

            //데이터그리드뷰에 데이터 입력
            try
            {
                foreach (DataRow row in dTable.Rows)
                {
                    int cnt = 0;
                    pRO1COURSESBindingSource.AddNew();
                    foreach (DataColumn col in dTable.Columns)
                    {
                        dataGridViewMakeCrs.Rows[rCnt].Cells[cnt].Value = dTable.Rows[rCnt].ItemArray[cnt];
                        cnt++;
                    }
                    rCnt++;
                }
            }
            catch (NoNullAllowedException ex)
            {
                MessageBox.Show("완성되지 않은 행이 있습니다. 삭제 후 다시 시도해주세요.");
            }
        }