kms访问数据库的方式(该篇只是作为个人笔记,不具有任何公共参考意图)
项目类型:winform
语言:C#
服务程序:webservice(webservice我本人也不了解,在下一章中会总结一下对它的概念的简单理解)
情景描述:简单创建一个窗体,实现学生信息(姓名、性别)的新增和查询
一、保存按钮下的事件:
1 private void btnSave_Click(object sender, EventArgs e) 2 { 3 try 4 { 5 List<SqlPara> list = new List<SqlPara>(); 6 list.Add(new SqlPara("stuName",txtName.Text)); 7 list.Add(new SqlPara("stuSex",txtSex.Text)); 8 SqlParasEntity sps = new SqlParasEntity(OperType.Execute, "AddStudentInfo", list); 9 int result = SqlHelper.ExecuteNoQuery(sps); 10 if(result > 0) 11 { 12 MessageBox.Show("操作成功!"); 13 return; 14 } 15 } 16 catch(Exception ex) 17 { 18 MessageBox.Show(ex.ToString()); 19 return; 20 } 21 }
二、创建方法中使用到的类(使用一个类库统一管理这些类型)
1 //参数对象 2 public class SqlPara 3 { 4 public string ParaName 5 { get; set; } 6 public object ParaValue 7 { get; set; } 8 public SqlPara(string n, object o) 9 { 10 this.ParaName = n; 11 this.ParaValue = o; 12 } 13 } 14 15 //表示数据库操作类型 16 public enum OperType 17 { 18 Query = 1,//查询 19 20 Execute = 2//执行 21 } 22 23 //实例化对象(构造函数的重载,实现不同数据库操作的对象实例化) 24 public class SqlParasEntity 25 { 26 public OperType OperType 27 { get; set; } 28 public string procedureName 29 { get; set; } 30 public List<SqlPara> ParaList 31 { get; set; } 32 public SqlParasEntity(OperType type, string proName, List<SqlPara> list) 33 { 34 OperType = type; 35 procedureName = proName; 36 ParaList = list; 37 } 38 public SqlParasEntity(OperType type,string proName) 39 { 40 OperType = type; 41 procedureName = proName; 42 } 43 }
三、第一大点中第九行的方法:int result = SqlHelper.ExecuteNoQuery(sps);
1 namespace DAL 2 { 3 public class SqlHelper 4 { 5 private static int ver = 1; 6 public static int ExecuteNoQuery(SqlParasEntity ent) 7 { 8 int flag = ExecuteNoQuery(ent, ver); 9 return flag; 10 } 11 private static int ExecuteNoQuery(SqlParasEntity ent, int ver) 12 { 13 DataSet ds = new DataSet(); 14 DataTable dt = new DataTable(ent.procedureName); 15 dt.Columns.Add("pname"); 16 dt.Columns.Add("pvalue"); 17 ds.Tables.Add(dt); 18 if (ent.ParaList == null) 19 { 20 List<SqlPara> list = new List<SqlPara>(); 21 ent.ParaList = list; 22 } 23 foreach(var item in ent.ParaList) 24 { 25 if (item.ParaValue.GetType() == typeof(DataTable)) 26 { 27 DataTable table = (item.ParaValue as DataTable).Copy(); 28 table.TableName = item.ParaName; 29 ds.Tables.Add(table); 30 } 31 else 32 { 33 dt.Rows.Add(item.ParaName,item.ParaValue); 34 } 35 } 36 SqlResult result = new SqlResult(); 37 JsonSerializerSettings settings = new JsonSerializerSettings(); 38 settings.DateFormatString = "yyyy-MM-dd HH:mm:ss"; 39 SqlParasTable data = new SqlParasTable(ent.OperType,ds); 40 string json = JsonConvert.SerializeObject(data,settings); 41 result = HttpHelper.HttpPost(string.Format("ver={0}&json={1}",ver,json)); 42 if (result.State == 1) 43 { 44 int count = 0; 45 if (StringHelper.IsNumberId(result.Result)) 46 { 47 count = Convert.ToInt32(result.Result);//这个为什么这样做 48 } 49 return count; 50 } 51 else 52 { 53 throw new Exception("返回信息:\r\n" + result.Result.Replace("数据库访问异常:","").Replace("错误信息","").Trim()); 54 } 55 } 56 } 57 }
四、第三大点中41行代码方法:result = HttpHelper.HttpPost(string.Format("ver={0}&json={1}",ver,json));
1、先创建使用到的类型,三大点中36,39行
1 public class SqlResult 2 { 3 /// <summary> 4 /// sql执行返回状态:1成功,2失败 5 /// </summary> 6 public int State 7 { get; set; } 8 9 /// <summary> 10 /// 如果state是1,则返回结果集(DataSet);State=0,返回错误信息 11 /// </summary> 12 public string Result 13 { get; set; } 14 } 15 16 17 /// <summary> 18 /// 数据库操作提交参数 19 /// </summary> 20 public class SqlParasTable 21 { 22 public OperType OperType 23 { get; set; } 24 25 /// <summary> 26 /// 参数集合,表类型 27 /// <para>第一个 table 是简单的数据类型,键值类型</para> 28 /// <para>第二 table 放表类型的参数(例如批量导入数据时)</para> 29 /// </summary> 30 public DataSet ParasDataSet 31 { get; set; } 32 public SqlParasTable(OperType type,DataSet ds) 33 { 34 OperType = type; 35 ParasDataSet = ds; 36 } 37 }
2、三大点中41行方法:
1 namespace DAL 2 { 3 class HttpHelper 4 { 5 public static string urlPage = "kms1.ashx"; 6 // static string domain = "http://192.168.2.139:8090/"; 7 static string domain = "http://localhost:28383/"; 8 public static SqlResult HttpPost(string data) 9 { 10 string url = domain + urlPage; 11 SqlResult result = new SqlResult(); 12 try 13 { 14 string json = ""; 15 HttpWebRequest req = (HttpWebRequest)WebRequest.Create(url); 16 req.Proxy = null; 17 req.Method = "post"; 18 req.ContentType = "application/x-www-form-urlencoded"; 19 req.Timeout = 600000; 20 21 byte[] btbody = Encoding.UTF8.GetBytes(data); 22 req.ContentLength = btbody.Length; 23 using (Stream st = req.GetRequestStream()) 24 { 25 st.Write(btbody, 0, btbody.Length); 26 st.Close(); 27 st.Dispose(); 28 } 29 WebResponse wr = req.GetResponse(); 30 using (StreamReader sr = new StreamReader(wr.GetResponseStream())) 31 { 32 json = sr.ReadToEnd(); 33 sr.Close(); 34 sr.Dispose(); 35 } 36 wr.Close(); 37 result = JsonConvert.DeserializeObject<SqlResult>(json); 38 } 39 catch (WebException ex) 40 { 41 result.State = 0; 42 result.Result = "远程访问错误,\r\n" + ex.Message; 43 } 44 catch (Exception ex) 45 { 46 result.State = 0; 47 result.Result = ex.Message; 48 } 49 return result; 50 } 51 } 52 }
章结尾:上面的数据库访问失败了。公司项目代码是这样的逻辑,但我没有使用过webservice服务,想自己搭建然后访问,但是没有成功。
上面的内容供自己参考一下数据传输的设计理念,后面要掌握一下webservice服务,实现数据访问
查询的操作方式和上面基本一致,将数据库放回结果转换为自己想要的数据类型即可(DataSet)