使用ADO.NET访问数据库是目前较通用的手段，但使用过程中的感觉却差强人意，比如：频繁地处理DataSet, DataTable, DataAdapter 等对象。这次项目的时候，我把它再次封装了一下，比以前用起来稍微方便一点。
在命名空间MyUtilsSpace中主要有两个类DatabaseUtils和DataCursor 。直接操作数据库的方法放在DatabaseUtils中，而使用数据集操作数据库的方法放在DataCursor类中。在该命名空间中还有一个类StringParseTool，是为了对sql语句做一点简单的解析，不会直接用到。
1 添加到工程：
a.将文末DatabaseUtils.cs 代码复制并保存，然后添加到你的工程中
b.要在项目引用里添加System.cofiguration.dll的引用：解决方案->引用->添加引用->.NET->System.configuration->确定
c.在需要使用DatabaseUtils.cs中的类的地方添加命名空间引用：using MyUtilsSpace
2 具体使用方法：
2.1 创建DatabaseUtils对象，并初始化连接字符串
DatabaseUtils du= new DatabaseUtils();
string s=@"data source=（local);user ID=xxx;password=yyy;Initial Catalog=test;Integrated Security=True";
du.initConnectionDirectly(s);
//用具体数据源，数据库名，id，密码初始化连接 public string initConnection(string provider, string dataSource, string databaseName, string user, string password)//用App.config文件内的key键名初始化连接
public string initConnection(string providerName)
数据操作：若数据库中表tbl含有字段id, name, age
2.2删除数据，使用delete函数
//where子句的格式为 key1=? AND key2=? .....,然后whereArgs会一次代替前面的?
//也可以直接写出key1=value1 AND key2=value2....
//但都不必写出where关键字
public int delete(string tableName, string where, params string[] whereArgs)
du.open();
du.delete(“tbl”,”id>5”,null);
//或du.delete(“tbl”,”id>?”,new string[]{“5”});
du.close();2.3 插入数据，使用insert函数
//插入一行数据,返回插入的ID;若插入多行数据，返回受影响的行数
//Dictionary中key为string，value为object
//故可以存储简单文本也可以直接存储二进制对象
//一个Dictionary代表了一行数据，要存储多行就需要多个Dictionary
//注意多个Dictionary必须有相同的存储列
public int insert(string tableName, params Dictionary<string, object>[] values)
//要插入一行数据(12, “Tom”)du.open();
Dictionary<string,object> v=new Dictionary<string,object>();
v.Add(“id”,12);
v.Add(“name”,”Tom”);
du.insert(“tbl”,v);du.close();//如要插入二进制数据如图像，也是一样，若已将图像转换成字节数组byte[] image//dictionary.Add(“imageColumn”,image);
若要插入两行数据(12,“Tom”),(17,”Alice”)
Dictionary<string,object> v1=new Dictionary<string,object>();
Dictionary<string,object> v2=new Dictionary<string,object>();
v1.Add(“id”,12);
v1.Add(“name”,”Tom”);
v2.Add(“id”,17);
v2.Add(“name”,”Alice”);du.open();
du.insert(“tbl”,v1,v2);
//或du.insert(“tbl”,new Dictionary<string,object>[]{v1,v2});
du.close();当然，也提供了另一种插入方法：
public int insert(string tableName, string[] columns, object[] values)2.3 更新数据，使用update方法
//用Dictionay中的值更新某一行
public int update(string tableName, Dictionary<string, object> values, string where, params string[] whereArgs) public int update(string tableName, string[] columns, object[] values, string where, params string[] whereArgs)
以上方法都是直接处理数据库数据，没有使用DataSet，下面讲述的查询等操作则需要使用了DataSet，适合操作大量数据时使用。
2.4 查询数据
//查询数据库并返回一个数据游标类DataCursor
//参数tableNameInDataSet表示存储到数据集中的名称，可任意命名,为空则自动命名//参数select为sql语句
public DataCursor query(string tableNameInDataSet,string select, params string[] selectArgs)
//使用query不必显示open数据库，但如果使用了open()，则必须显示close()
DataCursor dc=du.query("xxx","select * from tbl",null);一般，我们需要对查询的数据进行处理，以下都是DataCursor 的方法
//新建空白行
DataRow newRow()
//增加多行到数据游标，注意，这些行必须由newRow()函数新建，然后手工填充得到
void addRows(params DataRow[] rows)
//用指定的数据增加一行，包含全部列的数据
void addRow(object[] value)
//添加一行新数据，且只添加指定列的数据
void addRow(string[] columnName, object[] value)
//删除某一索引行
void removeRow(int position)
//按一定条件删除某些行，如condition可能为: id>3
void removeRow(string condition)
//修改某一索引行的部分列的值
void modifyRow(int position,string[] columns,object[] values)
//按一定条件修改某些行的值
void modifyRow(string condition, string[] columns, object[] values)
//在完成数据集的操作后，需要更新数据集到数据库
void updateCursor()
DataCursor dc=du.query(null,"select * from tbl where id<100",null);
dc.removeRow("id>40");
dc.modifyRow("age>13",new string[]{"age"},new object[]{31});
dc.updateCursor();
ž
另外，DataCursor包含填充DataGridView 的方法：
ž注意：使用DataCursor，如果要修改数据，那么，创建DataCursor的sql语句必须包含主键且数据只能来自一张表
ž若只作为查询用，则没有限制,可以接受较简单的连接查询
public void fillDataGridView(ref System.Windows.Forms.DataGridView dgv,string property)dc.fillDataGridView(ref dataGridView1,null);3. 附上DatabaseUtils.cs源码
/*qjpcpu @2011-11-16
*
* DatabaseUtils是一个数据库操作组件
* 封装了数据库的插入，删除，更新这些基
* 本操作对查询操作，为了增加效率，将数
* 据集操作封装到了DataCursor中，可以
* 直接在DataCursor中进行断开数据库连
* 接情况下操作，更新完毕后再一起将所有
* 的更新同步到数据库。
* 1.将本文件复制到项目文件夹下，和其他*.cs文件同一目录，然后点击项目名，
* 添加现有项，浏览到本文件，添加，确定
* 2.要在项目引用里添加System.cofiguration.dll的引用
* 解决方案->引用->添加引用->.NET->System.configuration->确定
*
* I hope the routine would be helpful.
* Any problem, contact qjpcpu@gmail.com
*/
using System;
using System.Configuration;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using System.Data;
using System.Text.RegularExpressions;
namespace MyUtilsSpace
{
public class DatabaseUtils
{
//variables
public static string connString
{
private set;
get;
}
private SqlConnection sqlConn;
private DataSet myDataSet;
#region 初始化连接字符串的方法
/*
* initConnection初始化连接SqlConnection
*/
//具体用数据源，数据库名，id，密码初始化连接
public string initConnection(string provider, string dataSource, string databaseName, string user, string password)
{
if (provider != null)
connString += @"Provider=" + provider + ";";
if (dataSource == null)
throw new ArgumentException("The data source shouldn't be null");
connString += @"Data Source=" + dataSource + ";";
if (databaseName == null)
throw new ArgumentException("You must specify a database");
connString += @"Initial Catalog=" + databaseName + ";";
if (user == null)
throw new ArgumentException("You must specify a user to login");
connString += @"User ID=" + user + ";";
if (password == null)
throw new ArgumentException("The password of " + user + " can't be empty");
connString += @"Password=" + password + ";"+"Integrated Security=True";
sqlConn=new SqlConnection(connString);
return connString;
}
//用App.config文件内的key键名初始化连接
public string initConnection(string providerName)
{
connString = fetchConnectionString(providerName);
sqlConn = new SqlConnection(connString);
return connString;
}
public string fetchAppSettings(string key)
{
//要在项目引用里添加System.cofiguration.dll的引用
return ConfigurationManager.AppSettings[key];
}
public string fetchConnectionString(string provider)
{
return ConfigurationManager.ConnectionStrings[provider].ConnectionString;
}
//用连接字符串初始化连接
public string initConnectionDirectly(string connString)
{
sqlConn = new SqlConnection(connString);
return connString;
}
#endregion
//返回内建数据集
public DataSet getDataSet()
{
return myDataSet;
}
#region 数据库打开和关闭操作
public SqlConnection open()
{
try
{
sqlConn.Open();
}
catch (Exception e)
{
Console.WriteLine(e.Message);
Console.WriteLine("open database failed");
throw e;
}
return sqlConn;
}
public void close()
{
try
{
sqlConn.Close();
}
catch (Exception e)
{
Console.WriteLine(e.Message);
Console.WriteLine("close database failed");
throw e;
}
}
#endregion
#region 删除，更新，查询数据
//where子句的格式为 key1=? AND key2=? .....,然后whereArgs会一次代替前面的?
//也可以直接写出key1=value1 AND key2=value2....
//但都不必写出where关键字
public int delete(string tableName, string where, params string[] whereArgs)
{
string cmdString = "delete from " + tableName;
if (where != null)
{
cmdString += " where " + StringParseTool.replaceWithArgs(where, "?", whereArgs);
}
SqlCommand cmd=sqlConn.CreateCommand();
cmd.CommandText = cmdString;
int rtn=-1;
try
{
rtn=cmd.ExecuteNonQuery();
}
catch (Exception e)
{
Console.WriteLine(e.Message);
throw e;
}
return rtn;
}
//插入一行数据,返回插入的ID;若插入多行数据，返回受影响的行数
//Dictionary中key为string，value为object
//故可以存储简单文本也可以直接存储二进制对象
//一个Dictionary代表了一行数据，要存储多行就需要多个Dictionary
//注意多个Dictionary必须有相同的存储列
public int insert(string tableName, params Dictionary<string, object>[] values)
{
if (values == null)
throw new ArgumentException("the row you wanna insert is empty");
SqlCommand cmd = buildInsertCommand(tableName,values);
if (values.Length == 1)
{
cmd.CommandText += ";SELECT @@IDENTITY";
}
int id = -1;
cmd.Connection = sqlConn;
try
{
if (values.Length == 1)
{
id = Convert.ToInt32(cmd.ExecuteScalar());
}
else
{
id=cmd.ExecuteNonQuery();
}
}
catch (Exception e)
{
Console.WriteLine(e.Message);
throw e;
}
return id;
}
public int insert(string tableName, string[] columns, object[] values)
{
if (values == null || columns == null||(columns.Length>values.Length))
throw new ArgumentException("Illegal argument");
SqlCommand cmd = buildInsertCommand(tableName, columns, values);
if (values.Length == columns.Length)
{
cmd.CommandText += ";SELECT @@IDENTITY";
}
int id = -1;
cmd.Connection = sqlConn;
try
{
if (values.Length == columns.Length)
{
id = Convert.ToInt32(cmd.ExecuteScalar());
}
else
{
id = cmd.ExecuteNonQuery();
}
}
catch (Exception e)
{
Console.WriteLine(e.Message);
throw e;
}
return id;
}
private SqlCommand buildInsertCommand(string tableName, params Dictionary<string, object>[] values)
{
SqlCommand cmd = new SqlCommand();
string str1 = "insert into " + tableName + "(";
string str2 = " values ";
int loopCnt = 0;
foreach (Dictionary<string, object> element in values)
{
if (loopCnt == 0)
{
str2 += "(";
}
else
{
str2 += ",(";
}
foreach (KeyValuePair<string, object> pair in element)
{
if (loopCnt == 0)
{
str1 += pair.Key + ",";
}
str2 += "@" + pair.Key + loopCnt+" ,";
cmd.Parameters.AddWithValue("@" + pair.Key+loopCnt, pair.Value);
}
if (loopCnt == 0)
{
str1 = str1.Remove(str1.Length - 1) + ") ";
}
str2 = str2.Remove(str2.Length - 1) + ")";
loopCnt++;
}
cmd.CommandText = str1 + str2;
return cmd;
}
private SqlCommand buildInsertCommand(string tableName, string[] columns, object[] values)
{
SqlCommand cmd = new SqlCommand();
string str1 = "insert into " + tableName + "(";
string str2 = " values (";
int size = columns.Length;
for (int i = 0; i < size; i++)
{
str1 += columns[i] + ",";
}
str1 = str1.Remove(str1.Length - 1) + ") ";
for (int j = 0; j < values.Length; j++)
{
str2 += "@" + columns[j%size] + j + " ,";
cmd.Parameters.AddWithValue("@" + columns[j % size] + j, values[j]);
if(j>0&& ((j+1)%size)==0)
str2 = str2.Remove(str2.Length - 1) + "),(";
}
str2 = str2.Substring(0, str2.Length - 2);
cmd.CommandText = str1 + str2;
Console.WriteLine(str1 + str2);
return cmd;
}
private SqlCommand buildUpdateCommand(string tableName, Dictionary<string, object> values, string where, params string[] whereArgs)
{
SqlCommand cmd = new SqlCommand();
string str = "update " + tableName + " set ";
foreach (KeyValuePair<string, object> pair in values)
{
str += " "+pair.Key + "=@" + pair.Key+" ";
cmd.Parameters.AddWithValue("@" + pair.Key, pair.Value);
}
cmd.CommandText = str + " where " + StringParseTool.replaceWithArgs(where, "?", whereArgs);
return cmd;
}
private SqlCommand buildUpdateCommand(string tableName, string[] columns, object[] values, string where, params string[] whereArgs)
{
SqlCommand cmd = new SqlCommand();
string str = "update " + tableName + " set ";
int size = columns.Length;
for (int i = 0; i < values.Length; i++)
{
str += " " + columns[i % size] + "=@" + columns[i % size] + " ";
cmd.Parameters.AddWithValue("@" + columns[i % size], values[i]);
}
cmd.CommandText = str + " where " + StringParseTool.replaceWithArgs(where, "?", whereArgs);
return cmd;
}
//用Dictionay中的值更新某一行
public int update(string tableName, Dictionary<string, object> values, string where, params string[] whereArgs)
{
if (values == null)
throw new ArgumentException("the row you wanna insert is empty");
SqlCommand cmd = buildUpdateCommand(tableName, values,where,whereArgs);
int rtn = -1;
cmd.Connection = sqlConn;
try
{
rtn=cmd.ExecuteNonQuery();
}
catch (Exception e)
{
Console.WriteLine(e.Message);
throw e;
}
return rtn;
}
public int update(string tableName, string[] columns, object[] values, string where, params string[] whereArgs)
{
if (values == null||columns==null)
throw new ArgumentException("the row you wanna update is empty");
SqlCommand cmd = buildUpdateCommand(tableName, columns,values, where, whereArgs);
int rtn = -1;
cmd.Connection = sqlConn;
try
{
rtn = cmd.ExecuteNonQuery();
}
catch (Exception e)
{
Console.WriteLine(e.Message);
throw e;
}
return rtn;
}
//查询数据库并返回一个数据游标类DataCursor
//参数tableNameInDataSet表示存储到数据集中的名称，可任意命名,为空则自动命名
private static int table_tag = 0;
public DataCursor query(string tableNameInDataSet,string select, params string[] selectArgs)
{
if (selectArgs != null)
select = StringParseTool.replaceWithArgs(select, "?", selectArgs);
if (myDataSet == null)
myDataSet = new DataSet();
if (tableNameInDataSet == null || tableNameInDataSet=="")
{
tableNameInDataSet = "TABLE_"+table_tag+"_TABLE";
table_tag++;
}
return new DataCursor(this,sqlConn, ref myDataSet, select, tableNameInDataSet);
}
#endregion
#region 直接运行sql语句的ado.net方法
public int executeNonQuery(string selcetionString)
{
SqlCommand cmd = sqlConn.CreateCommand();
cmd.CommandText = selcetionString;
return cmd.ExecuteNonQuery();
}
public object executeScalar(string selcetionString)
{
SqlCommand cmd = sqlConn.CreateCommand();
cmd.CommandText = selcetionString;
return cmd.ExecuteScalar();
}
public SqlDataReader executeReader(string selcetionString)
{
SqlCommand cmd = sqlConn.CreateCommand();
cmd.CommandText = selcetionString;
return cmd.ExecuteReader();
}
#endregion
#region ExtendedProperty操作方法
public DataCursor listTableProperty(string property_name, string table_name, string column_name)
{
property_name=formatPropertyParams(property_name);
table_name = formatPropertyParams(table_name);
//选择表属性的select语句
string select_t = @"select objname as name,value as alias from fn_listextendedproperty(?, 'schema', 'dbo', 'table', ?, default, default)";
#region 选择列属性的select语句
string select_c = @"select name,value as alias
from ( select COLUMN_NAME as name from INFORMATION_SCHEMA.COLUMNS where TABLE_NAME=?)t
left outer join (select objname,value from fn_listextendedproperty(?, 'schema', 'dbo', 'table', ?, ?, ?))p
on p.objname=t.name COLLATE Chinese_PRC_CI_AS
where t.name like ?";
#endregion
#region 重构select语句
//列为null，查询表属性
if (column_name == null)
{
select_c = StringParseTool.replaceWithArgs(select_t, "?", new string[] { property_name,table_name });
}
//查询全部列属性
else if (column_name == "*")
{
select_c = StringParseTool.replaceWithArgs(select_c, "?", new string[] { table_name, property_name, table_name, "'column'", "default","'%'" });
}
//查询某列属性
else
{
select_c = StringParseTool.replaceWithArgs(select_c, "?", new string[] { table_name, property_name, table_name, "'column'", "default","'"+column_name+"'" });
}
#endregion
return query("property", select_c, null);
}
public void addProperty(string property_name, string property_value, string table_name, string column_name)
{
if (property_name == null)
{
throw new Exception("property_name should not be null");
}
if (table_name == null)
{
throw new Exception("table name should not be null");
}
string addPropertyString = @"exec sys.sp_addextendedproperty ? ,?,'schema','dbo','table',?,?,? ";
property_name = formatPropertyParams(property_name);
property_value = formatPropertyParams(property_value);
table_name = formatPropertyParams(table_name);
column_name = formatPropertyParams(column_name);
//添加表的属性
if (column_name == "null")
{
addPropertyString=StringParseTool.replaceWithArgs(addPropertyString, "?", new string[] {property_name,property_value,table_name,"null",column_name});
}
//添加列属性
else
{
addPropertyString = StringParseTool.replaceWithArgs(addPropertyString, "?", new string[] { property_name, property_value, table_name, @"'column'", column_name });
}
try
{
executeNonQuery(addPropertyString);
}
catch (Exception excep)
{
throw excep;
}
}
public void updateProperty(string property_name, string property_value, string table_name, string column_name)
{
if (property_name == null)
{
throw new Exception("property_name should not be null");
}
if (table_name == null)
{
throw new Exception("table name should not be null");
}
string updatePropertyString = @"exec sys.sp_updateextendedproperty ? ,?,'schema','dbo','table',?,?,? ";
property_name = formatPropertyParams(property_name);
property_value = formatPropertyParams(property_value);
table_name = formatPropertyParams(table_name);
column_name = formatPropertyParams(column_name);
//添加表的属性
if (column_name == "null")
{
updatePropertyString = StringParseTool.replaceWithArgs(updatePropertyString, "?", new string[] { property_name, property_value, table_name, "null", column_name });
}
//添加列属性
else
{
updatePropertyString = StringParseTool.replaceWithArgs(updatePropertyString, "?", new string[] { property_name, property_value, table_name, @"'column'", column_name });
}
try
{
executeNonQuery(updatePropertyString);
}
catch (Exception excep)
{
throw excep;
}
}
public void dropProperty(string property_name, string table_name, string column_name)
{
if (property_name == null)
{
throw new Exception("property_name should not be null");
}
if (table_name == null)
{
throw new Exception("table name should not be null");
}
string dropPropertyString = @"exec sys.sp_dropextendedproperty ? ,'schema','dbo','table',?,?,? ";
property_name = formatPropertyParams(property_name);
table_name = formatPropertyParams(table_name);
column_name = formatPropertyParams(column_name);
//添加表的属性
if (column_name == "null")
{
dropPropertyString = StringParseTool.replaceWithArgs(dropPropertyString, "?", new string[] { property_name, table_name, "null", column_name });
}
//添加列属性
else
{
dropPropertyString = StringParseTool.replaceWithArgs(dropPropertyString, "?", new string[] { property_name, table_name, @"'column'", column_name });
}
try
{
executeNonQuery(dropPropertyString);
}
catch (Exception excep)
{
throw excep;
}
}
private string formatPropertyParams(string str)
{
if (str == null)
{
str = "null";
}
else if (str == "*")
{
str = "default";
}
else
{
str = "'" + str + "'";
}
return str;
}
#endregion
}
//===========================================================
//注意：使用DataCursor，如果要修改数据，那么，创建DataCursor的sql语句
//必须包含主键且数据只能来自一张表
//若只作为查询用，则没有限制
//===========================================================
public class DataCursor
{
private int currentPosition; //游标位置
private SqlConnection sqlCon;
private SqlDataAdapter adapter;
private DataTable table;
private DatabaseUtils du;
private DataView dv;
public DataCursor(DatabaseUtils du,SqlConnection sqlCon,ref DataSet ds,string selection,string actionName)
{
currentPosition = 0;
this.du=du;
this.sqlCon = sqlCon;
adapter = new SqlDataAdapter(selection, sqlCon);
SqlCommandBuilder cb = new SqlCommandBuilder(adapter);
removeTable(ref ds, actionName);
adapter.Fill(ds, actionName);
table = ds.Tables[actionName];
dv = table.DefaultView;
}
private void removeTable(ref DataSet ds, string tableName)
{
try
{
ds.Tables.Remove(tableName);
}
catch (Exception ex) {
}
}
//移动到指定行
public bool moveTo(int position)
{
if (position < 0)
throw new ArgumentException("the cursor index must > 0");
if (position >= table.Rows.Count)
throw new IndexOutOfRangeException("the cursor index is out of bound");
currentPosition = position;
return true;
}
//获得内建的DataView
public DataView getDataView()
{
return dv;
}
//获得查询得到的实际DataTable
public DataTable getTable()
{
return table;
}
//移动到下一行
public bool moveToNext()
{
if (currentPosition + 1 < table.Rows.Count)
{
currentPosition++;
return true;
}
return false;
}
//获得当前行
public DataRow getRow()
{
Console.WriteLine("\n获取第"+currentPosition+"行\n");
return dv[currentPosition].Row;
}
//更新数据集到数据库
public void updateCursor()
{
adapter.Update(table);
}
//关闭连接
public void closeCursor()
{
sqlCon.Close();
}
//新建空白行
public DataRow newRow()
{
return table.NewRow();
}
//增加多行到数据游标，注意，这些行必须由newRow()函数新建，然后手工填充得到
public void addRows(params DataRow[] rows)
{
foreach (DataRow dr in rows)
{
table.Rows.Add(dr);
}
}
//用指定的数据增加一行，包含全部列的数据
public void addRow(object[] value)
{
DataRow dr = table.NewRow();
fillRowContent(dr, null, value);
table.Rows.Add(dr);
}
//添加一行新数据，且只添加指定列的数据
public void addRow(string[] columnName, object[] value)
{
DataRow dr = table.NewRow();
fillRowContent(dr, columnName, value);
table.Rows.Add(dr);
}
//删除某一索引行
public void removeRow(int position)
{
dv.Delete(position);
}
//按一定条件删除某些行
//如condition可能为: id>3
public void removeRow(string condition)
{
DataRow[] dr=table.Select(condition);
if (dr != null)
{
foreach (DataRow r in dr)
{
r.Delete();
}
}
}
//修改某一索引行的部分列的值
public void modifyRow(int position, string[] columns,object[] values)
{
DataRow r=dv[position].Row;
fillRowContent(r, columns, values);
}
//按一定条件修改某些行的值
public void modifyRow(string condition, string[] columns, object[] values)
{
DataRow[] dr = table.Select(condition);
if (dr != null)
{
foreach (DataRow r in dr)
{
fillRowContent(r, columns, values);
}
}
}
//用数据填充行
private void fillRowContent(DataRow r, string[] columns, object[] values)
{
for (int i = 0; i < values.Length; i++)
{
if (columns != null)
r[columns[i]] = values[i];
else
r[i] = values[i];
}
}
/*
* 填充DataGridView
*/
public void fillDataGridView(ref System.Windows.Forms.DataGridView dgv)
{
fillDataGridView(ref dgv, "column_name");
}
public void fillDataGridView(ref System.Windows.Forms.DataGridView dgv,string property)
{
dgv.DataSource = this.getDataView();
if (property != null)
{
try
{
string[] col = fetchColumnsName(property);
for (int i = 0; i < dgv.ColumnCount; i++)
{
dgv.Columns[i].HeaderCell.Value = col[i];
}
}
catch (Exception exc)
{
Console.WriteLine(exc.Message);
}
}
}
private string[] fetchColumnsName(string property)
{
List<Dictionary<string, object>> c,b;
StringParseTool.parseSelect(adapter.SelectCommand.CommandText, out c, out b);
string[] c_name;
if (c.Count == 0)
{
#region 查询所有表所有列
List<string> cols = new List<string>();
for (int j = 0; j < b.Count; j++)
{
string t_name = b[j]["name"].ToString();
DataCursor tdc = du.listTableProperty(property, t_name, "*");
do
{
DataRow r = tdc.getRow();
if (r["alias"].ToString() == "")
cols.Add(r["name"].ToString());
else
{
cols.Add(r["alias"].ToString());
}
} while (tdc.moveToNext());
}
c_name=cols.ToArray<string>();
return c_name;
#endregion
}
else
{
#region 查询属性得到列名
c_name = new string[c.Count];
for (int i = 0; i < c.Count; i++)
{
Dictionary<string, object> v = c[i];
//若select语句取了别名就不再用属性名
if (v["alias"].ToString() != "")
{
c_name[i] = v["alias"].ToString();
continue;
}
string tbl = v["master"].ToString();
string ccc = v["name"].ToString();
//去掉列名前的表名
if (ccc.IndexOf(".") > 0)
{
ccc = ccc.Substring(ccc.IndexOf(".") + 1);
}
Console.WriteLine("\n查询表" + tbl + "的列" + ccc + "的属性值" + property + "\n");
DataRow dr=du.listTableProperty(property, tbl, ccc).getRow();
c_name[i] = dr["alias"].ToString() == "" ? dr["name"].ToString() : dr["alias"].ToString();
}
return c_name;
#endregion
}
}
}
class StringParseTool
{
public const int TYPE_TABLE = 0;
public const int TYPE_COLUMN = 1;
public const string table_meta_expr = @"(?<name>\w+)(\s+as\s(?<alias>\w+))?";
public const string from_expr = @"\sfrom\s+" + table_meta_expr + @"(\s*,\s*" + table_meta_expr + @")*";
public const string join_expr = @"\sjoin\s+" + table_meta_expr + @"\s+on\s";
public const string column_meta_expr = @"(?<name>\w+(\.\w+)?)(\s+as\s(?<alias>\w+))?";
public const string column_select_expr1 = @"select\s+\*\s+";
public const string column_select_expr2 = @"select\s+" + column_meta_expr + @"(\s*,\s*" + column_meta_expr + @")*";
//用数组元素依次代替srcString中的oldString
public static string replaceWithArgs(string srcString, string oldString, params string[] newString)
{
if (srcString == null)
return "";
if (oldString == null)
return srcString;
if (newString == null)
return srcString.Replace(oldString, "");
if (newString.Length == 1)
return srcString.Replace(oldString, newString[0]);
int oldlength = oldString.Length;
int index = 0;
foreach (string n in newString)
{
index = srcString.IndexOf(oldString, index);
srcString = srcString.Remove(index, oldlength);
srcString = srcString.Insert(index, n);
index += n.Length;
}
return srcString;
}
/*
* 若columns的 size为0，则select语句为“select * from```` "需要进一步处理
* 否则 columns一列的键值对：type:0/1，0代表表，1代表列
* 如果是列：name:列名；alias:列别名;master：所属表名
* 如果是表：name:表名；alias：表别名；
*
*/
public static void parseSelect(string select, out List<Dictionary<string, object>> columns, out List<Dictionary<string, object>> tables)
{
columns = new List<Dictionary<string, object>>();
tables = new List<Dictionary<string, object>>();
parseFromSentence(select,ref tables);
parseJoinSentence(select,ref tables);
parseColumns(select, ref columns);
column2TableProject(ref columns,ref tables);
}
public static void column2TableProject(ref List<Dictionary<string, object>> columns, ref List<Dictionary<string, object>> tables)
{
if (columns.Count() == 0)
return;
foreach (Dictionary<string, object> pair in columns)
{
string c = pair["name"].ToString();
Console.WriteLine("----\n寻找列:" + c + "的所属表");
int i;
if ((i=c.IndexOf("."))>0)
{
#region 寻找列的表名,存入master中
c = c.Substring(0, i);
foreach (Dictionary<string, object> t in tables)
{
if (t.ContainsValue(c))
{
pair["master"] = t["name"];
Console.WriteLine("找到表:" + t["name"] + "\n----");
break;
}
}
#endregion
}
else
{
//如果没有限定表名，则认为第一个表单列
pair["master"] =tables[0]["name"];
Console.WriteLine("找到表:" + pair["master"] + "\n----");
}
}
}
public static void parseColumns(string sql_sentence, ref List<Dictionary<string,object>> list)
{
Regex r = new Regex(column_select_expr2, RegexOptions.IgnoreCase);
Match m = r.Match(sql_sentence);
if (m.Success)
{
string sub = sql_sentence.Substring(m.Index, m.Length).Substring(7);
fetchNameAndAlias(column_meta_expr, sub,TYPE_COLUMN, ref list);
return;
}
r = new Regex(column_select_expr1, RegexOptions.IgnoreCase);
m = r.Match(sql_sentence);
if (m.Success)
{
return;
}
}
public static void parseJoinSentence(string sql_sentence, ref List<Dictionary<string, object>> list)
{
Regex r = new Regex(join_expr, RegexOptions.IgnoreCase);
MatchCollection matchCollection = r.Matches(sql_sentence);
foreach (Match m in matchCollection) //匹配到的join子句
{
string tables = sql_sentence.Substring(m.Index, m.Length-4).Substring(5);
//Console.WriteLine(sql_sentence.Substring(m.Index, m.Length));
fetchNameAndAlias(table_meta_expr,tables,TYPE_TABLE, ref list);
}
}
public static void parseFromSentence(string sql_sentence, ref List<Dictionary<string, object>> list)
{
Regex r = new Regex(from_expr, RegexOptions.IgnoreCase);
MatchCollection matchCollection = r.Matches(sql_sentence);
foreach (Match m in matchCollection) //匹配到的from子句
{
string tables = sql_sentence.Substring(m.Index, m.Length).Substring(5);
//Console.WriteLine(sql_sentence.Substring(m.Index, m.Length));
fetchNameAndAlias(table_meta_expr,tables,TYPE_TABLE, ref list);
}
}
private static void fetchNameAndAlias(string meta_expr, string meta_list,int type, ref List<Dictionary<string, object>> list)
{
Regex r = new Regex(meta_expr, RegexOptions.IgnoreCase);
MatchCollection mc = r.Matches(meta_list);
foreach (Match m in mc)
{
GroupCollection gc = m.Groups;
Dictionary<string, object> v = new Dictionary<string, object>();
v.Add("name", gc["name"].Value);
v.Add("alias", gc["alias"].Value);
v.Add("type", type);
Console.WriteLine("\n找到列:" + gc["name"].Value + " 其别名:" + gc["alias"].Value + "\n");
list.Add(v);
}
}
}
}
扫一扫
1159
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
