簡單的sql server->bs或cs數據交互模式，server-bs
主要記錄工作當中遇到的一些問題和總結的一些經驗
客戶端請求-->web服務接口-->sql 語句執行(存儲在數據庫中)-->web服務(客戶端通過調用web服務接口)-->返回DataTable或Dataset(sql server)--> 統一的DataTable或Dataset轉換成對象-->提交給客戶端(xml、json等等其他的)
1、首先通過sql語句返回結果，返回的結果一般都以Dataset的形式和DataTable的形式返回。
2、統一的DataTable或Dataset轉換成對象
![]()
1 #region 寫對象信息
2
3 /// <summary>
4 ///
5 /// </summary>
6 /// <typeparam name="T"></typeparam>
7 /// <param name="tableName"></param>
8 /// <returns></returns>
9 public T WriteTObjectInfo<T>(string tableName, DataRow dr, string[] exceptArray)
10 {
11 try
12 {
13 if (this.Status == 0)
14 {
15 throw new Exception(this.Msg);
16 }
17
18 T item = Activator.CreateInstance<T>();
19
20 List<Parameter> listParameter = GetProperties<T>(item, exceptArray);
21
22 foreach (Parameter p in listParameter)
23 {
24 foreach (DataColumn dc in this.dsResult.Tables[tableName].Columns)
25 {
26 if (dc.ColumnName == p.Name)
27 {
28 Type type = item.GetType();
29
30 MethodInfo method = type.GetMethod("SetAttributeValue");
31
32 method.Invoke(item, new object[] { p.Name, dr[p.Name].ToString().Trim() });
33 }
34 }
35 }
36
37 return item;
38 }
39 catch (Exception ex)
40 {
41 throw new Exception("寫" + Activator.CreateInstance<T>().ToString() + "信息發生錯誤，錯誤原因：" + ex.Message);
42 }
43 }
44
45 /// <summary>
46 ///
47 /// </summary>
48 /// <typeparam name="T"></typeparam>
49 /// <param name="tableName"></param>
50 /// <returns></returns>
51 public T WriteTObjectInfo<T>(string tableName)
52 {
53 try
54 {
55 if (this.Status == 0)
56 {
57 throw new Exception(this.Msg);
58 }
59
60 T item = Activator.CreateInstance<T>();
61
62 if (this.dsResult.Tables.Contains(tableName))
63 {
64 DataRow dr = this.dsResult.Tables[tableName].Rows[0];
65
66 List<Parameter> listParameter = GetProperties<T>(item);
67
68 foreach (Parameter p in listParameter)
69 {
70 foreach (DataColumn dc in this.dsResult.Tables[tableName].Columns)
71 {
72 if (dc.ColumnName == p.Name)
73 {
74 Type type = item.GetType();
75
76 MethodInfo method = type.GetMethod("SetAttributeValue");
77
78 method.Invoke(item, new object[] { p.Name, dr[p.Name].ToString().Trim() });
79 }
80 }
81 }
82 }
83
84 return item;
85 }
86 catch (Exception ex)
87 {
88 throw new Exception("寫" + Activator.CreateInstance<T>() + "信息發生錯誤，錯誤原因：" + ex.Message);
89 }
90 }
91
92 /// <summary>
93 ///
94 /// </summary>
95 /// <typeparam name="T"></typeparam>
96 /// <param name="tableName"></param>
97 /// <returns></returns>
98 public T WriteTObjectInfo<T>(string tableName, string[] exceptArray)
99 {
100 try
101 {
102 if (this.Status == 0)
103 {
104 throw new Exception(this.Msg);
105 }
106
107 T item = Activator.CreateInstance<T>();
108
109 if (this.dsResult.Tables.Contains(tableName))
110 {
111 DataRow dr = this.dsResult.Tables[tableName].Rows[0];
112
113 List<Parameter> listParameter = GetProperties<T>(item, exceptArray);
114
115 foreach (Parameter p in listParameter)
116 {
117 foreach (DataColumn dc in this.dsResult.Tables[tableName].Columns)
118 {
119 if (dc.ColumnName == p.Name)
120 {
121 Type type = item.GetType();
122
123 MethodInfo method = type.GetMethod("SetAttributeValue");
124
125 method.Invoke(item, new object[] { p.Name, dr[p.Name].ToString().Trim() });
126 }
127 }
128 }
129 }
130
131 return item;
132 }
133 catch (Exception ex)
134 {
135 throw new Exception("寫" + Activator.CreateInstance<T>() + "信息發生錯誤，錯誤原因：" + ex.Message);
136 }
137 }
138
139 /// <summary>
140 ///
141 /// </summary>
142 /// <typeparam name="T"></typeparam>
143 /// <param name="tableName"></param>
144 /// <returns></returns>
145 public List<T> WriteTObjectInfoList<T>(string tableName, string[] exceptArray)
146 {
147 try
148 {
149 if (this.Status == 0)
150 {
151 throw new Exception(this.Msg);
152 }
153
154 List<T> list = new List<T>();
155
156 if (this.dsResult.Tables.Contains(tableName))
157 {
158 foreach (DataRow dr in this.dsResult.Tables[tableName].Rows)
159 {
160 T item = WriteTObjectInfo<T>(tableName, dr, exceptArray);
161
162 list.Add(item);
163 }
164 }
165
166 return list;
167 }
168 catch (Exception ex)
169 {
170 throw new Exception("寫" + Activator.CreateInstance<T>().ToString() + "信息發生錯誤，錯誤原因：" + ex.Message);
171 }
172 }
173
174 /// <summary>
175 ///
176 /// </summary>
177 /// <typeparam name="T"></typeparam>
178 /// <param name="tableName"></param>
179 /// <returns></returns>
180 public List<T> WriteTObjectInfoList<T>(string tableName)
181 {
182 return WriteTObjectInfoList<T>(tableName, new string[] { });
183 }
184
185 #endregion
View Code
以上代碼統一用泛型實現
比較實用的獲取屬性的代碼
![]()
1 /// <summary>
2 /// 獲取對象的屬性名稱、值和描述
3 /// </summary>
4 /// <typeparam name="T">對象的類型</typeparam>
5 /// <param name="t">對象</param>
6 /// <returns>對象列表</returns>
7 public List<Parameter> GetProperties<T>(T t)
8 {
9 List<Parameter> list = new List<Parameter>();
10
11 if (t == null)
12 {
13 return list;
14 }
15 PropertyInfo[] properties = t.GetType().GetProperties(BindingFlags.Instance | BindingFlags.Public);
16
17 if (properties.Length <= 0)
18 {
19 return list;
20 }
21 foreach (PropertyInfo item in properties)
22 {
23 string name = item.Name; //名稱
24 object value = item.GetValue(t, null); //值
25
26 string des = string.Empty;
27
28 try
29 {
30 des = ((DescriptionAttribute)Attribute.GetCustomAttribute(item, typeof(DescriptionAttribute))).Description;// 屬性值
31 }
32 catch { }
33
34 if (item.PropertyType.IsValueType || item.PropertyType.Name.StartsWith("String"))
35 {
36 Parameter parameter = new Parameter();
37
38 parameter.Name = name;
39 parameter.Value = value == null ? "" : value.ToString();
40 parameter.Object = des;
41
42 list.Add(parameter);
43 }
44 else
45 {
46 GetProperties(value);
47 }
48 }
49 return list;
50 }
51
52 /// <summary>
53 ///
54 /// </summary>
55 /// <typeparam name="T"></typeparam>
56 /// <param name="t"></param>
57 /// <param name="exceptArray"></param>
58 /// <returns></returns>
59 public List<Parameter> GetProperties<T>(T t, string[] exceptArray)
60 {
61 List<Parameter> list = new List<Parameter>();
62
63 if (t == null)
64 {
65 return list;
66 }
67 PropertyInfo[] properties = t.GetType().GetProperties(BindingFlags.Instance | BindingFlags.Public);
68
69 if (properties.Length <= 0)
70 {
71 return list;
72 }
73 foreach (PropertyInfo item in properties)
74 {
75 string name = item.Name; //名稱
76 object value = item.GetValue(t, null); //值
77 string des = string.Empty;
78
79 try
80 {
81 des = ((DescriptionAttribute)Attribute.GetCustomAttribute(item, typeof(DescriptionAttribute))).Description;// 屬性值
82 }
83 catch (Exception ex)
84 {
85 des = string.Empty;
86 }
87
88 if (item.PropertyType.IsValueType || item.PropertyType.Name.StartsWith("String"))
89 {
90 if (!((IList)exceptArray).Contains(name))
91 {
92 Parameter parameter = new Parameter();
93
94 parameter.Name = name;
95 parameter.Value = value == null ? "" : value.ToString();
96 parameter.Object = des;
97
98 list.Add(parameter);
99 }
100 }
101 else
102 {
103 GetProperties(value);
104 }
105 }
106 return list;
107 }
View Code
基礎的Parameter類
![]()
1 public class Parameter
2 {
3 /// <summary>
4 /// 名稱
5 /// </summary>
6 private string _name = string.Empty;
7
8 /// <summary>
9 /// 獲取或設置名稱
10 /// </summary>
11 public string Name
12 {
13 get { return this._name; }
14 set { this._name = value; }
15 }
16
17 /// <summary>
18 /// 值
19 /// </summary>
20 private string _value = string.Empty;
21
22 /// <summary>
23 /// 獲取或設置值
24 /// </summary>
25 public string Value
26 {
27 get { return this._value; }
28 set { this._value = value; }
29 }
30
31 private object _object = null;
32
33
34 public object Object
35 {
36 get { return this._object; }
37 set { this._object = value; }
38 }
39
40 /// <summary>
41 /// 構造函數
42 /// </summary>
43 /// <param name="name">名稱</param>
44 /// <param name="value">值</param>
45 public Parameter(string name, string value)
46 {
47 this.Name = name;
48 this.Value = value;
49 }
50
51 public Parameter(string name, object obj)
52 {
53 this.Name = name;
54 this.Object = obj;
55 }
56
57 /// <summary>
58 /// 構造函數
59 /// </summary>
60 public Parameter()
61 {
62
63 }
64
65 /// <summary>
66 ///
67 /// </summary>
68 /// <returns></returns>
69 public override string ToString()
70 {
71 return string.Format(@"名稱(Name)：{0}，值(Value)：{1}，對象(Object)：{2}", this.Name, this.Value, this.Object);
72 }
73 }
View Code
對象例子(這個對象例子的類，這個類其實和上面的DataTable和Dataset是對應的，通過以上的操作可以把DataTable或Dataset轉換成具體的對象)，因為這個類是比較統一的可以用代碼生成工具可以直接生成
![]()
1 public class Log
2 {
3 #region 屬性
4
5 [Description("數據日志編號")]
6 public string LogID { get; set; }
7
8 [Description("設備編號")]
9 public string DeviceID { get; set; }
10
11 [Description("設備名稱")]
12 public string DeviceName { get; set; }
13
14 [Description("質控項目編號")]
15 public string QCItemDicID { get; set; }
16
17 [Description("質控項目中文名稱")]
18 public string CNName { get; set; }
19
20 [Description("質控項目英文名稱")]
21 public string ENName { get; set; }
22
23 [Description("質控項目名稱簡拼碼")]
24 public string JPM { get; set; }
25
26 [Description("質控項目名稱簡拼碼")]
27 public string NameAB { get; set; }
28
29 [Description("質控項目單位")]
30 public string Unit { get; set; }
31
32 [Description("設備質控編號")]
33 public string Dev_QC_No { get; set; }
34
35 [Description("設備質控序號")]
36 public string Dev_QC_SequenceNo { get; set; }
37
38 [Description("設備質控名稱")]
39 public string Dev_QC_Name { get; set; }
40
41 [Description("質控時間")]
42 public string QCTime { get; set; }
43
44 [Description("值類型")]
45 public string TextType { get; set; }
46
47 [Description("數值")]
48 public string ItemValue { get; set; }
49
50 [Description("創建時間")]
51 public string CreateTime { get; set; }
52
53 [Description("創建人")]
54 public string CreateUser { get; set; }
55
56 [Description("序號(通道號)")]
57 public string Serial { get; set; }
58
59 #endregion
60
61 /// <summary>
62 /// 設置屬性值
63 /// </summary>
64 /// <param name="name">名稱</param>
65 /// <param name="value">值</param>
66 public void SetAttributeValue(string name, string value)
67 {
68 switch (name)
69 {
70 case "LogID"://數據日志編號
71 this.LogID = value;
72 break;
73 case "DeviceID"://設備編號
74 this.DeviceID = value;
75 break;
76 case "DeviceName"://設備名稱
77 this.DeviceName = value;
78 break;
79 case "QCItemDicID"://質控項目編號
80 this.QCItemDicID = value;
81 break;
82 case "CNName"://質控項目中文名稱
83 this.CNName = value;
84 break;
85 case "ENName"://質控項目英文名稱
86 this.ENName = value;
87 break;
88 case "JPM"://質控項目名稱簡拼碼
89 this.JPM = value;
90 break;
91 case "NameAB"://質控項目名稱簡拼碼
92 this.NameAB = value;
93 break;
94 case "Unit"://質控項目單位
95 this.Unit = value;
96 break;
97 case "Dev_QC_No"://設備質控編號
98 this.Dev_QC_No = value;
99 break;
100 case "Dev_QC_SequenceNo"://設備質控序號
101 this.Dev_QC_SequenceNo = value;
102 break;
103 case "Dev_QC_Name"://設備質控名稱
104 this.Dev_QC_Name = value;
105 break;
106 case "QCTime"://質控時間
107 this.QCTime = value;
108 break;
109 case "TextType"://值類型
110 this.TextType = value;
111 break;
112 case "ItemValue"://數值
113 this.ItemValue = value;
114 break;
115 case "CreateTime"://創建時間
116 this.CreateTime = value;
117 break;
118 case "CreateUser"://創建人
119 this.CreateUser = value;
120 break;
121 case "Serial"://序號(通道號)
122 this.Serial = value;
123 break;
124 default:
125 break;
126 }
127 }
128 }
View Code
另外也可以把對象轉換成DataTable或Dataset 根據具體使用的情況進行具體的轉換
![]()
1 #region 獲取對象和對象轉換成DataTable
2
3 /// <summary>
4 /// 返回數據列
5 /// </summary>
6 /// <param name="columnName"></param>
7 /// <param name="caption"></param>
8 /// <returns></returns>
9 public static DataColumn AddDataColumn(string columnName, string caption)
10 {
11 DataColumn dc = new DataColumn();
12
13 dc.ColumnName = columnName;
14 dc.Caption = caption;
15
16 return dc;
17 }
18
19 /// <summary>
20 /// 獲取表格的數據列
21 /// </summary>
22 /// <param name="name"></param>
23 /// <param name="caption"></param>
24 /// <returns></returns>
25 public static DataColumn GetColumn(string name, string caption)
26 {
27 DataColumn dc = new DataColumn();
28
29 dc.ColumnName = name;
30 dc.Caption = caption;
31
32 return dc;
33 }
34
35 /// <summary>
36 /// 獲取對象的屬性名稱、值和描述
37 /// </summary>
38 /// <typeparam name="T">對象的類型</typeparam>
39 /// <param name="t">對象</param>
40 /// <returns>對象列表</returns>
41 public static List<Parameter> GetProperties<T>(T t)
42 {
43 List<Parameter> list = new List<Parameter>();
44
45 if (t == null)
46 {
47 return list;
48 }
49 System.Reflection.PropertyInfo[] properties = t.GetType().GetProperties(System.Reflection.BindingFlags.Instance | System.Reflection.BindingFlags.Public);
50
51 if (properties.Length <= 0)
52 {
53 return list;
54 }
55 foreach (System.Reflection.PropertyInfo item in properties)
56 {
57 string name = item.Name; //名稱
58 object value = item.GetValue(t, null); //值
59 string des = string.Empty;
60
61 try
62 {
63 des = ((DescriptionAttribute)Attribute.GetCustomAttribute(item, typeof(DescriptionAttribute))).Description;// 屬性值
64 }
65 catch { }
66
67 if (item.PropertyType.IsValueType || item.PropertyType.Name.StartsWith("String"))
68 {
69 Parameter parameter = new Parameter();
70
71 parameter.Name = name;
72 parameter.Value = value == null ? string.Empty : value.ToString();
73 parameter.Object = des;
74
75 list.Add(parameter);
76 }
77 else
78 {
79 GetProperties(value);
80 }
81 }
82 return list;
83 }
84
85 /// <summary>
86 ///
87 /// </summary>
88 /// <typeparam name="T"></typeparam>
89 /// <param name="t"></param>
90 /// <param name="exceptArray"></param>
91 /// <returns></returns>
92 public static List<Parameter> GetProperties<T>(T t, string[] exceptArray)
93 {
94 List<Parameter> list = new List<Parameter>();
95
96 if (t == null)
97 {
98 return list;
99 }
100 PropertyInfo[] properties = t.GetType().GetProperties(BindingFlags.Instance | BindingFlags.Public);
101
102 if (properties.Length <= 0)
103 {
104 return list;
105 }
106 foreach (PropertyInfo item in properties)
107 {
108 string name = item.Name; //名稱
109 object value = item.GetValue(t, null); //值
110 string des = string.Empty;
111
112 try
113 {
114 des = ((DescriptionAttribute)Attribute.GetCustomAttribute(item, typeof(DescriptionAttribute))).Description;// 屬性值
115 }
116 catch (Exception ex)
117 {
118 des = string.Empty;
119 }
120
121 if (item.PropertyType.IsValueType || item.PropertyType.Name.StartsWith("String"))
122 {
123 if (!((IList)exceptArray).Contains(name))
124 {
125 Parameter parameter = new Parameter();
126
127 parameter.Name = name;
128 parameter.Value = value == null ? "" : value.ToString();
129 parameter.Object = des;
130
131 list.Add(parameter);
132 }
133 }
134 else
135 {
136 GetProperties(value);
137 }
138 }
139 return list;
140 }
141
142 /// <summary>
143 /// 類型對象生成DataTable
144 /// </summary>
145 /// <typeparam name="T"></typeparam>
146 /// <param name="t"></param>
147 /// <returns></returns>
148 public static DataTable TToDataTable<T>(T obj, List<T> listT)
149 {
150 DataTable dt = new DataTable();
151
152 int flag = 0;
153
154 if (listT != null)
155 {
156 foreach (T t in listT)
157 {
158 List<Parameter> listProperty = GetProperties<T>(t);
159
160 if (flag <= 0)
161 {
162 foreach (Parameter parameter in listProperty)
163 {
164 flag++;
165
166 dt.Columns.Add(GetColumn(parameter.Name, parameter.Object.ToString()));
167 }
168 }
169
170 DataRow dr = dt.NewRow();
171
172 foreach (Parameter parameter in listProperty)
173 {
174 dr[parameter.Name] = parameter.Value;
175 }
176
177 dt.Rows.Add(dr);
178 }
179 }
180 else
181 {
182 List<Parameter> listProperty = GetProperties<T>(obj);
183
184 foreach (Parameter parameter in listProperty)
185 {
186 dt.Columns.Add(GetColumn(parameter.Name, parameter.Object.ToString()));
187 }
188
189 DataRow dr = dt.NewRow();
190
191 foreach (Parameter parameter in listProperty)
192 {
193 dr[parameter.Name] = parameter.Value;
194 }
195
196 dt.Rows.Add(dr);
197 }
198
199 return dt;
200 }
201
202 /// <summary>
203 ///
204 /// </summary>
205 /// <typeparam name="T"></typeparam>
206 /// <param name="obj"></param>
207 /// <returns></returns>
208 public static DataTable TToDataTable<T>(T obj)
209 {
210 return TToDataTable<T>(obj, null);
211 }
212
213 /// <summary>
214 /// 類型對象生成DataTable
215 /// </summary>
216 /// <typeparam name="T"></typeparam>
217 /// <param name="listT"></param>
218 /// <returns></returns>
219 public static DataTable TToDataTable<T>(List<T> listT)
220 {
221 return TToDataTable<T>(default(T), listT);
222 }
223
224 /// <summary>
225 /// 生成參數
226 /// </summary>
227 /// <param name="name"></param>
228 /// <param name="value"></param>
229 /// <returns></returns>
230 public static Parameter GetParameter(string name, string value)
231 {
232 Parameter parameter = new Parameter();
233
234 parameter.Name = name;
235 parameter.Value = value;
236
237 return parameter;
238 }
View Code
要是客戶端為bs架構，用一下代碼進行發送
![]()
1 /// <summary>
2 ///
3 /// </summary>
4 /// <typeparam name="T"></typeparam>
5 /// <param name="t"></param>
6 public void SendDataObject<T>(T t)
7 {
8 string json = Newtonsoft.Json.JsonConvert.SerializeObject(t);
9
10 SendDataByJson(json);
11 }
View Code
具體的後端向前端發送的代碼可以參考如下：
![]()
1 #region 公共方法
2 /// <summary>
3 /// 向客戶端發送數據
4 /// </summary>
5 /// <param name="contentEncoding">字符編碼</param>
6 /// <param name="contentType">輸出流的MIME類型</param>
7 /// <param name="content">輸出的內容</param>
8 public void SendData(Encoding contentEncoding, string contentType, string content)
9 {
10 Response.Clear();
11 Response.ContentEncoding = contentEncoding;
12 Response.ContentType = contentType;
13 Response.Write(content);
14 Response.Flush();
15 Response.End();
16 }
17 /// <summary>
18 /// 向客戶端發送數據
19 /// </summary>
20 /// <param name="content">輸出的內容</param>
21 public void SendData(string content)
22 {
23 SendData(Encoding.UTF8, "application/json", content);
24 }
25
26 public void SendDataFile(string filePath, string fileName)
27 {
28 System.IO.FileStream fs = new System.IO.FileStream(filePath, System.IO.FileMode.Open, System.IO.FileAccess.Read);
29
30 byte[] b = new Byte[fs.Length];
31 fs.Read(b, 0, b.Length);
32 fs.Flush();
33 fs.Close();
34
35 Response.Clear();
36 Response.ClearHeaders();
37 Response.Clear();
38 Response.ClearHeaders();
39 Response.Buffer = false;
40 Response.ContentType = "application/octet-stream";
41 Response.AppendHeader("Content-Disposition", "attachment;filename=" + HttpUtility.UrlEncode(fileName, System.Text.Encoding.UTF8));
42 Response.AppendHeader("Content-Length", b.Length.ToString());
43 fs.Close();
44 fs.Close();
45 if (b.Length > 0)
46 {
47 Response.OutputStream.Write(b, 0, b.Length);
48 }
49 Response.Flush();
50 Response.End();
51 }
52 /// <summary>
53 /// 通過json的形式發送文本
54 /// </summary>
55 /// <param name="content">要發送的內容</param>
56 public void SendDataByJson(string content)
57 {
58 SendData(Encoding.UTF8, "application/json", content);
59 }
60 /// <summary>
61 /// 向客戶端發送數據
62 /// </summary>
63 /// <param name="content">輸出的內容</param>
64 public void SendData(string contentType, string content)
65 {
66 SendData(Encoding.UTF8, contentType, content);
67 }
68 /// <summary>
69 /// 通過文本的形式發送文件
70 /// </summary>
71 /// <param name="content">要發送的內容</param>
72 public void SendDataByText(string content)
73 {
74 SendData(Encoding.UTF8, "text/plain", content);
75 }
76 /// <summary>
77 /// 處理錯誤消息
78 /// </summary>
79 /// <param name="message">要處理的消息</param>
80 /// <returns>處理之後的消息</returns>
81 public string DealErrorMsg(string message)
82 {
83 return message.Replace((char)13, (char)0).Replace((char)10, (char)0).Replace("\"", "'").Replace("\0", "");
84 }
85
86 #endregion
View Code
