csharp:Nhibernate Procedure with CreateSQLQuery and GetNamedQuery

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
<?xml version="1.0" encoding="utf-8"?>
<hibernate-mapping assembly="Domain" namespace="Domain" xmlns="urn:nhibernate-mapping-2.2">
  <class name="DuCardType" table="DuCardType" lazy="true" >
    <id name="CardTypeId" column="CardTypeId">
      <generator class="identity" />
    </id>
    <property name="CardTypeName">
      <column name="CardTypeName" sql-type="nvarchar" not-null="true" />
    </property>
    <property name="CardTypeColor">
      <column name="CardTypeColor" sql-type="nvarchar" not-null="true" />
    </property>
 
  <sql-insert name="InsertDuCardType">exec proc_Insert_DuCardType ?,?,?</sql-insert>
  <!--<sql-insert name="SqlInsertDuCardType">INSERT INTO DuCardType (CardTypeName, CardTypeColor) VALUES (?,?,?)</sql-insert>-->
  <!--<sql-insert>
    　　<sql-update>
      　　<sql-delete>-->
  </class>
</hibernate-mapping>

　　

 

复制代码
  ///<summary>
        /// 追加记录返回值
        ///</summary>
        ///<param name="DuCardType">输入参数:DuCardTypeInfo</param>
        ///<param name="CardTypeId">返回参数:CardTypeId</param>
        ///<returns>返回是否添加的个数</returns>
        public int InsertDuCardTypeOutput(DuCardType cardType, out int cardTypeId)
        {

            int sid = 1;
            NHibernate.ISession session = this.SessionFactory.GetCurrentSession();
            ITransaction tran = session.BeginTransaction();
            try
            {
                session.Save(cardType);
                cardTypeId = cardType.CardTypeId;
                tran.Commit();
            }
            catch (MappingException ex)
            {
                sid = 0;
                tran.Rollback();
                throw ex;

            }

            return sid;

            //return dal.InsertDuCardTypeOutput(duCardType,out cardTypeId);
        }
