自制工具,将SqlServer数据导入到SqlCE数据库

 

最近做一个SqlCE项目，需要频繁地从SqlServer2008服务器上提取数据到SqlCE数据库。

SqlServer2008中有生成数据脚本功能，可以通过脚本向导（右键点击数据库-〉任务-〉生成脚本）实现。但经常这么导数据太麻烦，一天导10几次能把你导崩溃。。。

突然想到动软代码生成器里边有生成数据脚本的功能。于是拿来参考着做了个SqlServer到SqlCE的数据转工具。

先晒一下界面：

主要功能： 

1、输入数据库名和表名，生成创建SQLCE数据库的脚本和向SQKCE数据库中插入数据的脚本。

2、将脚本输出到文件。

3、直接生成SQLCE数据库。

实现方法 

1、SqlServer中表结构的获取（根据动软代码生成器 ）

要从创建表，首先要提取表的结构信息。以下代码组织了一个用于查询表结构的Sql语句。

            StringBuilder strSql = new StringBuilder();
            strSql.Append("SELECT ");
            strSql.Append("colorder=C.column_id,");
            strSql.Append("ColumnName=C.name,");
            strSql.Append("TypeName=T.name, ");
            //strSql.Append("Length=C.max_length, ");
            strSql.Append("Length=CASE WHEN T.name='nchar' THEN C.max_length/2 WHEN T.name='nvarchar' THEN C.max_length/2 ELSE C.max_length END,");
            strSql.Append("Preci=C.precision, ");
            strSql.Append("Scale=C.scale, ");
            strSql.Append("IsIdentity=CASE WHEN C.is_identity=1 THEN N'√'ELSE N'' END,");
            strSql.Append("isPK=ISNULL(IDX.PrimaryKey,N''),");

            strSql.Append("Computed=CASE WHEN C.is_computed=1 THEN N'√'ELSE N'' END, ");
            strSql.Append("IndexName=ISNULL(IDX.IndexName,N''), ");
            strSql.Append("IndexSort=ISNULL(IDX.Sort,N''), ");
            strSql.Append("Create_Date=O.Create_Date, ");
            strSql.Append("Modify_Date=O.Modify_date, ");

            strSql.Append("cisNull=CASE WHEN C.is_nullable=1 THEN N'√'ELSE N'' END, ");
            strSql.Append("defaultVal=ISNULL(D.definition,N''), ");
            strSql.Append("deText=ISNULL(PFD.[value],N'') ");

            strSql.Append("FROM sys.columns C ");
            strSql.Append("INNER JOIN sys.objects O ");
            strSql.Append("ON C.[object_id]=O.[object_id] ");
            strSql.Append("AND (O.type='U' or O.type='V') ");
            strSql.Append("AND O.is_ms_shipped=0 ");
            strSql.Append("INNER JOIN sys.types T ");
            strSql.Append("ON C.user_type_id=T.user_type_id ");
            strSql.Append("LEFT JOIN sys.default_constraints D ");
            strSql.Append("ON C.[object_id]=D.parent_object_id ");
            strSql.Append("AND C.column_id=D.parent_column_id ");
            strSql.Append("AND C.default_object_id=D.[object_id] ");
            strSql.Append("LEFT JOIN sys.extended_properties PFD ");
            strSql.Append("ON PFD.class=1  ");
            strSql.Append("AND C.[object_id]=PFD.major_id  ");
            strSql.Append("AND C.column_id=PFD.minor_id ");
            //			strSql.Append("--AND PFD.name='Caption'  -- 字段说明对应的描述名称(一个字段可以添加多个不同name的描述) ");
            strSql.Append("LEFT JOIN sys.extended_properties PTB ");
            strSql.Append("ON PTB.class=1 ");
            strSql.Append("AND PTB.minor_id=0  ");
            strSql.Append("AND C.[object_id]=PTB.major_id ");
            //			strSql.Append("-- AND PFD.name='Caption'  -- 表说明对应的描述名称(一个表可以添加多个不同name的描述)   ");
            strSql.Append("LEFT JOIN ");// -- 索引及主键信息
            strSql.Append("( ");
            strSql.Append("SELECT  ");
            strSql.Append("IDXC.[object_id], ");
            strSql.Append("IDXC.column_id, ");
            strSql.Append("Sort=CASE INDEXKEY_PROPERTY(IDXC.[object_id],IDXC.index_id,IDXC.index_column_id,'IsDescending') ");
            strSql.Append("WHEN 1 THEN 'DESC' WHEN 0 THEN 'ASC' ELSE '' END, ");
            strSql.Append("PrimaryKey=CASE WHEN IDX.is_primary_key=1 THEN N'√'ELSE N'' END, ");
            strSql.Append("IndexName=IDX.Name ");
            strSql.Append("FROM sys.indexes IDX ");
            strSql.Append("INNER JOIN sys.index_columns IDXC ");
            strSql.Append("ON IDX.[object_id]=IDXC.[object_id] ");
            strSql.Append("AND IDX.index_id=IDXC.index_id ");
            strSql.Append("LEFT JOIN sys.key_constraints KC ");
            strSql.Append("ON IDX.[object_id]=KC.[parent_object_id] ");
            strSql.Append("AND IDX.index_id=KC.unique_index_id ");
            strSql.Append("INNER JOIN  ");// 对于一个列包含多个索引的情况,只显示第1个索引信息
            strSql.Append("( ");
            strSql.Append("SELECT [object_id], Column_id, index_id=MIN(index_id) ");
            strSql.Append("FROM sys.index_columns ");
            strSql.Append("GROUP BY [object_id], Column_id ");
            strSql.Append(") IDXCUQ ");
            strSql.Append("ON IDXC.[object_id]=IDXCUQ.[object_id] ");
            strSql.Append("AND IDXC.Column_id=IDXCUQ.Column_id ");
            strSql.Append("AND IDXC.index_id=IDXCUQ.index_id ");
            strSql.Append(") IDX ");
            strSql.Append("ON C.[object_id]=IDX.[object_id] ");
            strSql.Append("AND C.column_id=IDX.column_id  ");
            strSql.Append("WHERE O.name=N'" + TableName + "' ");
            strSql.Append("ORDER BY O.name,C.column_id  ");

