C# winform处理数据库

//公共类Workclass:

//数据库放在根目录App_Data文件夹mySQL.mdb下.

using System.Data;
using System.Data.OleDb;
using System.IO;
using System.Configuration;
using System.Windows.Forms;
using System.Collections;

namespace mySQL
{

    /// <summary>
    /// 存储设置的字段类
    /// </summary>
    public class field
    {
        public string name;//字段名称
        public string value;//字段值
        public OleDbType type;//字段类型
        public Int32 size;//字段大小
    }

 

    class Workclass
    {

        //连接数据库

        public OleDbConnection OledCon()
        {
            string reportPath = Application.StartupPath.Substring(0, Application.StartupPath.Substring(0,Application.StartupPath.LastIndexOf("//")).LastIndexOf("//"));
            reportPath += @"/DataBase/question.mdb";
            string ConStr = "Provider=Microsoft.Jet.OLEDB.4.0;Data source=" + reportPath;
            try
            {
                con = new OleDbConnection(ConStr);
            }
            catch (OleDbException oee)
            {
                string oeestr = oee.ToString();
            }
            return con;
        }

 

 

        /// <summary>
        /// 连接数据库
        /// </summary>
        /// <returns>OleDbConnection</returns>
        public static OleDbConnection OleDbConnection()
        {
            OleDbConnection con = null;
            string provider = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=";
            DirectoryInfo mydir = new DirectoryInfo(Application.StartupPath);
            int index = Application.StartupPath.IndexOf(mydir.Parent.ToString());
            string datapath = Application.StartupPath.Substring(0, index) + "App_Data//mySQL.mdb;";

            string user = "User ID=;Password=;";
            string ConStr = provider + datapath + user;
            try
            {
                con = new OleDbConnection(ConStr);
            }
            catch (OleDbException oee)
            {
                string oeestr = oee.ToString();
            }
            return con;
        }

        public static bool OleDbInsert_info_user(string username, string password, string flag)
        {
            try
            {
                OleDbConnection oleCon = Workclass.OleDbConnection();
                string sql = "insert into info_user([Username],[Password],[Flag]) values(?,?,?)";
                OleDbCommand oleCom = new OleDbCommand(sql, oleCon);
                oleCom.Parameters.Add("?", OleDbType.LongVarWChar, 20).Value = username;
                oleCom.Parameters.Add("?", OleDbType.LongVarWChar, 20).Value = password;
                oleCom.Parameters.Add("?", OleDbType.Integer, 10).Value = flag;
                oleCon.Open();
                int Affected = oleCom.ExecuteNonQuery();
                oleCon.Close();
                oleCom.Dispose();
                return Affected > 0;
            }
            catch
            {
                return false;
            }
        }

        /// <summary>
        /// 根据sql命令执行
        /// </summary>
        /// <param name="sql">string</param>
        /// <returns>bool</returns>
        public static bool OleDbExecuteNonQuery(string sql)
        {
            try
            {
                OleDbConnection oleCon = Workclass.OleDbConnection();
                OleDbCommand oleCom = new OleDbCommand(sql, oleCon);
                oleCom.CommandText = sql;
                oleCon.Open();
                int Affected = oleCom.ExecuteNonQuery();
                oleCon.Close();
                oleCom.Dispose();
                return Affected > 0;
            }
            catch
            {
                return false;
            }
        }

        /// <summary>
        /// 根据sql命令读取表中数据
        /// </summary>
        /// <param name="sql">string</param>
        /// <returns>OleDbDataReader</returns>
        public static OleDbDataReader OleDbReader(string sql)
        {
            try
            {
                OleDbDataReader odr = null;
                OleDbConnection oleCon = Workclass.OleDbConnection();
                OleDbCommand oleCom = new OleDbCommand(sql, oleCon);
                oleCom.CommandText = sql;
                oleCon.Open();
                odr = oleCom.ExecuteReader();              
                oleCom.Dispose();
                return odr;
            }
            catch
            {
                return null;
            }
        }