复制代码

 

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
///<summary>
      ///修改记录
      ///</summary>
      ///<param name="DuCardType">输入参数:DuCardTypeInfo</param>
      ///<returns>返回修改的多少记录数</returns>
      public int UpdateDuCardType(DuCardType duCardType)
      {
          int sid = 1;
          NHibernate.ISession session = this.SessionFactory.GetCurrentSession();
          ITransaction tran = session.BeginTransaction();
          try
          {
              session.Update(duCardType);
              tran.Commit();
          }
          catch (Exception ex)
          {
              sid = 0;
              tran.Rollback();
              throw ex;
 
          }
 
          return sid;
          //return dal.UpdateDuCardType(duCardType);
      }

　　

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
/// <summary>
       /// 删除
       /// </summary>
       /// <param name="customer"></param>
       /// <returns></returns>
       public int Dell(DuCardType cardType)
       {
           int sid = 1;
           NHibernate.ISession session = this.SessionFactory.GetCurrentSession();
           ITransaction tran = session.BeginTransaction();
           try
           {
               session.Delete(cardType);
               tran.Commit();
           }
           catch (Exception ex)
           {
               sid = 0;
               tran.Rollback();
               throw ex;
 
           }
           return sid;
       }

　　

 

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
///<summary>
       /// 删除记录
       ///</summary>
       ///<param name="cardTypeId">输入参数:CardTypeId</param>
       ///<returns>返回删除记录条数</returns>
       public bool DeleteDuCardType(int cardTypeId)
       {
           bool isok = false;
           try
           {
               IDbDataParameter[] par = new SqlParameter[]
               {                  
                   new SqlParameter(),
               };
               par[0].ParameterName = "CardTypeId";
               par[0].Value = cardTypeId;
               isok = DBHelper.ExecuteNonQuery(SessionFactory, CommandType.StoredProcedure, "proc_Delete_DuCardType");
           }
           catch (NotImplementedException ex)
           {
               ex.Message.ToString();
           }
 
           return isok;
 
 
           //return dal.DeleteDuCardType(cardTypeId);
       }
 
       /// <summary>
       /// 删除
       /// </summary>
       /// <param name="customer"></param>
       /// <returns></returns>
       public int Dell(DuCardType cardType)
       {
           int sid = 1;
           NHibernate.ISession session = this.SessionFactory.GetCurrentSession();
           ITransaction tran = session.BeginTransaction();
           try
           {
               session.Delete(cardType);
               tran.Commit();
           }
           catch (Exception ex)
           {
               sid = 0;
               tran.Rollback();
               throw ex;
 
           }
           return sid;
       }
 
       ///<summary>
       /// 删除多条记录
       ///</summary>
       ///<param name="cardTypeId">输入参数:CardTypeId</param>
       ///<returns>返回删除多少记录</returns>
       public bool DeleteDuCardTypeId(string cardTypeId)
       {
           bool isok = false;          
           try
           {
               IDbDataParameter[] par = new SqlParameter[]
               {                  
                   new SqlParameter(),
               };
               par[0].ParameterName = "CardTypeId";
               par[0].Value = cardTypeId;
               isok = DBHelper.ExecuteNonQuery(SessionFactory, CommandType.StoredProcedure, "proc_Delete_DuCardTypeId");
           }
           catch (NotImplementedException ex)
           {
               ex.Message.ToString();
           }
 
           return isok;
 
           //return dal.DeleteDuCardTypeId(cardTypeId);
       }

　　

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
/// <summary>
       ///
       /// </summary>
       /// <param name="id"></param>
       /// <returns></returns>
       public IList<DuCardType> CardTypeSql(int id)
       {
 
           NHibernate.ISession session = this.SessionFactory.GetCurrentSession();
           IList<DuCardType> card = new List<DuCardType>();
           if (!object.Equals(session, null))
           {
               //写法1
               //return _session.CreateQuery("from DuCardType c where c.CardTypeName='" + firstname + "'")
               //    .List<DuCardType>();
 
               //写法2:位置型参数
               //return _session.CreateQuery("from DuCardType c where c.CardTypeName=?")
               //    .SetString(0, firstname)
               //    .List<DuCardType>();
               //写法4.
               //var sql = "select * from DuCardType";
               //var query = session.CreateSQLQuery(sql)
               //.AddScalar("CardTypeId", NHibernateUtil.Int32)
               //.AddScalar("CardTypeName", NHibernateUtil.String)
               //.AddScalar("CardTypeColor", NHibernateUtil.String)
               //.SetResultTransformer(Transformers.AliasToBean<DuCardType>());
               //result = query.List<DuCardType>(); 
 
               //写法3:命名型参数(推荐)
 
               var query = session.CreateSQLQuery("Select * FROM DuCardType where CardTypeId=:CardTypeId")
                   .SetInt32("CardTypeId", id)
                   .SetResultTransformer(Transformers.AliasToBean(typeof(DuCardType)));
               card = query.List<DuCardType>();
           }
           return card;
 
       }
 
       /// <summary>
       /// 存储过程
       /// </summary>
       /// <param name="id"></param>
       /// <returns></returns>
       public IList<DuCardType> CardTypeProcedures(int id)
       {
           //读到数据
           IList<DuCardType> li = null;
           NHibernate.ISession session = this.SessionFactory.GetCurrentSession();
           string sql = @"exec proc_Select_DuCardType :CardTypeId";
           IQuery query = session.CreateSQLQuery(sql)
               .SetInt32("CardTypeId", id);
           var list = query.List();
           foreach (object[] item in list)
           {
               li =new List<DuCardType>();
               var cardType = new DuCardType();
               cardType.CardTypeId = (int)item[0];
               cardType.CardTypeName = (string)item[1];
               cardType.CardTypeColor = item[2].ToString();
               li.Add(cardType);
           }
 
           //query = query.SetResultTransformer(Transformers.AliasToBean<OrderDto>());
           //var list2 = query.List<OrderDto>();
 
           var result = from cardType in li
                        orderby cardType.CardTypeName //名字排序
                        select cardType;
 
           return li;
 
       }

