#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
由于代码比较长,所以呢分成几个部分显示。 |
|
来自: 昵称10504424 > 《C#》