Rumah > pangkalan data > tutorial mysql > 一个简单的ORM制作(CURD操作类)

一个简单的ORM制作(CURD操作类)

黄舟
Lepaskan: 2016-12-28 13:56:48
asal
1266 orang telah melayarinya

SQL执行类

CURD操作类

其他酱油类


CURD操作类负责将用户提供的条件转换为SQL语句,并提供给IHelper执行,返回Model集合.

477.png

CURD类需要一个接口抽象出公共方法.便于修改和扩展,提供泛型接口。为了简单起见暂时未提供JOIN的实现,可以以数据库视图替代

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

25

26

27

28

29

30

31

public interface IDbOper<T> : IDisposable where T : new()

    {

       object Insert(T m);//新增MODEL,返回ID,简单起见只做了INT自增

       int Update(string str);//批量更新

       int Update(T m);//Model更新

       int Delete();//删除

        ///拼接字符版,需要自己防止注入,特别是Orderby容易被忽视

       IDbOper<T> Select(string sl);//选择字段

       IDbOper<T> Where(string sl);

       IDbOper<T> Orderby(string orby);

        ///Expression版重载,转化为参数方式执行,以参数方式拼接无注入风险

       IDbOper<T> Select(Expression<Func<T, object>> sl);

       IDbOper<T> Where(Expression<Func<T, bool>> sl);

       ///Dictionary版重载,需要牛顿JSON帮忙转化,以参数方式拼接无注入风险,此方式用于“等于”等查询方式,不提供大于小于查询

       IDbOper<T> Orderby(Dictionary<string, string> dic);

       IDbOper<T> Where(Dictionary<string, object> dic);

        ///

       IDbOper<T> Index(int i);

       IDbOper<T> Size(int i);

       T First();//获取第一个model

       void BegTran();

       void RollBack();

       void Commit();

       M ToObj<M>(Func<IDataReader, M> func,string sql);

       List<T> ToList();

     //转化为其他类型,若开启了事务的话需要此转化

       IDbOper<M> ToOper<M>() where M : new();

       int Count();

       //直接执行SQL语句

       int DoCommand(string sql, bool issp);

    }

Salin selepas log masuk

由于比较喜欢JQ的操作方式,所以想将这种执行方式带到后台操作数据库,废话不说了先定义2个Model和实例化一个操作类

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

public class User

{

     [Key]

     public int ID{get;set;}

     public string UserName{get;set;}

     public string Password{get;set;}

}

public class NewUser

{

     [Key]

     public int ID{get;set;}

     public string UserName{get;set;}

     public string Password{get;set;}

}

var db=new DbOper<User>(new DbInfo(){DbType="…",DbConntion="…"});

Salin selepas log masuk

表达式的执行

1

User a=db.Select(u=>new{u.ID}).Where(u=>u.ID==54).First();

Salin selepas log masuk

文本拼接的执行

1

User a=db.Select("*").Where("ID=54").First();

Salin selepas log masuk

字典拼接的执行

1

User a=db.Select("*").Where(new Dictionary<string, object>(){Key="ID",Value=54}).First();

Salin selepas log masuk

分页代码

1

List<User> lt=db.Select("*").Where("ID>0").Orderby("ID Desc").Index(2).Size(20).ToList();

Salin selepas log masuk

事务的运用

1

2

3

4

5

6

7

db.BegTran();

try{

    int b=db.Where("ID=54").Delete();//user表删除ID=54

    int c=db.ToOper<NewUser>().Insert(new NewUser(){UserName="…",Password="…"});//newuser表新增一条记录

    db.Commit();

}

catch{db.RollBack();}

Salin selepas log masuk

只有当调用Insert,Update,Delete,Count,ToList方法才会开始拼接文本再调用IHelper执行SQL语句,调用完成后会自动调用Clear()来清理保存的where,select等信息。

以下是我提供一个操作类的实现,大家也可以实现自己的操作类。

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

25

26

27

28

29

30

31

32

33

34

35

36

37

38

39

40

41

42

43

44

45

46

47

48

49

50

51

52

53

54

55

56

57

58

59

60

61

62

63

64

65

66

67

68

69

70

71

72

73

74

75

76

77

78

79

80

81

82

83

84

85

86

87

88

89

90

91

92

93

94

