我的数据访问函数库的源代码（二）—— SQL语句部分
我的数据访问函数库的源代码（二）—— SQL语句部分
用户1174620
发布于 2018-02-07 16:23:49
发布于 2018-02-07 16:23:49
/* 2008 4 25 更新 */
我的数据访问函数库的源码。整个类有1400行，原先就是分开来写的，现在更新后还是分开来发一下吧。
第二部分：SQL语句部分。
传入SQL语句，执行相关的操作。
//查询语句部分
#region 运行查询语句返回记录（DataSet、DataTable、DataRow、String[]、String）
/// <summary>
/// 运行SQL查询语句 返回DataSet。可以传入多条查询语句，返回的DataSet里会有多个DataTable
/// </summary>
/// <param name="SQL">查询语句。比如select * from tableName</param>
/// <returns>返回DataSet</returns>
#region 函数实现 — — RunSqlDataSet
public DataSet RunSqlDataSet(string SQL)
{
SetCommand(SQL,); //设置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>
#region 函数实现 — — RunSqlDataTable
public DataTable RunSqlDataTable(string SQL)
{
SetCommand(SQL,); //设置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>
#region 函数实现 — — RunSqlDataRow
public DataRow RunSqlDataRow(string SQL)
{
SetCommand(SQL,); //设置command
SqlDataAdapter da = new SqlDataAdapter(cm);
try
{
DataTable DT = new DataTable();
da.Fill(DT);
if (DT.Rows.Count > )
return DT.Rows[];
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>
#region 函数实现 — — RunSqlStrings
public string[] RunSqlStrings(string SQL)
{
//返回ID 传入查询语句，返回第一条记录的第一的字段的值
SetCommand(SQL,); //设置command
SqlDataReader r = null;
try
{
if (cm.Connection.State == ConnectionState.Broken || cm.Connection.State == ConnectionState.Closed )
cm.Connection.Open();
r = cm.ExecuteReader();
string[] strValue = null;
if (r.Read() )
{
int ArrLength = r.FieldCount;
strValue = new string[ArrLength];
for(int i=;i<ArrLength;i++)
strValue[i] = r.GetValue(i).ToString();
}
return strValue;
}
catch(Exception ex)
{
SetErrorMsg("RunSqlStrs",SQL,ex.Message ); //处理错误
return null;
}
finally
{
if (r != null)
r.Close();
if (!isUseTrans)
cm.Connection.Close();
}
}
#endregion
/// <summary>
/// 运行SQl语句返回每一条记录的第一个字段的数组。返回字符串数组
/// </summary>
/// <param name="SQL">查询语句。比如select myName from tableName</param>
/// <param name="SQLForCount">用于统计记录数的SQL语句。如果传入数字的话，则直接使用数字作为记录数。</param>
/// <returns></returns>
#region 函数实现 — — RunSqlStringsByRow
public string[] RunSqlStringsByRow(string SQL,string SQLForCount)
{
//先获取记录数
int RowCount = ;
if (Functions.IsInt(SQLForCount))
{
RowCount = Int32.Parse(SQLForCount);
}
else
{
string strRowCount = RunSqlGetID(SQLForCount);
if (strRowCount == null)
return null;
RowCount = Int32.Parse(strRowCount);
}
if (RowCount <)
return null;
//传入查询语句，返回每条记录的第一的字段的值
SetCommand(SQL,); //设置command
SqlDataReader r = null;
try
{
if (cm.Connection.State == ConnectionState.Broken || cm.Connection.State == ConnectionState.Closed )
cm.Connection.Open();
string[] strValue = new string[RowCount];
r = cm.ExecuteReader();
int i = ;
while (r.Read())
{
strValue[i] = r[].ToString();
i++;
}
return strValue;
}
catch(Exception ex)
{
SetErrorMsg("RunSqlStringsByRow",SQL,ex.Message ); //处理错误
return null;
}
finally
{
if (r != null)
r.Close();
if (!isUseTrans)
cm.Connection.Close();
}
}
#endregion
/// <summary>
/// 运行SQl语句返回每一条记录的第一个字段的数组。返回字符串数组
/// </summary>
/// <param name="SQL">查询语句。比如select myName from tableName</param>
/// <returns></returns>
#region 函数实现 — — RunSqlStringsByRow
public string[] RunSqlStringsByRow(string SQL)
{
//传入查询语句，返回每条记录的第一的字段的值
SetCommand(SQL,); //设置command
SqlDataReader r = null;
try
{
if (cm.Connection.State == ConnectionState.Broken || cm.Connection.State == ConnectionState.Closed )
cm.Connection.Open();
r = cm.ExecuteReader();
//int i = 0;
System.Collections.IList li = new System.Collections.ArrayList();
while (r.Read())
li.Add(r[].ToString());
string[] strValue = new string[li.Count];
li.CopyTo(strValue,);
return strValue;
}
catch(Exception ex)
{
SetErrorMsg("RunSqlStringsByRow",SQL,ex.Message ); //处理错误
return null;
}
finally
{
if (r != null)
r.Close();
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>
#region 函数实现 — — RunSqlGetID
public string RunSqlGetID(string SQL)
{
SetCommand(SQL,); //设置command
SqlDataReader r = null;
try
{
if (cm.Connection.State == ConnectionState.Broken || cm.Connection.State == ConnectionState.Closed )
cm.Connection.Open();
r = cm.ExecuteReader(CommandBehavior.SingleRow);
string re = "";
if (r.Read())
re = r.GetValue().ToString();
else
re = null;
return re;
}
catch(Exception ex)
{
SetErrorMsg("RunSqlGetID",SQL,ex.Message ); //处理错误
return null;
}
finally
{
if (r != null)
r.Close();
if (!isUseTrans)
cm.Connection.Close();
}
}
#endregion
#endregion
#region 运行查询语句不返回记录集（无返回记录、检查持否存在指定的记录）
/// <summary>
/// 运行SQL查询语句，不返回记录集。用于添加、修改、删除等操作
/// </summary>
/// <param name="SQL">查询语句。比如insert into tableName 、update tableName</param>
/// <returns></returns>
#region 函数实现 — — RunSql
public void RunSql(string SQL)
{
SetCommand(SQL,); //设置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>
#region 函数实现 — — RunSqlExists
public bool RunSqlExists( string SQL)
{
SetCommand(SQL,); //设置command
SqlDataReader r = null;
try
{
if (cm.Connection.State == ConnectionState.Broken || cm.Connection.State == ConnectionState.Closed )
cm.Connection.Open();
r = cm.ExecuteReader();
bool re = true;
if (r.HasRows)
re = true;
else
re = false;
return re;
}
catch(Exception ex)
{
errorMsg = "运行RunSqlExists函数时出现错误。<BR>错误信息：" + ex.Message;
SetErrorMsg("RunSqlDataSet",SQL,ex.Message ); //处理错误
return true;
}
finally
{
if (r != null)
r.Close();
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();
SQL.Append("insert into "); //insert into
SQL.Append(TableName);
SQL.Append(" ([");
int i;
for( i = ;i< ziduan.Length ;i++) //字段
{
if (msg[i] != "_n_")
{
SQL.Append(ziduan[i]);
SQL.Append("],[");
}
}
SQL = SQL.Remove(SQL.Length -,);
SQL.Append(") values ('");
for( i = ;i<ziduan.Length ;i++)
{
if (msg[i] != "_n_")
{
SQL.Append(msg[i]);
SQL.Append("','");
}
}
SQL = SQL.Remove(SQL.Length -,);
SQL.Append(") select scope_identity() as a1");
string re = RunSqlGetID(SQL.ToString());
SQL.Length = ;
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();
SQL.Append("update "); //update
SQL.Append(TableName);
SQL.Append(" set ");
int i;
for (i = ;i< ziduan.Length ;i++)
{
if (msg[i] != "_n_")
{
SQL.Append("[");
SQL.Append(ziduan[i]); //update
SQL.Append("]='");
SQL.Append(msg[i]);
SQL.Append("',");
}
}
SQL = SQL.Remove(SQL.Length-,); //去掉最后一个 ","
SQL.Append(" where ");
SQL.Append(tiaojian);
RunSql(SQL.ToString());
return true;
}
#region "20071220增加的通过 FormTableCntrlInfo 保存数据的方法"
/// <summary>
/// 添加记录。传入表名，FormTableCntrlInfo，返回新生成记录的ID
/// </summary>
/// <param name="TableName">要添加记录的表的名称</param>
/// <param name="cInfo">FormTableCntrlInfo</param>
/// <returns></returns>
public string InsertDataStr(string TableName , FormTableCntrlInfo[] cInfo )
{
//添加数据 返回新添加的ID
System.Text.StringBuilder SQL = new System.Text.StringBuilder();
SQL.Append("insert into "); //insert into
SQL.Append(TableName);
SQL.Append(" ([");
int i;
for( i = ;i < cInfo.Length ;i++) //字段
{
if (cInfo[i].FieldValue != "_n_")
{
SQL.Append(cInfo[i].FieldName);
SQL.Append("],[");
}
}
SQL = SQL.Remove(SQL.Length -,);
SQL.Append(") values ('");
for( i = ;i < cInfo.Length ;i++)
{
if (cInfo[i].FieldValue != "_n_")
{
SQL.Append(cInfo[i].FieldValue);
SQL.Append("','");
}
}
SQL = SQL.Remove(SQL.Length -,);
SQL.Append(") select scope_identity() as a1");
string re = RunSqlGetID(SQL.ToString());
SQL.Length = ;
if (re == null)
return "-1";
else
return re;
}
/// <summary>
/// 修改记录。传入表名，FormTableCntrlInfo，修改条件
/// </summary>
/// <param name="TableName">要修改记录的表的名称</param>
/// <param name="cInfo">FormTableCntrlInfo</param>
/// <param name="tiaojian">条件 ，加在where 后面的语句</param>
/// <returns></returns>
public bool UpdateData( string TableName ,FormTableCntrlInfo[] cInfo ,string tiaojian)
{
System.Text.StringBuilder SQL = new System.Text.StringBuilder();
SQL.Append("update "); //update
SQL.Append(TableName);
SQL.Append(" set ");
int i;
for (i = ;i< cInfo.Length ;i++)
{
if (cInfo[i].FieldValue != "_n_")
{
SQL.Append("[");
SQL.Append(cInfo[i].FieldName); //update
SQL.Append("]='");
SQL.Append(cInfo[i].FieldValue);
SQL.Append("',");
}
}
SQL = SQL.Remove(SQL.Length-,); //去掉最后一个 ","
SQL.Append(" where ");
SQL.Append(tiaojian);
RunSql(SQL.ToString());
return true;
}
#endregion
#endregion下载全部源文件。
本文参与 腾讯云自媒体分享计划，分享自作者个人站点/博客。
原始发表：2006-08-16 ，如有侵权请联系 cloudcommunity@tencent.com 删除
评论
登录后参与评论
推荐阅读