关闭

为了更方便使用ADO.NET访问数据库

标签: stringobjectexceptiontablenulldataset
540人阅读 评论(0) 收藏 举报
分类:

          使用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)
eg.删除表tbl中id大于5的记录,返回受影响的行数

 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)
eg.

//要插入一行数据(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)
eg.查询tbl的记录

//使用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()
eg.查询表tbl中id<100的数据,然后删除其中id>40的数据,最后将age>13的记录age都改为31

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,如果要修改数据,那么,创建DataCursor的sql语句必须包含主键且数据只能来自一张表
ž若只作为查询用,则没有限制,可以接受较简单的连接查询

另外,DataCursor包含填充DataGridView 的方法:

 public void fillDataGridView(ref System.Windows.Forms.DataGridView dgv,string property)
eg.

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);
            }
        }
    }
  
}


0
0

查看评论
* 以上用户言论只代表其个人观点,不代表CSDN网站的观点或立场
    个人资料
    • 访问:193272次
    • 积分:3076
    • 等级:
    • 排名:第11244名
    • 原创:106篇
    • 转载:40篇
    • 译文:5篇
    • 评论:34条
    文章分类
    最新评论