//查询语句部分
运行查询语句返回记录（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
由于代码比较长，所以呢分成几个部分显示。
发表于 @ 2006年08月15日 20:31:00|评论(loading...)