写在前面:刚学Dapper的小白 欢迎各位同行指正!!!
Dbhelp类
class Dbhelp { //全局变量 DynamicParameters Dynparam = new DynamicParameters(); //sql语句中的where条件 log4net.ILog logInfo = log4net.LogManager.GetLogger("loginfo"); //数据库连接语句 public string ConnectionString { get { return ConfigurationManager.ConnectionStrings["SqlServerConnString"].ConnectionString; } } //数据库连接对象 public IDbConnection Db { get { return new SqlConnection(ConnectionString); } } //设置参数 public void setParam<T>(string param, T value) { Dynparam.Add(param, value); } //重写查询语句 //querystr 查询语句 public List<T> query<T>(string querystr) { List<T> result = null; try { result = (List<T>)Db.Query<T>(querystr, Dynparam); dispose(); } catch (Exception ex) { logInfo.Info(ex.ToString()); } return result; } //执行插入、修改、删除语句,返回受影响的行数 public int execute(string sql) { int row = 0; try { row = Db.Execute(sql, Dynparam); dispose(); } catch (Exception ex) { logInfo.Info(ex.ToString()); } return row; } //释放动态参数 public void dispose() { Dynparam = new DynamicParameters(); } }
//app.config 连接配置
<connectionStrings>
<add name="SqlServerConnString" providerName="System.Data.SqlClient" connectionString="server=服务器名称;database=数据库名称;User ID=用户名;Password=密码!"/>
</connectionStrings>
//如何调用
1、查询
string str2 = string.Format("select stuname from student where stuname like @stuname"); dbhelp.setParam("stuname", "李敏希");//全查询 dbhelp.setParam("stuname", "%唐%");//模糊查询 List<student> lststudent2 = dbhelp.query<student>(str2); foreach (var item in lststudent2) { Console.WriteLine(item.StuName + "\n"); }
2、修改
string updateSql = string.Format(" update student set StuName =@StuName where StuID = '20130523070'"); dbhelp.setParam("StuName", "吴A5"); int row = dbhelp.execute(updateSql); Console.WriteLine(row); Console.ReadKey();
3、删除
//删除--单条件 string deleteSql = string.Format(" delete from student where StuID = '20130523070'"); int row2 = dbhelp.execute(deleteSql); Console.WriteLine(row2); Console.ReadKey(); //删除--多条件 string deleteSql2 = string.Format("delete from student where StuID in @StuId"); dbhelp.setParam("StuId", new string[] { "20130523071", "20130523072" }); int row21 = dbhelp.execute(deleteSql2); Console.WriteLine(row21); Console.ReadKey();
4、插入
string insertstr = string.Format("insert into student (StuID,StuName,StuSex,StuAge,StuBirth,StuCardID,IsDelete) values" + " (@StuID,@StuName,@StuSex,@StuAge,@StuBirth,@StuCardID,@IsDelete)"); dbhelp.setParam("StuID", "20200925"); dbhelp.setParam("StuName", "测试小三"); dbhelp.setParam("StuSex", 2); dbhelp.setParam("StuAge", 18); dbhelp.setParam("StuBirth", "1997-09-15"); dbhelp.setParam("StuCardID", 200000000); dbhelp.setParam("IsDelete", 1); int row3 = dbhelp.execute(insertstr); Console.WriteLine(row3); Console.ReadKey();