　　GetNamedQuery: 存储过程

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
/// <summary>
      /// 存储过程查询
      /// </summary>
      /// <param name="id"></param>
      /// <returns></returns>
      public IList<DuCardType> CardTypeProcedures(int id)
      {
          //读到数据
          IList<DuCardType> li = null;
          NHibernate.ISession session = this.SessionFactory.GetCurrentSession();
 
          try
          {
 
              IQuery query = session.GetNamedQuery("proc_Select_DuCardType")
                   .SetParameter("CardTypeId", id)
                   .SetResultTransformer(
                   Transformers.AliasToBean(typeof(DuCardType)));
              li = query.List<DuCardType>();
 
              //var executor = new HibernateStoredProcedureExecutor(session);
              //var lie = executor.ExecuteStoredProcedure<DuCardType>(
              //  "proc_Select_DuCardType",  //find no
              //  new[]
              //  {
              //      new SqlParameter("CardTypeId", id),
              //      //new SqlParameter("startDate", startDate),
              //     // new SqlParameter("endDate", endDate),
              //  });
              //li = lie.ToList();
              //return li;
 
 
 
              //string sql = @"exec proc_Select_DuCardType :CardTypeId";
              //IQuery query = session.CreateSQLQuery(sql)
              //    .SetInt32("CardTypeId", id);
              //var list = query.List();
              //foreach (object[] item in list)
              //{
              //    li = new List<DuCardType>();
              //    var cardType = new DuCardType();
              //    cardType.CardTypeId = (int)item[0];
              //    cardType.CardTypeName = (string)item[1];
              //    cardType.CardTypeColor = item[2].ToString();
              //    li.Add(cardType);
              //}
 
              ////query = query.SetResultTransformer(Transformers.AliasToBean<OrderDto>());
              ////var list2 = query.List<OrderDto>();
 
              //var result = from cardType in li
              //             orderby cardType.CardTypeName //名字排序
              //             select cardType;
          }
          catch (MappingException ex)
          {
              ex.Message.ToString();
          }
          return li;
 
      }

　　

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
/// <summary>
       /// Linq查询
       /// </summary>
       /// <param name="id"></param>
       /// <returns></returns>
       public IQueryable<DuCardType> getCardTypeID(int id)
       {
           NHibernate.ISession session = this.SessionFactory.GetCurrentSession();
           var query = session.Query<DuCardType>();
           var result = from cardType in query
                        where cardType.CardTypeId == id
                        select cardType;
           return result;
       }

　　

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
/// <summary>
       /// 存储过程分页
       /// 涂聚文 2016.07.03
       /// </summary>
       /// <param name="strwhere">Where条件</param>
       /// <param name="aecdesc">排序字段</param>
       /// <param name="pageIndex">开始页码</param>
       /// <param name="pageSize">页大小</param>
       /// <param name="RecordCount">输出总页数</param>
       /// <returns></returns>
       public DataTable GetDataPage(string strwhere, string aecdesc, int pageIndex, int pageSize, out int RecordCount)
       {
           NHibernate.ISession session = this.SessionFactory.GetCurrentSession();
           DataSet data = new DataSet();
           try
           {
 
               //SessionFactory.ConnectionProvider.Driver
               IDbCommand cmd = SessionFactory.OpenSession().Connection.CreateCommand();
               //cmd.CommandText = "testaa";
 
 
               //IDbConnection con = session.Connection;
               //Type conType = con.GetType();
               //string conTypeName = conType.FullName;
               // if (!conTypeName.EndsWith("Connection"))
               //    throw new Exception("Unable to interpret connection type name: " + conTypeName);
               //string adapterTypeName = conTypeName.Substring(0, conTypeName.Length - 10) + "DataAdapter";
 
 
               IDbDataAdapter adapter = new SqlDataAdapter();
 
               //IDbDataAdapter adapter = conType.Assembly.CreateInstance(adapterTypeName) as IDbDataAdapter;
               //if (adapter == null)
               //    throw new Exception("Unable to load IDbDataAdapter: " + adapterTypeName); 
               // IDbCommand cmd = con.CreateCommand();
 
               cmd.CommandText = "GetPagedRecordFor2005_2008";
               cmd.CommandType = CommandType.StoredProcedure;
               IDbDataParameter p = cmd.CreateParameter();
               IDbDataParameter p1 = cmd.CreateParameter();
               IDbDataParameter p2 = cmd.CreateParameter();
               IDbDataParameter p3 = cmd.CreateParameter();
               IDbDataParameter p4 = cmd.CreateParameter();
               IDbDataParameter p5 = cmd.CreateParameter();
               IDbDataParameter p6 = cmd.CreateParameter();
              // IDbDataParameter p7 = cmd.CreateParameter();
 
               if (string.IsNullOrEmpty(strwhere))
               {
                   strwhere = " 1=1 ";
               }
               p.ParameterName = "Table";
               p.Value = "DuCardType";
 
               p1.ParameterName = "TIndex";
               p1.Value = "CardTypeId";
 
               p2.ParameterName = "Column";
               p2.Value = " * ";
 
               p3.ParameterName = "Sql";
               p3.Value = strwhere;
 
               p4.ParameterName = "PageIndex";
               p4.Value = pageIndex;
 
               p5.ParameterName = "PageSize";
               p5.Value = pageSize;
 
               p6.ParameterName = "Sort";
               p6.Value = aecdesc;
 
               //p1.ParameterName = "geovindu";
               //p1.Size = 10;
               //p1.Direction = ParameterDirection.Output;
 
 
 
               //p7.ParameterName = "TotalRecords";
               //p7.Size = 10;
               //p7.Direction = ParameterDirection.Output;
 
               cmd.Parameters.Add(p);
               cmd.Parameters.Add(p1);
               cmd.Parameters.Add(p2);
               cmd.Parameters.Add(p3);
               cmd.Parameters.Add(p4);
               cmd.Parameters.Add(p5);
               cmd.Parameters.Add(p6);
              // cmd.Parameters.Add(p7);
 
               adapter.SelectCommand = cmd;
               adapter.Fill(data);
               //RecordCount =(int)p7.Value;        
               cmd.Cancel();
               cmd.Dispose();
               RecordCount = data.Tables[0].Rows.Count;
 
           }
           catch (NotImplementedException ex)
           {
 
               RecordCount = 0;
               ex.Message.ToString();
           }
 
           return data.Tables[0];
 
          // return dal.GetDataPage(strwhere, aecdesc, pageIndex, pageSize, out RecordCount);
       }

　

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
/// <summary>
       /// 存储过程分页
       /// 涂聚文 2016.07.03
       /// </summary>
       /// <param name="strwhere">Where条件</param>
       /// <param name="aecdesc">排序字段</param>
       /// <param name="pageIndex">开始页码</param>
       /// <param name="pageSize">页大小</param>
       /// <param name="RecordCount">输出总页数</param>
       /// <returns></returns>
       public DataTable GetDataPage(string strwhere, string aecdesc, int pageIndex, int pageSize, out int RecordCount)
       {
           NHibernate.ISession session = this.SessionFactory.GetCurrentSession();
           DataSet data = new DataSet();
           try
           {
 
               //SessionFactory.ConnectionProvider.Driver
               IDbCommand cmd = SessionFactory.OpenSession().Connection.CreateCommand();
               //cmd.CommandText = "testaa";
 
 
               //IDbConnection con = session.Connection;
               //Type conType = con.GetType();
               //string conTypeName = conType.FullName;
               // if (!conTypeName.EndsWith("Connection"))
               //    throw new Exception("Unable to interpret connection type name: " + conTypeName);
               //string adapterTypeName = conTypeName.Substring(0, conTypeName.Length - 10) + "DataAdapter";
 
 
               IDbDataAdapter adapter = new SqlDataAdapter();
 
               //IDbDataAdapter adapter = conType.Assembly.CreateInstance(adapterTypeName) as IDbDataAdapter;
               //if (adapter == null)
               //    throw new Exception("Unable to load IDbDataAdapter: " + adapterTypeName); 
               // IDbCommand cmd = con.CreateCommand();
               //1.
              // cmd.CommandText = "GetPagedRecordFor2005_2008";
              // cmd.CommandType = CommandType.StoredProcedure;
              // IDbDataParameter p = new SqlParameter();// cmd.CreateParameter();
              // IDbDataParameter p1 = cmd.CreateParameter();
              // IDbDataParameter p2 = cmd.CreateParameter();
              // IDbDataParameter p3 = cmd.CreateParameter();
              // IDbDataParameter p4 = cmd.CreateParameter();
              // IDbDataParameter p5 = cmd.CreateParameter();
              // IDbDataParameter p6 = cmd.CreateParameter();
              //// IDbDataParameter p7 = cmd.CreateParameter();
 
              // if (string.IsNullOrEmpty(strwhere))
              // {
              //     strwhere = " 1=1 ";
              // }
              // p.ParameterName = "Table";
              // p.Value = "DuCardType";
 
              // p1.ParameterName = "TIndex";
              // p1.Value = "CardTypeId";
 
              // p2.ParameterName = "Column";
              // p2.Value = " * ";
 
              // p3.ParameterName = "Sql";
              // p3.Value = strwhere;
 
              // p4.ParameterName = "PageIndex";
              // p4.Value = pageIndex;
 
              // p5.ParameterName = "PageSize";
              // p5.Value = pageSize;
 
              // p6.ParameterName = "Sort";
              // p6.Value = aecdesc;
 
              // //p1.ParameterName = "geovindu";
              // //p1.Size = 10;
              // //p1.Direction = ParameterDirection.Output;
 
 
               //输出值
              // //p7.ParameterName = "TotalRecords";
              // //p7.Size = 10;
              // //p7.Direction = ParameterDirection.Output;
 
              // cmd.Parameters.Add(p);
              // cmd.Parameters.Add(p1);
              // cmd.Parameters.Add(p2);
              // cmd.Parameters.Add(p3);
              // cmd.Parameters.Add(p4);
              // cmd.Parameters.Add(p5);
              // cmd.Parameters.Add(p6);
              //// cmd.Parameters.Add(p7);
              // adapter.SelectCommand = cmd;
              // adapter.Fill(data);
              // //RecordCount =(int)p7.Value;        
              // cmd.Cancel();
              // cmd.Dispose();
 
 
               //2.     
               IDbDataParameter[] par = new SqlParameter[]
               {
                   new SqlParameter(), //表名
                   new SqlParameter(),//主键，可以带表头
                   new SqlParameter(),//读取字段
                   new SqlParameter(),//Where条件
                   new SqlParameter(),//开始页码
                   new SqlParameter(),//页大小
                   new SqlParameter(),//排序字段
 
               };
               if (string.IsNullOrEmpty(strwhere))
               {
                   strwhere = " 1=1 ";
               }
               par[0].ParameterName = "Table"; //表名
               par[0].Value="DuCardType";
               par[1].ParameterName = "TIndex";
               par[1].Value = "CardTypeId";
               par[2].ParameterName = "Column";
               par[2].Value = " * ";
               par[3].ParameterName = "Sql";//查询条件
               par[3].Value = strwhere;
               par[4].ParameterName = "PageIndex";
               par[4].Value = pageIndex;
               par[5].ParameterName = "PageSize";
               par[5].Value = pageSize;
               par[6].ParameterName = "Sort";
               par[6].Value = aecdesc;
               data = DBHelper.ExecuteDataSet(SessionFactory, CommandType.StoredProcedure, "GetPagedRecordFor2005_2008", par);
 
               RecordCount = data.Tables[0].Rows.Count;
 
           }
           catch (NotImplementedException ex)
           {
 
               RecordCount = 0;
               ex.Message.ToString();
           }
 
           return data.Tables[0];
 
          // return dal.GetDataPage(strwhere, aecdesc, pageIndex, pageSize, out RecordCount);
       }

　　

　

 

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
/// <summary>
   /// 2016 geovindu
   /// </summary>
   public static class IListConverDataTable
   {
       /// <summary>
       ///
       /// </summary>
       /// <typeparam name="T"></typeparam>
       /// <param name="data"></param>
       /// <returns></returns>
       public static DataTable ToDataTable<T>(this IList<T> data)
       {
           PropertyDescriptorCollection properties =
               TypeDescriptor.GetProperties(typeof(T));
           DataTable table = new DataTable();
           foreach (PropertyDescriptor prop in properties)
               table.Columns.Add(prop.Name, Nullable.GetUnderlyingType(prop.PropertyType) ?? prop.PropertyType);
           foreach (T item in data)
           {
               DataRow row = table.NewRow();
               foreach (PropertyDescriptor prop in properties)
                   row[prop.Name] = prop.GetValue(item) ?? DBNull.Value;
               table.Rows.Add(row);
           }
           return table;
       }
       /// <summary>
       ///
       /// </summary>
       /// <param name="list"></param>
       /// <returns></returns>
       public static DataTable ToDataTable(IList list)
       {
           DataTable result = new DataTable();
           if (list.Count > 0)
           {
               PropertyInfo[] propertys = list[0].GetType().GetProperties();
               foreach (PropertyInfo pi in propertys)
               {
                   result.Columns.Add(pi.Name, pi.PropertyType);
               }
               for (int i = 0; i < list.Count; i++)
               {
                   ArrayList tempList = new ArrayList();
                   foreach (PropertyInfo pi in propertys)
                   {
                       object obj = pi.GetValue(list[i], null);
                       tempList.Add(obj);
                   }
                   object[] array = tempList.ToArray();
                   result.LoadDataRow(array, true);
               }
           }
           return result;
       }
   }

　　

 DBHelper.cs

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
  //
  private IDbConnection _idbConnection;
  private IDataReader _iDataReader;
  private IDbCommand _idbCommand;
  private IDbTransaction _idbTransaction;
  private IDbDataParameter[] _idbParameters;
  /// <summary>
  ///
  /// </summary>
  public IDbConnection Connection
  {
      get { return _idbConnection; }
      set { _idbConnection = value; }
  }
 
  public IDataReader DataReader
  {
      get { return _iDataReader; }
      set { _iDataReader = value; }
  }
 
  public IDbCommand Command
  {
      get { return _idbCommand; }
      set { _idbCommand = value; }
  }
 
  public IDbTransaction Transaction
  {
      get { return _idbTransaction; }
      set { _idbTransaction = value; }
  }
 
  public IDbDataParameter[] Parameters
  {
      get { return _idbParameters; }
      set { _idbParameters = value; }
  }
 
 
  public static IDbTransaction GetTransaction(NHibernate.ISession session) 
 { 
     IDbConnection iDbConnection = session.Connection; 
     IDbTransaction iDbTransaction = iDbConnection.BeginTransaction(); 
     return iDbTransaction; 
 }
 
  public static IDbDataParameter[] GetParameters(int paramsCount)
 {
     IDbDataParameter[] idbParams = new IDbDataParameter[paramsCount];
       
     for (int i = 0; i < paramsCount; i++)
     {
         idbParams[i] = new SqlParameter();
     }
     return idbParams; 
 }
