Mysql操作 調用
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());
