实现了数据库用存储过程增删改查的基础功能 仅供学习
BaseDao.cs 提供数据库的相关调用 包括存储过程
using MySql.Data.MySqlClient;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
namespace DBTest
{
public abstract class BaseDao
{
public string ConnectionString = "server=localhost;user id=root; password=123; database=DBTest; pooling=true; charset=utf8";
MySqlConnection connection;
public BaseDao()
{
connection = new MySqlConnection(ConnectionString);
}
//增删改操作
protected object ExecuteNonQuery(string cmdText, params MySqlParameter[] commandParameters)
{
return ExecuteNonQuery(null, CommandType.StoredProcedure, cmdText, commandParameters);
}
protected object ExecuteNonQuery(MySqlTransaction trans, CommandType cmdType, string cmdText, params MySqlParameter[] commandParameters)
{
MySqlCommand cmd = new MySqlCommand();
PrepareCommand(cmd, connection, trans, cmdType, cmdText, commandParameters);
int result = cmd.ExecuteNonQuery();
cmd.Parameters.Clear();
return result;
}
//查操作
protected List<Dictionary<string, object>> ExecuteQuery(string cmdText, params MySqlParameter[] commandParameters)
{
return ExecuteQuery(null, CommandType.StoredProcedure, cmdText, commandParameters);
}
protected List<Dictionary<string, object>> ExecuteQuery(MySqlTransaction trans, CommandType cmdType, string cmdText, params MySqlParameter[] commandParameters)
{
MySqlCommand cmd = new MySqlCommand();
PrepareCommand(cmd, connection, trans, cmdType, cmdText, commandParameters);
MySqlDataReader sdr = cmd.ExecuteReader();
List<Dictionary<string, object>> list = new List<Dictionary<string, object>>();
while (sdr.Read())
{
int fieldNum = sdr.VisibleFieldCount;
Dictionary<string, object> dic = new Dictionary<string, object>();
for(int i = 0;i < fieldNum;++i)
{
dic.Add(sdr.GetName(i),sdr[i]);
}
list.Add(dic);
}
return list;
}
private void PrepareCommand(MySqlCommand cmd, MySqlConnection conn, MySqlTransaction trans, CommandType cmdType, string cmdText, MySqlParameter[] cmdParms)
{
if (conn.State != ConnectionState.Open)
conn.Open();
cmd.Connection = conn;
cmd.CommandText = cmdText;
if (trans != null)
cmd.Transaction = trans;
cmd.CommandType = cmdType;
if (cmdParms != null)
{
foreach (MySqlParameter parm in cmdParms)
cmd.Parameters.Add(parm);
}
}
}
}
UserDao.cs 实现细节
using MySql.Data.MySqlClient;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
namespace DBTest
{
public class UserDao : BaseDao
{
public void AddUser(User t)
{
ExecuteNonQuery("AddUser", new MySqlParameter[]
{
//这里id是存储过程的参数id
new MySqlParameter("id",t.id),
new MySqlParameter("num",t.num),
});
}
public void DeleteUser(string id)
{
ExecuteNonQuery("DeleteUser", new MySqlParameter[]
{
new MySqlParameter("id",id),
});
}
public void UpdateUser(string id,int num)
{
ExecuteNonQuery("UpdateUser", new MySqlParameter[]
{
new MySqlParameter("num",num),
new MySqlParameter("id",id),
});
}
public int SelectUserNum(int numValue)
{
List<Dictionary<string, object>> list = ExecuteQuery("SelectUserNum",new MySqlParameter("numValue",numValue));
Dictionary<string, object> dic = list[0];
int num = Convert.ToInt32(dic["num"]);
return num;
}
public List<User> SelectAllUserByNum(int num)
{
List<Dictionary<string, object>> list = ExecuteQuery("SelectAllUserByNum", new MySqlParameter("num", num));
List<User> listT = new List<User>();
for (int i = 0; i < list.Count; ++i)
{
Dictionary<string, object> dic = list[i];
User temp = new User();
//这里kaller_id是表中字段kaller_id
temp.id = dic["kaller_id"].ToString();
temp.num = Convert.ToInt32(dic["kaller_num"]);
listT.Add(temp);
}
return listT;
}
}
}