用戶要求，可以自己輸入其自己亂定義的sql。。無法，隨便弄了個
估計有問題。不過也無法。先用這把
本來把想存儲field類別，可木找到這麼獲取，先留著吧，以後該下。
1 public class MySQLField
2 {
3 private string _name = String.Empty;
4 private string _value = String.Empty;
5 private Type _type = null;
6
7 public string Name
8 {
9 get { return _name; }
10 set { _name = value; }
11 }
12
13 public string Value
14 {
15 get { return _value; }
16 set { _value = value; }
17 }
18
19 public Type FieldType
20 {
21 get { return _type; }
22 set { _type = value; }
23 }
24
25
26 public override string ToString()
27 {
28 return Value;
29 }
30
31 public override int GetHashCode()
32 {
33 return Name.GetHashCode();
34 }
35 }
重寫了下[]和add 不過因為偷懶木繼承IList ，不知道會出問題不
1 public class MySQLRow : System.Collections.Generic.List<MySQLField>
2 {
3 public new MySQLField this[string index]
4 {
5 set
6 {
7 base.Add(value);
8 }
9 get
10 {
11 return this.GetField(index);
12 }
13 }
14
15 private System.Collections.Hashtable _strField = new Hashtable();
16
17
18 public string GetFieldValues (string key)
19 {
20 if (_strField.ContainsKey(key))
21 return (string)_strField[key];
22 else
23 return string.Empty;
24 }
25
26 public MySQLField GetField(string key)
27 {
28 if(_strField.ContainsKey(key))
29 return new MySQLField()
30 {
31 Name = key,
32 Value = GetFieldValues(key)
33 };
34 return null;
35 }
36
37
38
39 public new void Add(MySQLField item)
40 {
41 string key = item.Name;
42 if (_strField.ContainsKey(key))
43 _strField.Remove(key);
44 _strField.Add(key, item.Value);
45
46 if (Contains(item))
47 _strField.Remove (item);
48
49 base.Add(item);
50 }
51
52
53 }
因為前台用nvelocity list[i][j] 這個用不了。只有寫個中間方法用下了。
1 public class MySQLRst : System.Collections.Generic.List<MySQLRow>
2 {
3 private int _rstNums = default(int);
4
5 public int RstNums
6 {
7 set
8 {
9 _rstNums = value;
10 }
11 get
12 {
13 return _rstNums;
14 }
15 }
16 public MySQLField GetRowField(int row ,string key)
17 {
18 if (row > Count)
19 return null;
20 return this[row][key];
21 }
22
23 public MySQLRow GetRow(int row)
24 {
25 if (row > Count)
26 return null;
27 return this[row];
28 }
29 }
方法先掛這把。hibernate的封裝類，我從codeproject下弄下來的，改了下。。當然 你可以用codesmith生成，
自己寫也可以，都一樣了。
1 public class SQLHandle
2 {
3 protected NHibernateISessionManager sessionFactory = null;
4 public SQLHandle()
5 {
6 sessionFactory = NHibernateISessionManager.Instance;
7 }
8
9 public enum EQueryInfo
10 {
11 OK,
12 QUERYER
13 }
14
15 /**//// <summary>
16 /// 執行sql
17 /// </summary>
18 /// <param name="sqlStr"></param>
19 /// <returns></returns>
20 public EQueryInfo Query (string sqlStr)
21 {
22 return this.Query(sqlStr, CommandType.Text);
23 }
24
25 /**//// <summary>
26 /// 根據類型執行不同的 sql
27 /// </summary>
28 /// <param name="sqlStr"></param>
29 /// <param name="type"></param>
30 /// <returns></returns>
31 public EQueryInfo Query (string sqlStr,CommandType type)
32 {
33 ITransaction trans = sessionFactory.GetSession().BeginTransaction ();
34 try
35 {
36 IDbCommand cmd = sessionFactory.GetSession ().Connection.CreateCommand();
37 cmd.CommandText = sqlStr;
38 cmd.CommandType = type;
39 trans.Enlist(cmd);
40 cmd.Prepare();
41 cmd.ExecuteNonQuery();
42
43 trans.Commit ();
44 }
45 catch
46 {
47
48 trans.Rollback ();
49 return EQueryInfo.QUERYER;
50 }
51
52 return EQueryInfo.OK;
53 }
54
55 public MySQLRst GetRst(string sqlStr)
56 {
57 IDbCommand cmd = sessionFactory.GetSession().Connection.CreateCommand();
58 cmd.CommandText = sqlStr;
59 var rst = new MySQLRst();
60 try
61 {
62 IDataReader rs = cmd.ExecuteReader();
63 int rstCount = 0;
64 while (rs.Read())
65 {
66 rstCount++;
67 int fieldCount = rs.FieldCount;
68 var row = new MySQLRow();
69 for (int i = 0; i < fieldCount; i++)
70 {
71 row.Add(new MySQLField()
72 {
73 Name = rs.GetName(i),
74 Value = rs.GetValue(i).ToString()
75 });
76 }
77 rst.Add(row);
78 }
79 rst.RstNums = rstCount;
80 }catch(Exception e)
81 {
82 throw new Exception (e.ToString());
83 }
84 finally
85 {
86 sessionFactory.CloseSession();
87 }
88 return rst;
89 }
90 }
一個簡單測試，好像性能木測試，估計嘿嘿。問題大大
1 [TestFixture]
2 public class CommSQLTest
3 {
4 [Test]
5 public void select測試()
6 {
7 SQLHandle handle = new SQLHandle ();
8 MySQLRst rst = handle.GetRst("SELECT * FROM catalog cg");
9 for (var i = 0; i < rst.Count;i++ )
10 {
11 System.Console.WriteLine("id= {0}--{1}--{2}", rst[i]["id"],rst [i].GetFieldValues("parent_id"),rst.GetRowField(i,"id"));
12 }
13 }
14 [Test]
15 public void query測試()
16 {
17 SQLHandle handle = new SQLHandle();
18 MySQLRst rst = handle.GetRst("SELECT * FROM catalog cg where id = 1");
19 string sql = "update catalog set intro = 'yyy' where id = 1";
20 handle.Query(sql);
21 MySQLRst rst2 = handle.GetRst("SELECT * FROM catalog cg where id = 1");
22 System.Console.WriteLine("{0}-{1}",rst[0]["intro"],rst2[0] ["intro"]);
23 }
24 }
