c#操作資料庫,試著封裝成類 - 求誤入指點.

標籤：

Mysql操作

using System;using System.Collections.Generic;using System.Linq;using System.Text;using System.Data;using System.Data.SqlClient;using MySql.Data.MySqlClient;using System.Text.RegularExpressions;namespace importTxtToMysql{    class oMySql    {        //private static String mysqlcon = "Data Source=MySQL;Password=;User ID=root;Location=172.1.1.179";        private static String mysqlcon = "database=onepc;Password=;User ID=root;server=172.1.1.1";        private MySqlConnection conn;        public oMySql()        {            conn = new MySqlConnection(mysqlcon);        }        private void o_open()        {            if (conn.State == ConnectionState.Open)            {                conn.Close();            }            conn.Open();                }        private void o_close()        {            if (conn.State == ConnectionState.Open)            {                conn.Close();            }                    }        public DataSet ReadMysql(String cmd)        {            DataSet ds;            try            {                MySqlDataAdapter mdap = new MySqlDataAdapter(cmd, conn);                ds = new DataSet();                mdap.Fill(ds, "allhardtable");            }            catch (Exception ex)            {                ds = null;            }                  return ds;        }        //返回是否查到有使用者,若是>0則表示可以登入        //public int CheckLogin(MySqlParameter [] paras)        public int CheckLogin(String user,String pass) //出現異常返回-1        {            int loginstatus;            String sql = "select count(*) from login where [email protected]_username and [email protected]_password";// and [email protected]_password)            try            {                o_open();                MySqlCommand cmd = new MySqlCommand();                cmd.CommandText = sql;                cmd.Connection = conn;                MySqlParameter puser = new MySqlParameter("@onepc_username", MySqlDbType.VarChar, 128);                MySqlParameter ppass = new MySqlParameter("@onepc_password", MySqlDbType.VarChar, 128);                puser.Value = user;                ppass.Value = pass;                cmd.Parameters.Add(puser);                cmd.Parameters.Add(ppass);                loginstatus = Convert.ToInt32(cmd.ExecuteScalar());                /* foreach (MySqlParameter para in paras)                {                    cmd.Parameters.Add(para);                }                loginstatus = (int)cmd.ExecuteScalar();                 */            }            catch (Exception ex)            {                loginstatus = -1;            }            finally            {                o_close();            }            return loginstatus;        }        //添加記錄  1 SQL語句 2 各欄位的值 3 資料庫類型 4 類型長度        public int o_AddData(String sql, String[] input, MySqlDbType [] dbtype, int[] dbsize)        {            //String sql = "insert into login (onepc_username, onepc_password, onepc_level, onepc_display, onepc_size) values(@onepc_username, @onepc_password, @onepc_level, @onepc_display, @onepc_size)";            //Regex R = new Regex(@"\s*insert\s+into\s+w+\s*\(([^)]*)\).*");            /*MessageBox.Show(M.Value, M.Groups[1].Value);                String[] a = M.Groups[1].Value.Split(‘,‘);                MessageBox.Show(a.Length.ToString());                int[] c = new int[a.Length];                foreach (string b in a)                {                     MessageBox.Show(b);                }             */            int length = 0,result = 0;            String [] filed;            Regex R = new Regex(@"\s*insert\s+into\s+\w+\s*\(([^)]*)\).*");            Match M = R.Match(sql);            if (M.Success)            {                filed = M.Groups[1].Value.Split(‘,‘); //分割表欄位            }            else            {                result = -1;                return result;//返回-1表示正則匹配不了                        }            length = filed.Length;            if (input.Length != length || dbtype.Length != length || dbsize.Length != length)            {                 result = -2;                return result; //輸入參數的長度不對            }            MySqlCommand cmd = new MySqlCommand();            MySqlParameter[] paras = new MySqlParameter[length];            for (int i = 0; i < length; i++)            {                //int iv;                //if (dbtype[i] == MySqlDbType.Int32)                //{                //}                paras[i] = new MySqlParameter("@" + filed[i].Trim(), dbtype[i], dbsize[i] );//, input[i]);               }            for (int i = 0; i < length; i++)            {                if (dbtype[i] == MySqlDbType.Int32)                {                    paras[i].Value =Convert.ToInt32(input[i]);                }                else                {                    paras[i].Value = input[i];                                }                        }            cmd.Connection = conn;            cmd.CommandText = sql;            for (int i = 0; i < length; i++)            {                cmd.Parameters.Add(paras[i]); //添加參數                        }            try            {                o_open();                cmd.ExecuteNonQuery();                result = 1; //正常執行            }            catch (Exception ex)            {                 result = 0;                //執行SQL語句出現異常            }            finally            {                o_close();                        }            return result;        }            }}

　　調用

String sql = "insert into login (onepc_username, onepc_password, onepc_level, onepc_display, onepc_size) values(@onepc_username, @onepc_password, @onepc_level, @onepc_display, @onepc_size)";            String[] value = { "huangwen" , "mima" ,"0","心若靜冰","100"};            MySqlDbType[] vtype = { MySqlDbType.VarChar, MySqlDbType.VarChar, MySqlDbType.VarChar, MySqlDbType.VarChar, MySqlDbType.Int32 };            int [] vsize = {128,128,128,128,10};            oMySql insert = new oMySql();            int a = insert.o_AddData(sql,value,vtype,vsize);            MessageBox.Show(a.ToString());

　　

c#操作資料庫,試著封裝成類 - 求誤入指點.