95

96

97

98

99

100

101

102

103

104

105

106

107

108

109

110

111

112

113

114

115

116

117

118

119

120

121

122

123

124

125

126

127

128

129

130

131

132

133

134

135

136

137

138

139

140

141

142

143

144

145

146

147

148

149

150

151

152

153

154

155

156

157

158

159

160

161

162

163

164

165

166

167

168

169

170

171

172

173

174

175

176

177

178

179

180

181

182

183

184

185

186

187

188

189

190

191

192

193

194

195

196

197

198

199

200

201

202

203

204

205

206

207

208

209

210

211

212

213

214

215

216

217

218

219

220

221

222

223

224

225

226

227

228

229

230

231

232

233

234

235

236

237

238

239

240

241

242

243

244

245

246

247

248

249

250

251

252

253

254

255

256

257

258

259

260

261

262

263

264

265

266

267

268

269

270

271

272

273

274

275

276

277

278

279

280

281

282

283

284

285

286

287

288

289

290

291

292

293

294

295

296

297

298

299

300

301

302

303

304

305

internal class DbOper<T> :IDbPhysiceOper<T>, IDisposable where T : new()

    {

        internal IHelper db;

        internal StringBuilder where;

        internal StringBuilder select;

        internal StringBuilder orderby;

        internal List<IDataParameter> ps;

        internal StringBuilder sqlinfo;

        internal int index = 0;

        internal int size = OrmGlobal.PageSize;//提供一个默认分页大小

        private DbOper(IHelper h, StringBuilder w, StringBuilder s, StringBuilder or, List<IDataParameter> p,StringBuilder sql)

        {

            db = h;

            where = w;

            select = s;

            orderby = or;

            sqlinfo = sql;

            ps = p;

        }

        internal DbOper(DbInfo info)

        {            //db为上篇上定义的数据库操作类,分分种切换到其他数据库            if (info.DbType.Equals("mssql"))

            {

                db = new Helper.Mssql(info.DbConntion);

            }

            else if (info.DbType.Equals("msmars"))

            {

                db = new Helper.MsMars(info.DbConntion);

            }

            else if (info.DbType.Equals("mysql"))

            {

                db = new Helper.Mysql(info.DbConntion);

            }

            where = new StringBuilder();

            select = new StringBuilder();

            orderby = new StringBuilder();

            sqlinfo = new StringBuilder();

            ps = new List<IDataParameter>();

        }

        public object Insert(T m)

        {

            try

            {

                StringBuilder fields = new StringBuilder();

                StringBuilder values = new StringBuilder();

                List<IDataParameter> lt = new List<IDataParameter>();

                string tp = string.Empty; object o = null;

                foreach (var n in m.GetType().GetProperties())

                {

                    if (n.GetCustomAttributes(typeof(ExcludeColumn), false).Length > 0) { continue; }

                    if (n.GetCustomAttributes(typeof(Key), false).Length > 0) { continue; }

                    o = n.GetValue(m,null);//4.5o = n.GetValue(m);

                    if (o == null) { continue; }

                    fields.Append(n.Name + ",");

                    tp = db.ParStr(n.Name);

                    values.Append(tp + ",");

                    lt.Add(db.Cp(tp, o));

                }

                if (fields.Length > 0) { fields.Length--; }

                if (values.Length > 0) { values.Length--; }

                tp = "INSERT INTO " + typeof(T).Name + "(" + fields.ToString() + ")VALUES(" + values.ToString() + ") " + db.GetIdStr;

                if (OrmGlobal.isrecord) { Record(tp); }

                object a = db.ExectueScalar(tp, lt, false);

                Clear();

                return a;

            }

            catch

            {

                OrmGlobal.DoErr(sqlinfo.ToString()); throw;

            }

        }

        public int Update(string str)

        {

            try

            {

                string tp = "UPDATE " + typeof(T).Name + " SET " + str + (where.Length > 0 ? " WHERE " + where : string.Empty);

                if (OrmGlobal.isrecord) { Record(tp); }

                int i = db.ExecuteQuery(tp, ps, false);

                Clear();

                return i;

            }

            catch

            {

                OrmGlobal.DoErr(sqlinfo.ToString()); throw;

            }

        }

        public int Update(T m)

        {

            try

            {

                StringBuilder sb = new StringBuilder();

                sb.Append("UPDATE " + typeof(T).Name + " SET ");

                List<IDataParameter> lt = new List<IDataParameter>();

                object o = null;

                foreach (var n in m.GetType().GetProperties())

                {//需要定义一个特性Key,以便更新Model                    o = n.GetValue(m,null);//4.5o = n.GetValue(m);

                    if (o == null) { continue; }

                    if (n.GetCustomAttributes(typeof(Key), false).Length > 0)

                    {

                        where.Append((where.Length > 0 ? " AND " : string.Empty) + n.Name + "=" + db.ParStr(n.Name));

                        lt.Add(db.Cp(db.ParStr(n.Name), o));

                        continue;

                    }

                    sb.Append(n.Name + "=" + db.ParStr(n.Name) + ",");

                    lt.Add(db.Cp(db.ParStr(n.Name), o));

                }

                if (sb.Length > 0) { sb.Length--; }

                if (where.Length > 0) { sb.Append(" WHERE " + where); }

                var sql = sb.ToString();

                if (OrmGlobal.isrecord) { Record(sql); }

                int i = db.ExecuteQuery(sql, lt, false);

                Clear();

                return i;

            }

            catch

            {

                OrmGlobal.DoErr(sqlinfo.ToString()); throw;

            }

        }

        public int Delete()

        {

            try

            {

                string sql = "DELETE FROM " + typeof(T).Name + (where.Length > 0 ? " WHERE " + where : string.Empty);

                if (OrmGlobal.isrecord) { Record(sql); }

                int i = db.ExecuteQuery(sql, ps, false);

                Clear();

                return i;

            }

            catch

            {

                OrmGlobal.DoErr(sqlinfo.ToString()); throw;

            }

        }

        public IDbOper<T> Select(string sl)

        {

            if (string.IsNullOrEmpty(sl)) { return this; }

            select.Append((select.Length > 0 ? "," : string.Empty) + sl); return this;

        }

        public IDbOper<T> Select(Expression<Func<T, object>> sl)

        {

            string tp=null;

            using (var tp1 = new LinqVisitor())

            {

                tp=tp1.VisitNew(sl.Body as NewExpression);

            }

            return Select(tp);

        }

        public IDbOper<T> Where(Dictionary<string, object> dic)

        {

            if (dic == null || dic.Count == 0) { return this; }

            var sb = new StringBuilder(); string tp;

            foreach (var n in dic)

            {

                if (sb.Length > 0) { sb.Append(" AND "); }

                sb.Append(n.Key);

                if (n.Value is string)

                {

                    tp = n.Value.ToString();

                    if (tp.Substring(tp.Length - 1, 1) == "*")

                    {

                        sb.Append(" LIKE ");

                        tp = tp.Substring(0, tp.Length - 1) + "%";

                    }

                    else { sb.Append("="); }

                    ps.Add(db.Cp(db.ParStr(n.Key), tp));

                }

                else

                {

                    sb.Append("=");

                    ps.Add(db.Cp(db.ParStr(n.Key), n.Value));

                }

                sb.Append(db.ParStr(n.Key));

            }

            Where(sb.ToString());

            return this;

        }

        public IDbOper<T> Where(string sl)

        {

            if (string.IsNullOrEmpty(sl)) { return this; }

            where.Append((where.Length > 0 ? " AND " : string.Empty) + sl); return this;

        }

        public IDbOper<T> Where(Expression<Func<T, bool>> sl)

        {

            List<object> tp=null;             //需要解析表达式树            using (var tp1 = new LinqVisitor())

            {

                tp = tp1.Visit(sl) as List<object>;

                StringBuilder sb = new StringBuilder(); string s = string.Empty;

                for (int i = 0; i < tp.Count; i += 4)

                {

                    s = db.ParStr(tp[i].ToString());

                    sb.Append(tp[i].ToString() + tp[i + 1].ToString() + s);

                    if (i + 4 < tp.Count) { sb.Append(tp[i + 3]); }

                    ps.Add(db.Cp(s, tp[i + 2]));

                }

                Where(sb.ToString());

            }

            return this;

        }

        public IDbOper<T> Orderby(string orby)

        {

            if (string.IsNullOrEmpty(orby)) { return this; }

            orderby.Append((orderby.Length > 0 ? "," : string.Empty) + orby); return this;

        }

        public IDbOper<T> Orderby(Dictionary<string, string> dic)

        {

            if (dic.Count == 0) { return this; }

            StringBuilder sb = new StringBuilder();

            foreach (var n in dic.Keys)

            {

                if(string.Compare("DESC",dic[n],true)!=0 && string.Compare("ASC",dic[n],true)!=0){continue;}

                sb.Append(n + " " + dic[n] + ",");

            }

            if (sb.Length > 0) { sb.Length--; }

            Orderby(sb.ToString()); return this;

        }

        public IDbOper<T> Index(int i) { if (i > 0) { index = i; } return this; }

        public IDbOper<T> Size(int i) { if (i > 0) { size = i; } return this; }

        public void BegTran() { db.BegTran(); }

        public void RollBack() { db.RollBack(); }

        public void Commit() { db.Commit(); }

        public void Clear()

        {

            where.Length = 0; select.Length = 0; orderby.Length = 0; ps.Clear(); index = 0; size = OrmGlobal.size;

        }

        public M ToObj<M>(Func<IDataReader, M> func, string sql)

        {

            try

            {

                if (OrmGlobal.isrecord) { Record(sql); }

                var rd = db.ExectueReader(sql, ps, false);

                M t = func(rd);

                rd.Close(); Clear();

                return t;

            }

            catch

            {

                OrmGlobal.DoErr(sqlinfo.ToString()); throw;

            }

        }

        public List<T> ToList()

        {

            string sql = GetSql();

            return ToObj<List<T>>(rd => ToList(rd),sql);

        }        //返回List<T>类型        public List<T> ToList(IDataReader rd)

        {

            var lt = new List<T>();

            var set = DelegateExpr.SetMethod(typeof(T));//ExpressTree实现属性绑定,以提高Model赋值性能,可以以反射代替

            while (rd.Read())

            {

                var m = new T();

                for (var i = 0; i < rd.FieldCount; i++)

                {

                    if (rd[i] == DBNull.Value || rd[i] == null) { continue; }

                    set(m, rd.GetName(i).ToLower(), rd[i]);

                }

                lt.Add(m);

            }

            return lt;

        }

        public string GetSql()

        {

            return db.CreateSql(select.ToString(), typeof(T).Name, where.ToString(), orderby.ToString(), size, index);

        }

        public IDbOper<M> ToOper<M>() where M:new()

        {

            Clear();

            return new DbOper<M>(db,where,select,orderby,ps,sqlinfo);

        }

        public int Count()

        {

            try

            {

                string sql = "SELECT COUNT(*) FROM " + typeof(T).Name + (where.Length > 0 ? " WHERE " + where : string.Empty);

                if (OrmGlobal.RecordLog) { Record(sql); }

                int i= (int)db.ExectueScalar(sql, ps, false);

                Clear();

                return i;

            }

            catch

            {

                OrmGlobal.DoErr(sqlinfo.ToString()); throw;

            }

        }

        public int DoCommand(string sql,bool issp)

        {

            int i=db.ExecuteQuery(sql,ps,issp);

            Clear();

            return i;

        }

        public void Dispose()

        {

            where = null; select = null; orderby = null; db.Dispose(); ps = null; sqlinfo = null;

            GC.SuppressFinalize(this);

        }

        public T First()

        {

            var lt=Size(1).Index(1).ToList();

            if (lt.Count > 0) { return lt[0]; }

            return default(T);

        }

        ~DbOper()

        {

            Dispose();

        }

    }

Salin selepas log masuk

 以上就是一个简单的ORM制作(CURD操作类)的内容,更多相关内容请关注PHP中文网(www.php.cn)!


Label berkaitan:
Kenyataan Laman Web ini
Kandungan artikel ini disumbangkan secara sukarela oleh netizen, dan hak cipta adalah milik pengarang asal. Laman web ini tidak memikul tanggungjawab undang-undang yang sepadan. Jika anda menemui sebarang kandungan yang disyaki plagiarisme atau pelanggaran, sila hubungi admin@php.cn
Tutorial Popular
Lagi>
Muat turun terkini
Lagi>
kesan web
Kod sumber laman web
Bahan laman web
Templat hujung hadapan