我的数据访问层的源代码（二）

 #region 运行查询语句返回记录（DataSet、DataTable、DataRow、String[]、String）

    

        /**//// <summary>

        /// 运行SQL查询语句 返回DataSet。可以传入多条查询语句，返回的DataSet里会有多个DataTable

        /// </summary>

        /// <param name="SQL">查询语句。比如select * from tableName</param>

        /// <returns>返回DataSet</returns>

        函数实现 — — RunSqlDataSet#region 函数实现 — — RunSqlDataSet

        public DataSet RunSqlDataSet(string SQL)

        ...{    

            SetCommand(SQL,1);        //设置command

            SqlDataAdapter da = new SqlDataAdapter(cm);

            try

            ...{

                DataSet DS = new DataSet();

                da.Fill(DS);

                return DS;

            }

            catch(Exception ex)

            ...{

                SetErrorMsg("RunSqlDataSet",SQL,ex.Message );    //处理错误

                return null;

            }

            finally

            ...{

                //自动关闭了，不用手动关闭。

                da.Dispose();

            }

        }

        #endregion

        /**//// <summary>

        /// 运行SQL查询语句 返回DataTable。

        /// </summary>

        /// <param name="SQL">查询语句。比如select * from tableName</param>

        /// <returns>返回DataTable</returns>

        函数实现 — — RunSqlDataTable#region 函数实现 — — RunSqlDataTable

        public DataTable RunSqlDataTable(string SQL)

        ...{    

            SetCommand(SQL,1);        //设置command

            SqlDataAdapter da = new SqlDataAdapter(cm);

            try

            ...{

                DataTable DT = new DataTable();

                da.Fill(DT);

                return DT;

            }

            catch(Exception ex)

            ...{

                SetErrorMsg("RunSqlDataTable",SQL,ex.Message );    //处理错误

                return null;

            }

            finally

            ...{

                //自动关闭了，不用手动关闭。

                da.Dispose();

            }

        }

        #endregion

    

        /**//// <summary>

        /// 运行SQl语句返回第一条记录。返回DataRow

        /// </summary>

        /// <param name="SQL">查询语句。比如select * from tableName</param>

        /// <returns></returns>

        函数实现 — — RunSqlDataRow#region 函数实现 — — RunSqlDataRow

        public DataRow RunSqlDataRow(string SQL)

        ...{    

            SetCommand(SQL,1);        //设置command

            SqlDataAdapter da = new SqlDataAdapter(cm);

            try

            ...{

                DataTable DT = new DataTable();

                da.Fill(DT);

                if (DT.Rows.Count > 0)

                    return DT.Rows[0];

                else 

                    return null;

            }

            catch(Exception ex)

            ...{

                SetErrorMsg("RunSqlDataRow",SQL,ex.Message );    //处理错误

                return null;

            }

            finally

            ...{

                //自动关闭了，不用手动关闭。

                da.Dispose();

            }

            

        }

        #endregion

        /**//// <summary>

        /// 运行SQl语句返回第一条记录的数组。返回字符串数组

        /// </summary>

        /// <param name="SQL">查询语句。比如select top 1 * from tableName</param>

        /// <returns></returns>

        函数实现 — — RunSqlStrings#region 函数实现 — — RunSqlStrings

        public string[] RunSqlStrings(string SQL)

        ...{    

            //返回ID 传入查询语句，返回第一条记录的第一的字段的值

            SetCommand(SQL,1);        //设置command

            try

            ...{

                if (cm.Connection.State == ConnectionState.Broken || cm.Connection.State == ConnectionState.Closed )

                    cm.Connection.Open();

                

                SqlDataReader r =  cm.ExecuteReader(CommandBehavior.SingleRow);

                if (r.Read() )

                ...{

                    int ArrLength = r.FieldCount;

                

                    string[] strValue = new string[ArrLength];

                    for(int i=0;i<ArrLength;i++)

                        strValue[i] = r.GetValue(i).ToString();

                    return strValue;

                }

                else

                ...{

                    r.Close();

                    return null;

                }

            }

            catch(Exception ex)

            ...{

                SetErrorMsg("RunSqlStrs",SQL,ex.Message );    //处理错误

                return null;

            }

            finally

            ...{

                if (!isUseTrans)

                    cm.Connection.Close();

                

            }

        }

        #endregion

        /**//// <summary>

        /// 运行SQl语句返回每一条记录的第一个字段的数组。返回字符串数组

        /// </summary>

        /// <param name="SQL">查询语句。比如select myName from tableName</param>

        /// <param name="SQLForCount">用于统计记录数的SQL语句。如果传入数字的话，则直接使用数字作为记录数。</param>

        /// <returns></returns>

        函数实现 — — RunSqlStringsByRow#region 函数实现 — — RunSqlStringsByRow

        public string[] RunSqlStringsByRow(string SQL,string SQLForCount)

        ...{

            //先获取记录数

            int RowCount = 0;

            if (JYK.Functions.IsInt(SQLForCount))

            ...{

                RowCount = Int32.Parse(SQLForCount);

            }

            else

            ...{

                string strRowCount = RunSqlGetID(SQLForCount);

                if (strRowCount == null)

                    return null;

                

                RowCount = Int32.Parse(strRowCount);

            }

            if (RowCount <1)

                return null;

            

            //返回ID 传入查询语句，返回第一条记录的第一的字段的值

            SetCommand(SQL,1);        //设置command

            try

            ...{

                if (cm.Connection.State == ConnectionState.Broken || cm.Connection.State == ConnectionState.Closed )

                    cm.Connection.Open();

                

                string[] strValue = new string[RowCount];

                SqlDataReader r =  cm.ExecuteReader();

                int i = 0;

                while (r.Read())

                ...{

                    strValue[i] = r[0].ToString();

                    i++;

                }

                return strValue;

                

            }

            catch(Exception ex)

            ...{

                SetErrorMsg("RunSqlStrs",SQL,ex.Message );    //处理错误

                return null;

            }

            finally

            ...{

                if (!isUseTrans)

                    cm.Connection.Close();

                

            }

        }

        #endregion

        

        /**//// <summary>

        /// 运行SQl语句返回第一条记录的第一列的值。

        /// </summary>

        /// <param name="SQL">查询语句。比如select top 1 ID from tableName where userName='aa'。会返回ID的内容</param>

        /// <returns></returns>

        函数实现 — — RunSqlGetID#region 函数实现 — — RunSqlGetID

        public   string RunSqlGetID(string SQL)

        ...{    

            SetCommand(SQL,1);        //设置command

            try

            ...{

                if (cm.Connection.State == ConnectionState.Broken || cm.Connection.State == ConnectionState.Closed )

                    cm.Connection.Open();

                    

                SqlDataReader r =  cm.ExecuteReader(CommandBehavior.SingleRow);

                if (r.Read())

                    return r.GetValue(0).ToString();

                else

                    return null;

            }

            catch(Exception ex)

            ...{

                SetErrorMsg("RunSqlGetID",SQL,ex.Message );    //处理错误

                return null;

            }

            finally

            ...{

                if (!isUseTrans)

                    cm.Connection.Close();

            }

        }

        #endregion

        

        #endregion

        运行查询语句不返回记录集（无返回记录、检查持否存在指定的记录）

        查询语句的方式添加、修改数据运行查询语句返回记录（DataSet、DataTable、DataRow、String[]、String）#region 运行查询语句不返回记录集（无返回记录、检查持否存在指定的记录）

        /**//// <summary>

        /// 运行SQL查询语句，不返回记录集。用于添加、修改、删除等操作

        /// </summary>

        /// <param name="SQL">查询语句。比如insert into tableName 、update tableName...</param>

        /// <returns></returns>

        函数实现 — — RunSql#region 函数实现 — — RunSql

        public void RunSql(string SQL)

        ...{    

            SetCommand(SQL,1);        //设置command

            try

            ...{

                if (cm.Connection.State == ConnectionState.Broken || cm.Connection.State == ConnectionState.Closed )

                    cm.Connection.Open();

                executeRowCount = cm.ExecuteNonQuery();

            }

            catch(Exception ex)

            ...{

                SetErrorMsg("RunSql",SQL,ex.Message );    //处理错误

            }

            finally

            ...{

                if (!isUseTrans)

                    cm.Connection.Close();

            }

        }

        #endregion

        

        /**//// <summary>

        /// 执行一条SQL语句，看是否能查到记录 有：返回true；没有返回false，用于判断是否重名

        /// </summary>

        /// <param name="SQL">查询语句。比如select ID from tableName where userName='aa'</param>

        /// <returns></returns>

        函数实现 — — RunSqlExists#region 函数实现 — — RunSqlExists

        public bool RunSqlExists( string SQL)

        ...{

            SetCommand(SQL,1);        //设置command

            try

            ...{

                if (cm.Connection.State == ConnectionState.Broken || cm.Connection.State == ConnectionState.Closed )

                    cm.Connection.Open();

                    

                SqlDataReader r =  cm.ExecuteReader(CommandBehavior.CloseConnection);

                if (r.HasRows)

                    return true;

                else

                    return false;

            }

            catch(Exception ex)

            ...{

                errorMsg = "运行RunSqlExists函数时出现错误。<BR>错误信息：" + ex.Message;

                SetErrorMsg("RunSqlDataSet",SQL,ex.Message );    //处理错误

                return true;

            }

            finally

            ...{

                if (!isUseTrans)

                    cm.Connection.Close();

            }

        }

        #endregion

        #endregion#region 查询语句的方式添加、修改数据

        

        /**//// <summary>

        /// 添加记录。传入表名，字段数组，值数组，返回新生成记录的ID

        /// </summary>

        /// <param name="TableName">要添加记录的表的名称</param>

        /// <param name="ziduan">字段名数组</param>

        /// <param name="msg">字段对应的值的数组</param>

        /// <returns></returns>

        public   string InsertDataStr(string TableName  ,  string[] ziduan  ,  string[] msg )

        ...{

            //添加数据    返回新添加的ID

            System.Text.StringBuilder SQL = new System.Text.StringBuilder(300);

            SQL.Append("insert into ");                    //insert into 

            SQL.Append(TableName);

            SQL.Append(" (");

            int i;

            for( i = 0 ;i< ziduan.Length - 1;i++)        //字段

            ...{

                SQL.Append(ziduan[i]);

                SQL.Append(",");

            }

            SQL.Append(ziduan[i]);

            SQL.Append(")  values ('");

            for( i = 0;i<ziduan.Length - 1;i++)

            ...{

                SQL.Append(msg[i]);

                SQL.Append("','");

            }

            SQL.Append(msg[i]);

            SQL.Append("')  select scope_identity() as a1");

            

            string re = RunSqlGetID(SQL.ToString());

            SQL.Length = 1;

            if (re == null)

                return "-1";

            else

                return re;

        }

        

        /**//// <summary>

        /// 修改记录。传入表名，字段数组，值数组 

        /// </summary>

        /// <param name="TableName">要修改记录的表的名称</param>

        /// <param name="ziduan">字段名数组</param>

        /// <param name="msg">字段对应的值的数组</param>

        /// <param name="tiaojian">条件 ，加在where 后面的语句</param>

        /// <returns></returns>

        public bool UpdateData( string TableName ,string[] ziduan ,string[] msg ,string tiaojian) 

        ...{

            System.Text.StringBuilder SQL = new System.Text.StringBuilder(300);

            SQL.Append("update ");                    //update

            SQL.Append(TableName);

            SQL.Append(" set ");

            int i;

            for (i = 0 ;i< ziduan.Length - 1;i++)

            ...{

                SQL.Append(ziduan[i]);                    //update

                SQL.Append("='");

                SQL.Append(msg[i]);

                SQL.Append("',");

            }

            SQL.Append(ziduan[i]);                    //update

            SQL.Append("='");

            SQL.Append(msg[i]);

            SQL.Append("' where ");

            SQL.Append(tiaojian);

            

            RunSql(SQL.ToString());

            return true;

        

        }

        #endregion

 

由于代码比较长，所以呢分成几个部分显示。