TableName为用户输入的表名称。查询结果是一个包含了表详细信息的DataTable（通过ADO.NET的SqlDataAdapter将数据Fill到DataSet中）。

有了这些信息我们就可以生成创建表的语句了。（注：这些语句不适合SqlServer2000。具体这些语句是什么意思，问老李吧。。我也看不是很明白。）

2、自定义一个SqlServer中数据类型到SQLCE数据类型的映射。

SqlCE只支持18种数据类型，比SqlServer少的多。因此如果原数据库中有SqlCE不支持的数据类型，需要手动将数据类型映射为SqlCE数据类型。具体怎样映射，应可以根据需要手动设置。

还有一些用户自定义的数据类型，在SqlCE中是不可以使用的。也需要映射。下面这个类完成数据的映射功能。

首先编辑一个数据映射表（TypeMap.txt），保存一些如下面格式的数据：

bigint=bigint;

binary=binary;

char=nchar;

date=datetime;

decimal=float;

nvarchar=nvarchar;

varchar=nvarchar;

real=real;

smalldatetime=datetime;

smallint=smallint;

smallmoney=money;

text=ntext;

tinyint=tinyint;

uniqueidentifier=uniqueidentifier;

varbinary=varbinary;

NGUD=uniqueidentifier

前面是SqlServer中的数据类型，后边是想要映射到SqlCE中的数据类型，中间一分号隔开。这个映射可以根据需要手动调整。

下边类中的GetMapedDataType（）函数返回映射后的数据类型。 

    public class TypeMap
    {
        //获取数据库（SqlServer和SqlCE）字段类型映射
        private static string datafile = System.Windows.Forms.Application.StartupPath + "\\TypeMap.txt";
       
        //加载数据类型映射表
        private static  string[] LoadTypeMap()
        {
            string s = string.Empty;
            if (File.Exists(datafile))
            {
                StreamReader sr = new StreamReader(datafile, Encoding.UTF8);
                //读取数据到字符串中
                s = sr.ReadToEnd();
                sr.Close();
            }
            //替换掉字符串中的回车换行符
            s= s.Replace("\r\n", " ");
            return s.Split(';');
        }

        /// <summary>
        /// 获取数据库（SqlServer和SqlCE）字段类型映射
        /// </summary>
        /// <param name="sourceType">SqlServer数据类型</param>
        /// <returns>SqlCE数据类型</returns>
        public static string GetMapedDataType(string sourceType)
        {
            string[] ss = TypeMap.LoadTypeMap();
            //在映射表中查找原始类型映射后的数据类型
            foreach (string s in ss)
            {
                string[] sss = s.Split('=');
                if (sss[0].ToLower() == sourceType.ToLower())
                {
                    //如果找到，则返回映射后的类型
                    return sss[1];
                }
            }
            //找不到，返回输入的类型
            return sourceType;
        }
    }

 

