主要记录工作当中遇到的一些问题和总结的一些经验
客户端请求-->web服务接口-->sql 语句执行(存储在数据库中)-->web服务(客户端通过调用web服务接口)-->返回DataTable或Dataset(sql server)--> 统一的DataTable或Dataset转换成对象-->提交给客户端(
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 try101 {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 try148 {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 #endregionView 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 else102 {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 Name12 {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 Value26 {27 get { return this._value; }28 set { this._value = value; }29 }30 31 private object _object = null;32 33 34 public object Object35 {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 try113 {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 else135 {136 GetProperties(value);137 }138 }139 return list;140 }141 142 /// <summary>143 /// 类型对象生成DataTable144 /// </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 else181 {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 /// 类型对象生成DataTable215 /// </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 #endregionView Code
原标题：简单的sql server
关键词：sql
我知道了 