你的位置:首页 > ASP.net教程

[ASP.net教程]简单的sql server


主要记录工作当中遇到的一些问题和总结的一些经验

客户端请求-->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     #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         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     #endregion

View Code