        /// <summary>
        /// 向access插入指定数据
        /// </summary>
        /// <param name="TableName"></param>
        /// <param name="arraylist">field类数组列表</param>
        /// <returns>bool</returns>
        public static bool OleDbInsert(string TableName,ArrayList arraylist)
        {
            try
            {
                OleDbConnection oleCon = Workclass.OleDbConnection();
                int icount = arraylist.Count;
                string sqlstrd = "(";
                string sqlstrv = "(";
                foreach(field myfield in arraylist)
                {
                    sqlstrd += "[" + myfield.name + "],";
                    sqlstrv += "?,";
                }
                sqlstrd = sqlstrd.Substring(0, sqlstrd.Length - 1) + ")";
                sqlstrv = sqlstrv.Substring(0, sqlstrv.Length - 1) + ")";

                string sql = "insert into " + TableName + sqlstrd + " values" + sqlstrv;
                OleDbCommand oleCom = new OleDbCommand(sql, oleCon);
                foreach (field myfield in arraylist)
                {                 
                    oleCom.Parameters.Add("?", myfield.type, myfield.size).Value = myfield.value;                  
                }
               
                oleCon.Open();
                int rowsAffected = oleCom.ExecuteNonQuery();
                oleCon.Close();
                oleCom.Dispose();
                return rowsAffected > 0;
            }
            catch
            {
                return false;
            }
        }   

   }
}

 

下面实现调用:

private void button1_Click(object sender, EventArgs e)
        {
            if (Workclass.OleDbInsert_info_user(textBox1.Text.Trim(), textBox2.Text.Trim(), textBox3.Text.Trim()))
            {
                MessageBox.Show("插入成功!");
            }
            else
            {
                MessageBox.Show("插入失败!");
            }
        }

        private void button2_Click(object sender, EventArgs e)
        {
            string sql = "select * from info_user where ID=1";
            OleDbDataReader odr = Workclass.OleDbReader(sql);
            if (odr.Read())
            {
                textBox1.Text = odr["Username"].ToString();
                textBox2.Text = odr["Password"].ToString();
                textBox3.Text = odr["Flag"].ToString();          
            }
            odr.Close();
        }

        private void button3_Click(object sender, EventArgs e)
        {
            string sql = "delete from info_user where ID=1";
            if (Workclass.OleDbExecuteNonQuery(sql))
            {
                MessageBox.Show("删除成功!");
            }
            else
            {
                MessageBox.Show("删除失败!");
            }
        }

        private void button4_Click(object sender, EventArgs e)
        {
            string sql = "update info_user set [Username]='" + textBox1.Text.Trim() + "',[Password]='" + textBox2.Text.Trim() + "',[Flag]=" + Convert.ToInt32(textBox3.Text.Trim()) + " where ID=2";
            if (Workclass.OleDbExecuteNonQuery(sql))
            {
                MessageBox.Show("更新成功!");
            }
            else
            {
                MessageBox.Show("更新失败!");
            }
        }

 

       private void button5_Click(object sender, EventArgs e)
        {
            ArrayList mylist = new ArrayList();
            field myfield = new field();
            myfield.name = "Username";
            myfield.value = textBox1.Text.Trim();
            myfield.type = OleDbType.LongVarWChar;
            myfield.size = 10;
            mylist.Add(myfield);
            myfield = new field();
            myfield.name = "Password";
            myfield.value = textBox2.Text.Trim();
            myfield.type = OleDbType.LongVarWChar;
            myfield.size = 10;
            mylist.Add(myfield);
            myfield = new field();
            myfield.name = "Flag";
            myfield.value = textBox3.Text.Trim();
            myfield.type = OleDbType.Integer;
            myfield.size = 2;
            mylist.Add(myfield);
            if (Workclass.OleDbInsert("info_user", mylist))
            {
                MessageBox.Show("插入成功!");
            }
            else
            {
                MessageBox.Show("插入失败!");
            }
        }