#region BuilderDataAdapter 构造DataAdapter对象,解决并发问题,并填充DataSet对象
/// <summary>
/// 构造DataAdapter对象解决并发问题,并填充DataSet对象,使用此方法时需要在数据库中建立名为:v_TableFiled 的视图(参照《SQL2000、SQL2005和SQL2008用户表字典视图》)
/// </summary>
/// <remarks>
/// 示例:
/// BuilderDataAdapter("select a.aaa,a.bbb as abb,b.aaa as baa,b.bbb from tablea a,tableb","tablea","baa,bbb",true,da,ds,conn);
/// </remarks>
/// <param name="selectCommandText">SQL Select 语句</param>
/// <param name="tableName">需要更新的表名</param>
/// <param name="primaryKey">Update 关键字</param>
/// <param name="noUpdateField">不需要更新的字段</param>
/// <param name="isInit">是否第一次处理</param>
/// <param name="da">有效的数据配置器</param>
/// <param name="ds">数据集</param>
/// <param name="connection">有效的数据库连接对象</param>
public static void BuilderDataAdapter(string selectCommandText, string tableName, string primaryKey, string noUpdateField,bool isInit, SqlDataAdapter da, DataSet ds, SqlConnection connection)
{
#region 1 第一次啟用時進行SQL語句處理及构造InserrCommand、UpdateCommand、DeleteCommand
if (isInit)
{
#region 1.1 selectCommandText、NoUpdateField、Key等字符串规范化处理
//*************************************************************
//注意:
//1 selectCommandText语句中,需含有select 和 from关键字
//2 selectCommandText语句中,支持isnull,ltrim,rtrim,convert,case when 等关键字
//3 cSelectSQL语句中,若非原表字段,则需加as 如:rtrim(isnull(dep_bmmc,'')) as dep_bmmc
//*************************************************************
#region 1.1.1 selectCommandText、NoUpdateField、Key等字符串合法性校验
//1selectSQL、NoUpdateField等字符串合法性校验
if (string.IsNullOrEmpty(selectCommandText) || string.IsNullOrEmpty(tableName))
{
throw new ArgumentException("selectCommandText、Key等字符串有空值,无法解析!");
}
#endregion
#region 1.1.2 selectCommandText语句处理
string lcSQLStr = selectCommandText.ToLower();
lcSQLStr = lcSQLStr.Substring(lcSQLStr.IndexOf("select") + 6, lcSQLStr.IndexOf("from") - (lcSQLStr.IndexOf("select") + 6)).Trim() + ",";
lcSQLStr = lcSQLStr.Replace("\n", "").Replace("\r", "").Trim(); //移除回车换行符
string lcSQLStrNew = "";
string[] lcSQLField = lcSQLStr.Split(',');
for (int i = 0; i < lcSQLField.Length; i++)
{
lcSQLField[i] = lcSQLField[i].Trim();
if (lcSQLField[i].IndexOf('(') >= 0 && lcSQLField[i].IndexOf(" as ") <= 0) // 有左括号但没有 AS,忽略
{
continue;
}
if (lcSQLField[i].IndexOf(" as ") >= 0) //有 AS
{
lcSQLStrNew += lcSQLField[i].Substring(lcSQLField[i].IndexOf(" as ") + 4).Trim() + '\n';
continue;
}
if (lcSQLField[i].IndexOf(' ') < 0) // 无空格符,直接是字段名.
{
lcSQLStrNew += lcSQLField[i] + '\n';
continue;
}
else
{
throw new ArgumentException("未能处理的Select语句,请检查查询语句!");
}
}
lcSQLStrNew = lcSQLStrNew.Replace("\n\n", "\n");
#endregion
#region 1.1.3 noUpdateField处理
noUpdateField = noUpdateField.Replace(";", ",");
if (noUpdateField.Substring(noUpdateField.Length - 1, 1) != ",")
{
noUpdateField += ","; // 在noUpdateField后补入逗号,用于在判断是否包含时加逗号判断.以避免字段名前部分字符相同的情况.
}
#endregion
#endregion
#region 1.2 构造InserSQL、字段信息WhereSQL
string lcInsertSQL1 = " insert " + tableName + "("; //Insert SQL语句1
string lcInsertSQL2 = " values( "; //Insert SQL语句2
string lcInsertSQL = ""; //Inser SQL语句
string[] lcSQLField1 = lcSQLStrNew.Split('\n');
string lcWhereField = "("; //Where SQL语句
for (int i = 0; i < lcSQLField1.Length; i++)
{
if (noUpdateField.IndexOf(lcSQLField1[i] + ",") < 0)
{
lcWhereField += "'" + lcSQLField1[i] + "',";
lcInsertSQL1 += lcSQLField1[i] + ",";
lcInsertSQL2 += "@" + lcSQLField1[i] + ",";
}
}
//对字符串尾部再进行处理一下,构造成符合语法的结构
lcWhereField = lcWhereField.Substring(0, lcWhereField.LastIndexOf(",")) + ")";
#endregion
#region 1.3 查詢表結構信息,構造UpdateSQL、DeleteSQL、WhereSQL
string lcUpdateSQL = " update " + tableName + " set "; //Update SQL语句
string lcUpdateSQLWhere = " where 1 = 1 ";
string lcDeleteSQL = " delete " + tableName + " where 1=1 "; // Delete SQL 语句
string lcSelectViewSQL = "";
lcSelectViewSQL += " select field,datatype,length,isnullable from v_TableFiled ";
lcSelectViewSQL += " where tables='" + tableName + "'";
lcSelectViewSQL += " and field in " + lcWhereField;
SqlCommand parameterCommand = new SqlCommand(lcSelectViewSQL, connection);
SqlDataReader parameterReader;
try
{
parameterReader = parameterCommand.ExecuteReader();
}
catch (SqlException e)
{
string errorMessages = "查询表结构出错,错误原因是:\n";
for (int ii = 0; ii < e.Errors.Count; ii++)
{
errorMessages += e.Errors[ii].Message + "\n";
}
throw new ArgumentException(errorMessages);
}
SqlParameter[] sp = new SqlParameter[lcSQLField1.Length];
int nKeyFldNo = 0;
List<DBColumnInfo> columnList = new List<DBColumnInfo>();
DBColumnInfo columnInfo;
while (parameterReader.Read())
{
columnInfo = new DBColumnInfo();
columnInfo.Field = parameterReader.GetString(0).ToString().Trim();
columnInfo.DataType = parameterReader.GetString(1).ToString().Trim();
columnInfo.Length = int.Parse(parameterReader.GetString(2).ToString());
columnInfo.IsNullAble = parameterReader.GetInt32(3);
columnList.Add(columnInfo);
//SET語句
lcUpdateSQL += columnInfo.Field + "=@" + columnInfo.Field + ",";
//判斷字段是否允許為空,並給DeleteCommand和UpdateCommand語句添加原值比較
if (columnInfo.IsNullAble == 1)
{
lcDeleteSQL += " and (" + columnInfo.Field.Trim() + " is null OR " + columnInfo.Field.Trim() + "=@old_" + columnInfo.Field.Trim() + ")";
lcUpdateSQLWhere += " and (" + columnInfo.Field.Trim() + " is null OR " + columnInfo.Field.Trim() + "=@old_" + columnInfo.Field.Trim() + ")";
}
else
{
lcDeleteSQL += " and " + columnInfo.Field.Trim() + "=@old_" + columnInfo.Field.Trim();
lcUpdateSQLWhere += " and " + columnInfo.Field.Trim() + "=@old_" + columnInfo.Field.Trim();
}
}
parameterReader.Close();
lcInsertSQL1 = lcInsertSQL1.Substring(0, lcInsertSQL1.LastIndexOf(",")) + ")";
lcInsertSQL2 = lcInsertSQL2.Substring(0, lcInsertSQL2.LastIndexOf(",")) + ")";
lcUpdateSQL = lcUpdateSQL.Substring(0, lcUpdateSQL.LastIndexOf(","));
lcInsertSQL += lcInsertSQL1 + lcInsertSQL2;
lcUpdateSQL += lcUpdateSQLWhere; //+ primaryKey + "=@old_" + primaryKey;
#endregion
#region 1.4 构造InserrCommand、UpdateCommand、DeleteCommand
da.InsertCommand = new SqlCommand(lcInsertSQL, connection);
da.UpdateCommand = new SqlCommand(lcUpdateSQL, connection);
da.DeleteCommand = new SqlCommand(lcDeleteSQL, connection);
//構造Command參數
for (int i = 0; i < columnList.Count; i++)
{
columnInfo = columnList[i];
DBCommand.CommandBuilder(da, columnInfo.Field, columnInfo.Length, columnInfo.DataType);
//設置保存原值
nKeyFldNo++;
sp[nKeyFldNo] = new SqlParameter();
DBCommand.CommandBuilder(da, columnInfo.Field, columnInfo.Length, columnInfo.DataType, sp[nKeyFldNo]);
}
#endregion
}
#endregion
#region 2 查询数据
da.SelectCommand = new SqlCommand(selectCommandText, connection);
if (ds.Tables.Contains(tableName) == true)
{
ds.Tables[tableName].Clear();
}
try
{
da.Fill(ds, tableName);
}
catch (SqlException e)
{
string errorMessages = "查询后台数据出错,错误原因是:\n";
for (int ii = 0; ii < e.Errors.Count; ii++)
{
errorMessages += e.Errors[ii].Message + "\n";
}
throw new ArgumentException(errorMessages);
}
#endregion
}