using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.Common;
using System.Data.SqlClient;
using JYK;
namespace Database
{
public class DAL
{
#region 运行查询语句返回记录(DataSet、DataTable、DataRow、String[]、String)
public static DataSet RunSqlDataSet(string sql)
{
string msg = "";
return RunSqlDataSet(sql, out msg);
}
/// <summary>
/// 运行SQL查询语句 返回DataSet。可以传入多条查询语句,返回的DataSet里会有多个DataTable
/// </summary>
/// <param name="SQL">查询语句。比如select * from tableName</param>
#region 函数实现 — — RunSqlDataSet
public static DataSet RunSqlDataSet(string sql, out string ErrorMsg)
{
ErrorMsg = "";
//设置DataAdapter
DbDataAdapter da = Factory.CreateDataAdapter(sql);
try
{
DataSet DS = new DataSet();
da.Fill(DS);
return DS;
}
catch (Exception ex)
{
WriteLog.SetErrorMsg("RunSqlDataSet", sql, ex.Message); //处理错误
ErrorMsg = ex.Message + "<BR>RunSqlDataSet_SQL:" + sql;
return null;
}
finally
{
//关闭数据库,销毁实例
Factory.DbDataAdapterDispose(da);
}
}
#endregion
public static DataTable RunSqlDataTable(string sql)
{
string msg = "";
return RunSqlDataTable(sql, out msg);
}
/// <summary>
/// 运行SQL查询语句 返回DataTable。
/// </summary>
/// <param name="SQL">查询语句。比如select * from tableName</param>
///
#region 函数实现 — — RunSqlDataTable
public static DataTable RunSqlDataTable(string sql, out string ErrorMsg)
{
ErrorMsg = "";
//设置DataAdapter
DbDataAdapter da = Factory.CreateDataAdapter(sql);
try
{
DataTable DT = new DataTable();
da.Fill(DT);
return DT;
}
catch (Exception ex)
{
WriteLog.SetErrorMsg("RunSqlDataTable", sql, ex.Message); //处理错误
ErrorMsg = ex.Message + "<BR>RunSqlDataSet_SQL:" + sql;
return null;
}
finally
{
//关闭数据库,销毁实例
Factory.DbDataAdapterDispose(da);
}
}
#endregion
/// <summary>
/// 运行SQl语句返回第一条记录。返回DataRow
/// </summary>
/// <param name="SQL">查询语句。比如select * from tableName</param>
///
#region 函数实现 — — RunSqlDataRow
public static DataRow RunSqlDataRow(string sql)
{
//设置DataAdapter
DbDataAdapter da = Factory.CreateDataAdapter(sql);
try
{
DataTable DT = new DataTable();
da.Fill(DT);
if (DT.Rows.Count > 0)
return DT.Rows[0];
else
return null;
}
catch (Exception ex)
{
WriteLog.SetErrorMsg("RunSqlDataRow", sql, ex.Message); //处理错误
return null;
}
finally
{
//关闭数据库,销毁实例
Factory.DbDataAdapterDispose(da);
}
}
#endregion
/// <summary>
/// 运行SQl语句返回第一条记录的数组。返回字符串数组
/// </summary>
/// <param name="SQL">查询语句。比如select top 1 * from tableName</param>
///
#region 函数实现 — — RunSqlStrings
public static string[] RunSqlStrings(string sql)
{
//传入查询语句,返回第一条记录的字符串数组
//设置command
DbCommand cm = Factory.CreateCommand(sql);
try
{
cm.Connection.Open();
DbDataReader 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)
{
WriteLog.SetErrorMsg("RunSqlStrings", sql, ex.Message); //处理错误
return null;
}
finally
{
cm.Connection.Close();
cm.Connection.Dispose();
cm.Dispose();
}
}
#endregion
/// <summary>
/// 运行SQl语句返回每一条记录的第一个字段的数组。返回字符串数组
/// </summary>
/// <param name="SQL">查询语句。比如select myName from tableName</param>
///
#region 函数实现 — — RunSqlStringsByRow
public static string[] RunSqlStringsByRow(string sql)
{
//传入查询语句,每一条记录的第一个字段的数组。返回字符串数组
//设置command
DbCommand cm = Factory.CreateCommand(sql);
try
{
cm.Connection.Open();
DbDataReader r = cm.ExecuteReader();
System.Collections.Generic.List<string> list = new List<string>();
while (r.Read())
list.Add(r[0].ToString());
return list.ToArray();
}
catch (Exception ex)
{
WriteLog.SetErrorMsg("RunSqlStringsByRow", sql, ex.Message); //处理错误
return null;
}
finally
{
cm.Connection.Close();
cm.Connection.Dispose();
cm.Dispose();
}
}
#endregion
/// <summary>
/// 运行SQl语句返回第一条记录的第一列的值。
/// </summary>
/// <param name="SQL">查询语句。比如select top 1 ID from tableName where userName='aa'。会返回ID的内容</param>
///
#region 函数实现 — — RunSqlGetID
public static string RunSqlGetID(string sql)
{
//设置command
DbCommand cm = Factory.CreateCommand(sql);
try
{
cm.Connection.Open();
DbDataReader r = cm.ExecuteReader(CommandBehavior.SingleRow);
if (r.Read())
return r.GetValue(0).ToString();
else
return null;
}
catch (Exception ex)
{
WriteLog.SetErrorMsg("RunSqlGetID", sql, ex.Message); //处理错误
return null;
}
finally
{
cm.Connection.Close();
cm.Connection.Dispose();
cm.Dispose();
}
}
#endregion
#endregion
#region 运行查询语句不返回记录集(无返回记录、检查持否存在指定的记录)
/// <summary>
/// 运行SQL查询语句,不返回记录集。用于添加、修改、删除等操作
/// </summary>
/// <param name="SQL">查询语句。比如insert into tableName 、update tableName</param>
///
#region 函数实现 — — RunSql
public static void RunSql(string sql)
{
//设置command
DbCommand cm = Factory.CreateCommand(sql);
try
{
cm.Connection.Open();
cm.ExecuteNonQuery();
}
catch (Exception ex)
{
WriteLog.SetErrorMsg("RunSql", sql, ex.Message); //处理错误
}
finally
{
cm.Connection.Close();
cm.Connection.Dispose();
cm.Dispose();
}
}
#endregion
/// <summary>
/// 执行一条SQL语句,看是否能查到记录 有:返回true;没有返回false,用于判断是否重名
/// </summary>
/// <param name="SQL">查询语句。比如select ID from tableName where userName='aa'</param>
///
#region 函数实现 — — RunSqlExists
public static bool RunSqlExists(string sql)
{
//设置command
DbCommand cm = Factory.CreateCommand(sql);
try
{
if (cm.Connection.State == ConnectionState.Broken || cm.Connection.State == ConnectionState.Closed)
cm.Connection.Open();
DbDataReader r = cm.ExecuteReader(CommandBehavior.CloseConnection);
if (r.HasRows)
return true;
else
return false;
}
catch (Exception ex)
{
WriteLog.SetErrorMsg("RunSqlDataSet", sql, ex.Message); //处理错误
return true;
}
finally
{
cm.Connection.Close();
cm.Connection.Dispose();
cm.Dispose();
}
}
#endregion
#endregion
#region 查询语句的方式添加、修改数据
/// <summary>
/// 添加记录。传入表名,字段数组,值数组,返回新生成记录的ID
/// </summary>
/// <param name="TableName">要添加记录的表的名称</param>
/// <param name="ziduan">字段名数组</param>
/// <param name="msg">字段对应的值的数组</param>
/// <returns></returns>
public static string InsertDataStr(string TableName, string[] columns, 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 < columns.Length - 1; i++) //字段
{
SQL.Append(columns[i]);
SQL.Append(",");
}
SQL.Append(columns[i]);
SQL.Append(") values ('");
for (i = 0; i < columns.Length - 1; i++)
{
SQL.Append(msg[i]);
SQL.Append("','");
}
SQL.Append(msg[i]);
if (WebConfig.DataBaseType() == 1)
SQL.Append("') select scope_identity() as a1");
else
SQL.Append("')");
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 static bool UpdateData(string TableName, string[] cloumns, string[] msg, string myWhere)
{
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 < cloumns.Length - 1; i++)
{
SQL.Append(cloumns[i]); //update
SQL.Append("='");
SQL.Append(msg[i]);
SQL.Append("',");
}
SQL.Append(cloumns[i]); //update
SQL.Append("='");
SQL.Append(msg[i]);
SQL.Append("' where ");
SQL.Append(myWhere);
RunSql(SQL.ToString());
return true;
}
#endregion
}
}