3、生成创建表的语句（create table….）

            StringBuilder strclass = new StringBuilder();
            //strclass.AppendLine("if exists (select * from sysobjects where id = OBJECT_ID('[" + tablename + "]') and OBJECTPROPERTY(id, 'IsUserTable') = 1) ");
            //strclass.AppendLine("DROP TABLE [" + tablename + "]");

            //string PKfild = "";//主键字段
            //bool IsIden = false;//是否是标识字段
            StringBuilder ColdefaVal = new StringBuilder();//字段的默认值列表			

            Hashtable FildtabList = new Hashtable();//字段列表（字段名，字段类型）
            StringBuilder FildList = new StringBuilder();//字段列表

           DataTable dt = （1中语句查询后得到的表）; //包含表详细信息的DataTable

            //开始生成创建表语句。
            //建表第一句…..终于开始了
            strclass.AppendLine("CREATE TABLE [" + tablename + "] (");

            if (dt != null)
            {
                DataRow[] dtrows;
                dtrows = dt.Select();  //这里可以做点简单的筛选

                ////也可以这样
                //foreach (DataRow row in dt.Rows)
                //{
                //   //。。。。。。。 
                //}

                foreach (DataRow row in dtrows)
                {
                    string columnName = row["ColumnName"].ToString();
                    string columnType = TypeMap.GetMapedDataType(row["TypeName"].ToString());//转换后的数据类型

                    string IsIdentity = row["IsIdentity"].ToString();
                    string Length = row["Length"].ToString();
                    string Preci = row["Preci"].ToString();
                    string Scale = row["Scale"].ToString();
                    string ispk = row["isPK"].ToString();
                    string isnull = row["cisNull"].ToString();
                    string defaultVal = row["defaultVal"].ToString();

                    strclass.Append("[" + columnName + "] [" + columnType + "] ");

                    switch (columnType.Trim())
                    {
                        case "varchar":
                        case "char":
                        case "nchar":
                        case "binary":
                        case "nvarchar":
                        case "varbinary":
                            {
                                string len = this.GetDataTypeLenVal(columnType.Trim(), Length);//获取字符串长度（注2）
                                strclass.Append(" (" + len + ")");
                            }
                            break;
                        case "decimal":
                        case "numeric":
                            strclass.Append(" (" + Preci + "," + Scale + ")");
                            break;
                    }
                    if (isnull == "√")
                    {
                        strclass.Append(" NULL");
                    }
                    else
                    {
                        strclass.Append(" NOT NULL");
                    }
                    if (defaultVal != " ")
                    {
                        strclass.Append(" DEFAULT " + defaultVal);
                    }
                    strclass.AppendLine(",");

                    FildtabList.Add(columnName, columnType);
                    FildList.Append("[" + columnName + "],");

                    //if(defaultVal!="")
                    //{
                    //	ColdefaVal.Append("CONSTRAINT [DF_"+tablename+"_"+columnName+"] DEFAULT "+defaultVal+" FOR ["+columnName+"],");
                    //}

                    //if((ispk=="√")&&(PKfild==""))
                    //{						
                    //    PKfild=columnName;//得到主键
                    //}
                }
            }
            string s = strclass.ToString();

			//去掉strclass（）中最后一个豆号
            int n = s.LastIndexOf(',');
            string ss = s.Substring(0, n) + ")";
            strclass.Remove(0, strclass.ToString().Length);
            strclass.Append(ss);
            strclass.Append(";");//添加一个分号（;）用于分割字符串
            strclass.AppendLine(" ");
            strclass.AppendLine(" ");

		   	//去掉FildList（字段列表）中最后一个豆号
            s = FildList.ToString();
            n = s.LastIndexOf(',');
            ss = s.Substring(0, n);
            FildList.Remove(0, FildList.ToString().Length);
            FildList.Append(ss);

            //开始获取表中数据，dtdata中包含了查询数据库得到的数据
            DataTable dtdata = this.getData(dbname,tablename);
            //生成插入数据语句
            if (dtdata != null)
            {
                foreach (DataRow row in dtdata.Rows)//循环表数据
                {
                    StringBuilder strfild = new StringBuilder();
                    StringBuilder strdata = new StringBuilder();
                    //字段数组
                    string[] split = FildList.ToString().Split(new Char[] { ',' });

                    foreach (string fild in split)//循环一行数据的各个字段
                    {
                        string colname = fild.Substring(1, fild.Length - 2);
                        string coltype = " ";
                        foreach (DictionaryEntry myDE in FildtabList)
                        {
                            if (myDE.Key.ToString() == colname)
                            {
                                coltype = myDE.Value.ToString();
                            }
                        }
                        string strval = " ";
                        switch (coltype)
                        {
                            case "binary":
                                {
                                    //我的数据库中没有binary型数据，这里没有处理;
                                }
                                break;
                            case "bit":
                                {
                                    strval = (row[colname].ToString().ToLower() == "true") ? "1" : "0";
                                }
                                break;
                            default:
                                strval = row[colname].ToString().Trim().Replace(';','；');
                                break;
                        }
                        strdata.Append("'" + strval.Replace('\'', '°') + "',"); //字段中剔除分号“’”
                        strfild.Append("[" + colname.Replace('\'', '°') + "],");//数据中剔除分号“’”
					
                    }
					//还是三删除最后一个逗号
                    n = strdata.ToString().LastIndexOf(',');
                    ss = strdata.ToString().Substring(0, n);
                    strdata.Remove(0,strdata.ToString().Length);
                    strdata.Append(ss);

                    n = strfild.ToString().LastIndexOf(',');
                    ss = strfild.ToString().Substring(0, n);
                    strfild.Remove(0, strfild.ToString().Length);
                    strfild.Append(ss);


                    //导出数据INSERT语句
                    strclass.Append("INSERT [" + tablename + "] (");
                    strclass.Append(strfild.ToString());  //插入字段集
                    strclass.Append(") VALUES ( ");
                    strclass.Append(strdata.ToString().Replace(';','；');//数据值。并将英文分号替换为全角分号。
                    strclass.AppendLine(");");
				//一条插入语句完成
                }
            }
		
            //删除语句中最后一个分号
            n = strclass.ToString().LastIndexOf(';');
            ss = strclass.ToString().Substring(0,n);
            strclass.Remove(0, strclass.ToString().Length);
            strclass.Append(ss);

            return strclass.ToString();
        } 