/// <summary>
///
/// </summary>
/// <param name="paramsCount"></param>
  public void CreateParameters(int paramsCount)
  {
      Parameters = new IDbDataParameter[paramsCount];
      Parameters = GetParameters(paramsCount);
  }
 
  public void AddParameters(int index, string paramName, object objValue)
  {
      if (index < Parameters.Length)
      {
          Parameters[index].ParameterName = paramName;
          Parameters[index].Value = objValue;
      }
  }
  /// <summary>
  ///
  /// </summary>
  /// <param name="sessionFactory"></param>
  /// <param name="commandType"></param>
  /// <param name="commandText"></param>
  /// <returns></returns>
  public IDataReader ExecuteReader(NHibernate.ISessionFactory sessionFactory, CommandType commandType, string commandText)
  {
      Command = sessionFactory.OpenSession().Connection.CreateCommand();
      Command.Connection = Connection;
      PrepareCommand(Command, Connection, Transaction, commandType, commandText, Parameters);
      DataReader = Command.ExecuteReader();
      Command.Parameters.Clear();
      return DataReader;
  }
  /// <summary>
  ///
  /// </summary>
  /// <param name="sessionFactory"></param>
  /// <param name="commandType"></param>
  /// <param name="commandText"></param>
  /// <returns></returns>
  public int ExecuteNonQuery(NHibernate.ISessionFactory sessionFactory, CommandType commandType, string commandText)
  {
      Command = sessionFactory.OpenSession().Connection.CreateCommand();
      PrepareCommand(Command, Connection, Transaction, commandType, commandText, Parameters);
      int returnValue = Command.ExecuteNonQuery();
      Command.Parameters.Clear();
      return returnValue;
  }
 
  public object ExecuteScalar(NHibernate.ISessionFactory sessionFactory, CommandType commandType, string commandText)
  {
      Command = sessionFactory.OpenSession().Connection.CreateCommand();
      PrepareCommand(Command, Connection, Transaction, commandType, commandText, Parameters);
      object returnValue = Command.ExecuteScalar();
      Command.Parameters.Clear();
      return returnValue;
  }
    /// <summary>
    ///
    /// </summary>
    /// <param name="sessionFactory"></param>
    /// <param name="commandType"></param>
    /// <param name="commandText"></param>
    /// <returns></returns>
  public DataSet ExecuteDataSet(NHibernate.ISessionFactory sessionFactory, CommandType commandType, string commandText)
  {
      Command = sessionFactory.OpenSession().Connection.CreateCommand();
      PrepareCommand(Command, Connection, Transaction, commandType, commandText, Parameters);
      IDbDataAdapter dataAdapter = new SqlDataAdapter(); //根据不同的数据库选择不同的数据库
      dataAdapter.SelectCommand = Command;
      DataSet dataSet = new DataSet();
      dataAdapter.Fill(dataSet);
      Command.Parameters.Clear();
      return dataSet;
  }
  /// <summary>
  ///
  /// </summary>
  /// <param name="sessionFactory"></param>
  /// <param name="commandType"></param>
  /// <param name="commandText"></param>
  /// <param name="sqlParams"></param>
  /// <returns></returns>
  public DataSet ExecuteDataSet(NHibernate.ISessionFactory sessionFactory, CommandType commandType, string commandText, IDbDataParameter[] sqlParams)
  {
      Command = sessionFactory.OpenSession().Connection.CreateCommand();
      PrepareCommand(Command, Connection, Transaction, commandType, commandText, sqlParams);
      IDbDataAdapter dataAdapter = new SqlDataAdapter(); //根据不同的数据库选择不同的数据库
      dataAdapter.SelectCommand = Command;
      DataSet dataSet = new DataSet();
      dataAdapter.Fill(dataSet);
      Command.Parameters.Clear();
      return dataSet;
  }
 
