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

数据访问层 —— SQL语句部分。
 

    //查询语句部分
        运行查询语句返回记录（DataSet、DataTable、DataRow、String[]、String）#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

        运行查询语句不返回记录集（无返回记录、检查持否存在指定的记录）#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

 

由于代码比较长，所以呢分成几个部分显示。
http://www.cnblogs.com/jyk/archive/2006/08/16/478021.html