存储过程 c# 基础封装 学习之用
来源:互联网 发布:淘宝css是什么 编辑:程序博客网 时间:2024/05/17 22:30
实现了数据库用存储过程增删改查的基础功能 仅供学习
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;
}
}
}
- 存储过程 c# 基础封装 学习之用
- 存储过程基础之PLSQL学习
- c#学习之-c#通过sql存储过程实现分页
- c#学习之--封装
- Snail—ORACLE基础之存储过程学习(四)
- oracle存储过程基础学习
- Oracle存储过程基础学习
- MySQL基础之存储过程
- MySQL基础之存储过程
- sql基础之存储过程
- c# 用存储过程
- 企业库EnterpriseLibrary学习笔记之--通过企业库Microsoft.Practices.EnterpriseLibrary,实现调用存储过程的封装
- mysql学习之存储过程
- 数据库学习之存储过程
- 【Oracle学习】之 存储过程
- Oracle学习之存储过程
- oracle学习之存储过程
- 存储过程封装
- 启动mysql发现.socket错误,一般是mysql启动了两次。
- Linux命令大全
- html计算机
- buildAsset分析(二)——BuildCommon
- Linux Shell脚本攻略1:小试牛刀(2)
- 存储过程 c# 基础封装 学习之用
- Java八大排序算法
- spring boot Rabbitmq集成,延时消息队列实现
- 数据结构概念篇
- web前端与php后台做个简单的前后交互,传递数组,制作jquery版tab切换
- 京东 笔试 编程
- 亲测GO环境搭建,理解go build、go install、go get
- Http标准协议Android网络框架——NoHttp
- 设计模式——六大原则