/// <summary>
///
/// </summary>
/// <param name="command"></param>
/// <param name="commandParameters"></param>
  private void AttachParameters(IDbCommand command, IDbDataParameter[] commandParameters)
  {
      foreach (IDbDataParameter idbParameter in commandParameters)
      {
          if (idbParameter.Direction == ParameterDirection.InputOutput && idbParameter.Value == null)
          {
              idbParameter.Value = DBNull.Value;
          }
          command.Parameters.Add(idbParameter);
      }
  } 
/// <summary>
///
/// </summary>
/// <param name="command"></param>
/// <param name="connection"></param>
/// <param name="transaction"></param>
/// <param name="commandType"></param>
/// <param name="commandText"></param>
/// <param name="commandParameters"></param>
  private void PrepareCommand(IDbCommand command, IDbConnection connection, IDbTransaction transaction,
                               CommandType commandType, string commandText, IDbDataParameter[] commandParameters)
  {
      command.Connection = connection;
      command.CommandText = commandText;
      command.CommandType = commandType;
      if (transaction != null)
      {
          command.Transaction = transaction;
      }
      if (commandParameters != null)
      {
          AttachParameters(command, commandParameters);
      }
  } 

　　

posted @   ®Geovin Du Dream Park™  阅读(778)  评论(0)  编辑  收藏  举报
编辑推荐：
· AI与.NET技术实操系列：基于图像分类模型对图像进行分类
· go语言实现终端里的倒计时
· 如何编写易于单元测试的代码
· 10年+ .NET Coder 心语，封装的思维：从隐藏、稳定开始理解其本质意义
· .NET Core 中如何实现缓存的预热？
阅读排行：
· 25岁的心里话
· 闲置电脑爆改个人服务器（超详细） #公网映射 #Vmware虚拟网络编辑器
· 基于 Docker 搭建 FRP 内网穿透开源项目（很简单哒）
· 零经验选手，Compose 一天开发一款小游戏！
· 一起来玩mcp_server_sqlite，让AI帮你做增删改查！！
历史上的今天：
2011-07-01 SQL 一个表中的两个外键来自于同一个表创建的视图
< 2025年3月 >
日 一 二 三 四 五 六
23 24 25 26 27 28 1
2 3 4 5 6 7 8
9 10 11 12 13 14 15
16 17 18 19 20 21 22
23 24 25 26 27 28 29
30 31 1 2 3 4 5
点击右上角即可分享
微信分享提示