dboperate

1 using System;
2 using System.Collections.Generic;
3 using System.Text;
4 using System.Reflection;
5 using System.Data;
6 using System.Data.SqlClient;
7 using System.Data.Common;
8 namespace Attr
9 {
10 class Program
11 {
12 static void Main( string [] args)
13 {
14
15 Inventory inv = new Inventory();
16 inv.InvCode = " s0001 " ;
17 inv.InvName = " gua hu dao " ;
18 inv.CreateDate = DateTime.Now;
19
20
21 DAL < Inventory > dal = new DAL < Inventory > ();
22 dal.SelectAll();
23 dal.SelectWhere( " cInvCode='001' " );
24 dal.SelectWhere(inv, " InvCode " );
25 dal.Insert(inv);
26 dal.Delete(inv);
27 dal.DeleteWhere(inv, " InvName " );
28 dal.Update(inv);
29
30 }
31
32 private static void DiagnoseObject(Student stu)
33 {
34 Type type = stu.GetType();
35 PropertyInfo[] arrPi = type.GetProperties();
36 foreach (PropertyInfo pi in arrPi)
37 {
38 Console.WriteLine(pi.Name);
39 Console.WriteLine(pi.GetValue(stu, null ));
40 Console.WriteLine((pi.GetCustomAttributes( true )[ 0 ] as FieldAttribute).FieldName);
41 Console.WriteLine( " --------------------------------- " );
42 }
43 }
44 }
45
46 public class DAL < TEntity > where TEntity : DiagnoseEntity
47 {
48 public DataTable SelectAll()
49 {
50 string sql = string .Format( " select * from {0} " , this .TableName);
51 Console.WriteLine(sql);
52 return null ;
53 }
54 public void SelectWhere( string where )
55 {
56 string sql = string .Format( " select * from {0} where {1} " , this .TableName, where );
57 Console.WriteLine(sql);
58 }
59 public void SelectWhere(TEntity entity, params string [] properties)
60 {
61 StringBuilder sql = new StringBuilder( string .Format( " select * from {0} where 1=1 " , this .TableName));
62 StringBuilder whereStatement = null ;
63 DbParameter[] arrParam = new SqlParameter[properties.Length];
64 AssembleWhereStatement(entity, properties, out whereStatement, ref arrParam);
65 sql.Append(whereStatement.ToString());
66 Console.WriteLine(sql);
67 Console.WriteLine(arrParam);
68 }
69
70
71 public void Insert(TEntity entity)
72 {
73 string [] fields = entity.GetFields();
74 string [] prefixFields = Array.ConvertAll(fields, delegate ( string str) { return " : " + str; });
75 DbParameter[] param = entity.GetParams();
76 string sql = string .Format( " insert into ({0}) values({1}) " , string .Join( " , " , fields), string .Join( " , " , prefixFields));
77 Console.WriteLine(sql);
78 Console.WriteLine(param);
79 }
80 public void Update(TEntity entity)
81 {
82 StringBuilder sql = new StringBuilder();
83 string [] fields = entity.GetFields();
84 sql.AppendFormat( " update {0} set " ,entity.GetTableName());
85 for ( int i = 0 ; i < fields.Length; i ++ )
86 {
87 sql.AppendFormat( " \n{0}=:{0} " ,fields[i]);
88 }
89 sql.Append( " \nwhere " );
90 sql.AppendFormat( " \n{0}=:{0} " ,entity.GetPKey());
91
92 DbParameter[] param = entity.GetParams();
93 Console.WriteLine(sql.ToString());
94 Console.WriteLine(param);
95 }
96 public void Delete(TEntity entity)
97 {
98 string sql = string .Format( " delete from {0} where {1}=:{1} " , entity.GetTableName(),entity.GetPKey());
99 DbParameter param = new SqlParameter(entity.GetPKey(), entity.GetPKeyValue());
100 Console.WriteLine(sql);
101 Console.WriteLine(param);
102 }
103 public void DeleteWhere(TEntity entity, params string [] properties)
104 {
105 StringBuilder sql = new StringBuilder();
106 sql.AppendFormat( " delete from {0} where 1=1 " , entity.GetTableName());
107 StringBuilder whereStatement = null ;
108 DbParameter[] arrParam = new SqlParameter[properties.Length];
109 AssembleWhereStatement(entity, properties, out whereStatement, ref arrParam);
110 sql.Append(whereStatement.ToString());
111 Console.WriteLine(sql.ToString());
112 Console.WriteLine(arrParam);
113 }
114
115 private void AssembleWhereStatement(TEntity entity, string [] properties, out StringBuilder whereStatement, ref DbParameter[] arrParam)
116 {
117 string fieldName = string .Empty;
118 object fieldValue = null ;
119 whereStatement = new StringBuilder();
120 for ( int i = 0 ; i < properties.Length; i ++ )
121 {
122 fieldName = entity.GetFieldByProperty(properties[i]);
123 fieldValue = entity.GetValueByProperty(properties[i]);
124 arrParam[i] = new SqlParameter( " : " + fieldName, fieldValue);
125 whereStatement.AppendFormat( " and {0}=:{0} " , fieldName);
126 }
127 }
128
129 private string tbName;
130 public string TableName
131 {
132 get
133 {
134 if ( string .IsNullOrEmpty(tbName))
135 {
136 tbName = ((TableAttribute) ( this .GetType().GetGenericArguments()[ 0 ].GetCustomAttributes( true )[ 0 ])).TableName;
137 }
138 return tbName;
139 }
140 }
141 }
142
143
144 [Table( " mst_student " )]
145 public class Student:DiagnoseEntity
146 {
147 [Field( " stu_code " )]
148 [PrimaryKey( true )]
149 public string StuCode { get ; set ; }
150 [Field( " stu_name " )]
151 public string StuName { get ; set ; }
152 [Field( " stu_age " )]
153 public int Age { get ; set ; }
154 [Field( " stu_birthday " )]
155 public DateTime Birthday { get ; set ; }
156 }
157
158 [Table( " archive_inventory " )]
159 public class Inventory : DiagnoseEntity
160 {
161 [Field( " cInvCode " )]
162 [PrimaryKey( true )]
163 public string InvCode { get ; set ; }
164 [Field( " cInvName " )]
165 public string InvName { get ; set ; }
166 [Field( " cInvMemo " )]
167 public string InvMemo { get ; set ; }
168 [Field( " dInvPrice " )]
169 public decimal InvPrice { get ; set ; }
170 [Field( " dInvCost " )]
171 public decimal InvCost { get ; set ; }
172 [Field( " dInvDate " )]
173 public DateTime CreateDate { get ; set ; }
174 }
175
176 public class DiagnoseEntity
177 {
178 public string GetTableName()
179 {
180 return ((TableAttribute) this .GetType().GetCustomAttributes( true )[ 0 ]).TableName;
181 }
182 private object pkValue;
183 public string GetPKey()
184 {
185 Type type = this .GetType();
186 PropertyInfo[] arrPi = type.GetProperties();
187 foreach (PropertyInfo pi in arrPi)
188 {
189 object [] objs = pi.GetCustomAttributes( typeof (PrimaryKeyAttribute), true );
190 if (objs.Length > 0 )
191 {
192 this .pkValue = pi.GetValue( this , null );
193 return (pi.GetCustomAttributes( typeof (FieldAttribute), true )[ 0 ] as FieldAttribute).FieldName;
194 }
195 }
196 return string .Empty;
197 }
198 public object GetPKeyValue()
199 {
200 return pkValue;
201 }
202 private string [] fields;
203 private object [] values;
204
205 public void Initiate()
206 {
207 Type type = this .GetType();
208 PropertyInfo[] arrPi = type.GetProperties();
209 fields = new string [arrPi.Length];
210 values = new object [arrPi.Length];
211
212 int i = 0 ;
213 foreach (PropertyInfo pi in arrPi)
214 {
215 fields[i] = (pi.GetCustomAttributes( typeof (FieldAttribute), true )[ 0 ] as FieldAttribute).FieldName;
216 values[i ++ ] = pi.GetValue( this , null );
217 }
218 }
219
220 public string [] GetFields()
221 {
222 if (fields == null )
223 {
224 Initiate();
225 }
226 return fields;
227
228 }
229 public object [] GetValues()
230 {
231 if (values == null )
232 {
233 Initiate();
234 }
235 return values;
236 }
237
238 public DbParameter[] GetParams()
239 {
240 string [] fields = this .GetFields();
241 object [] values = this .GetValues();
242 DbParameter[] dbparams = new SqlParameter[fields.Length];
243 int i = 0 ;
244 foreach ( string item in fields)
245 {
246 dbparams[i] = new SqlParameter( " : " + item, values[i]);
247 i ++ ;
248 }
249 return dbparams;
250 }
251
252 public string GetFieldByProperty( string propertyName)
253 {
254 PropertyInfo pi = this .GetType().GetProperty(propertyName);
255 return ((FieldAttribute)(pi.GetCustomAttributes( typeof (FieldAttribute), false )[ 0 ])).FieldName;
256 }
257 public object GetValueByProperty( string propertyName)
258 {
259 PropertyInfo pi = this .GetType().GetProperty(propertyName);
260 return pi.GetValue( this , null );
261 }
262 }
263
264
265 public class FieldAttribute : Attribute
266 {
267 public string FieldName { get ; set ; }
268 public FieldAttribute( string fieldName)
269 {
270 this .FieldName = fieldName;
271 }
272 }
273 public class TableAttribute : Attribute
274 {
275 public string TableName { get ; set ; }
276 public TableAttribute( string tbName)
277 {
278 this .TableName = tbName;
279 }
280 }
281 public class PrimaryKeyAttribute : Attribute
282 {
283 public bool IsPK { get ; set ; }
284 public PrimaryKeyAttribute( bool isPK)
285 {
286 this .IsPK = isPK;
287 }
288 }
289 }

转载于:https://www.cnblogs.com/cnbwang/archive/2011/06/06/2073536.html