你的位置:首页 > ASP.net教程

[ASP.net教程]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 onepc_username=@onepc_username and onepc_password=@onepc_password";// and onepc_password=@onepc_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());