注：1、这里使用分号将每条语句隔开，是为了使用的方便。因为SqlCE不能一次执行多条语句，向表中插入数据只能一条条地循环。用分号分割后方便以后的使用。

2、GetDataTypeLenVal函数来自动软代码生成器。没必要列出

4、使用脚本生成SqlCE数据库。

代码如下，说明很详细就不啰嗦了

首先添加引用System.Data.SqlServerCe的引用：

之后再代码中添加对SqlServerCE的引用：

using System.Data.SqlServerCe;

开始：

    public class SDF
    {
        string fileName = "C:\\123.sdf"; //要创建的SQLCE数据库位置
        public SDF(string file)
        {
            fileName = file;
        }
        /// <summary>
        /// 创建数据库函数
        /// </summary>
        /// <param name="file"></param>
        private void CreateDB(string file)
        {
            fileName = file;
            //SQLCE连接字符串
            string connStr = "Data Source=" + file;
            try
            {
                if (File.Exists(file))
                {
                    File.Delete(file);
                }
                //在指定位置创建数据库
                SqlCeEngine eng = new SqlCeEngine();
                eng.LocalConnectionString = connStr;
                eng.CreateDatabase();
            }
            catch (Exception exp)
            {
                throw exp;
            }
        }

        //执行传入的脚本，在SqlCE数据库中创建表并插入数据
        private void CreateTab(string ScriptStr)
        {
            string connStr = "Data Source=" + fileName;
            string[] ss = ScriptStr.Split(';');//分割脚本语句。
            using (SqlCeConnection conn = new SqlCeConnection(connStr))
            {
                //ＳＱＬＣＥ中逐条执行语句。。。
                SqlCeCommand cmd = new SqlCeCommand();
                cmd.Connection = conn;
                conn.Open();
                foreach (string sqlstr in ss)
                {
                    cmd.CommandText = sqlstr;//循环
                    cmd.ExecuteNonQuery();
                }
                conn.Close();
            }
        }

        //通过文件名和脚本文件组装数据库
        public void Create(string File, string Script)
        {
            this.CreateDB(File);
            this.CreateTab(Script);
        }
}

 

在程序中调用方法

         if (this.saveToFile.ShowDialog() == DialogResult.OK)
            {
                SDF sdf = new SDF(" ");　//懒得输入了。。其实不输入文件名也行
                string filaname = saveToFile.FileName;　　//只要在这里将文件名传进去就行了
                //将文本写入SqlCE数据库
                sdf.Create(filaname, this.GetCode());
            }

 

5、字段类型映射的设置方法：

在下边界面中输入映射类型，前边是原始字段类型后边是SqlCE支持的字段类型

6、总结

分析表的结构，然后组织创建表语句-〉查询数据，组织插入语句。读取文件中字符串映射，替换原来映射。

为了省事，没有设置主键；对所有字段都进行操作，不管是不是空。

虽然效率不高，比手工快多了。。呵呵

测试中，对九千多条数据2-3秒就可以完事。数据量太大就不行了。

60万条数据能让我的几近崩溃。。。原因很简单：string字符串太长，而且全部保存在内存中。可是谁家PDA中方60万条数据呢？暂时不动了。。。

 

不会插附件，本来想把程序包也放上去。。。。

最后感谢李天平老师的无私奉献。。。

 

2010-09-18上传代码:

https://files.cnblogs.com/zyqgold/ScriptGenerate.rar