织梦CMS - 轻松建站从此开始！
高级搜索|网站地图|TAG标签RSS订阅[设为首页] [加入收藏]

罗索

搜索

热门标签:

当前位置: 主页>基础技术>数据库开发>

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

落鹤生 发布于 2010-07-06 16:06 点击:次 
最近做一个SqlCE项目，需要频繁地从SqlServer2008服务器上提取数据到SqlCE数据库。SqlServer2008中有生成数据脚本功能，可以通过脚本向导（右键点击数据库-〉任务-〉生成脚本）实现。但经常这么导数据太麻烦，一天导10几次能把你导崩溃。
TAG:

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

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

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

先晒一下界面：

clip_image002

主要功能：

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

2、将脚本输出到文件。

3、直接生成SQLCE数据库。

实现方法

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

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

 

  1. StringBuilder strSql = new StringBuilder(); 
  2. strSql.Append("SELECT "); 
  3. strSql.Append("colorder=C.column_id,"); 
  4. strSql.Append("ColumnName=C.name,"); 
  5. strSql.Append("TypeName=T.name, "); 
  6. //strSql.Append("Length=C.max_length, "); 
  7. strSql.Append("Length=CASE WHEN T.name='nchar' THEN C.max_length/2 WHEN
  8.  T.name='nvarchar' THEN C.max_length/2 ELSE C.max_length END,"); 
  9. strSql.Append("Preci=C.precision, "); 
  10. strSql.Append("Scale=C.scale, "); 
  11. strSql.Append("IsIdentity=CASE WHEN C.is_identity=1 THEN N'√'ELSE N'' END,"); 
  12. strSql.Append("isPK=ISNULL(IDX.PrimaryKey,N''),"); 
  13.  
  14. strSql.Append("Computed=CASE WHEN C.is_computed=1 THEN N'√'ELSE N'' END, "); 
  15. strSql.Append("IndexName=ISNULL(IDX.IndexName,N''), "); 
  16. strSql.Append("IndexSort=ISNULL(IDX.Sort,N''), "); 
  17. strSql.Append("Create_Date=O.Create_Date, "); 
  18. strSql.Append("Modify_Date=O.Modify_date, "); 
  19.  
  20. strSql.Append("cisNull=CASE WHEN C.is_nullable=1 THEN N'√'ELSE N'' END, "); 
  21. strSql.Append("defaultVal=ISNULL(D.definition,N''), "); 
  22. strSql.Append("deText=ISNULL(PFD.[value],N'') "); 
  23.  
  24. strSql.Append("FROM sys.columns C "); 
  25. strSql.Append("INNER JOIN sys.objects O "); 
  26. strSql.Append("ON C.[object_id]=O.[object_id] "); 
  27. strSql.Append("AND (O.type='U' or O.type='V') "); 
  28. strSql.Append("AND O.is_ms_shipped=0 "); 
  29. strSql.Append("INNER JOIN sys.types T "); 
  30. strSql.Append("ON C.user_type_id=T.user_type_id "); 
  31. strSql.Append("LEFT JOIN sys.default_constraints D "); 
  32. strSql.Append("ON C.[object_id]=D.parent_object_id "); 
  33. strSql.Append("AND C.column_id=D.parent_column_id "); 
  34. strSql.Append("AND C.default_object_id=D.[object_id] "); 
  35. strSql.Append("LEFT JOIN sys.extended_properties PFD "); 
  36. strSql.Append("ON PFD.class=1  "); 
  37. strSql.Append("AND C.[object_id]=PFD.major_id  "); 
  38. strSql.Append("AND C.column_id=PFD.minor_id "); 
  39. //strSql.Append("--AND PFD.name='Caption'
  40. //  -- 字段说明对应的描述名称(一个字段可以添加多个不同name的描述) "); 
  41. strSql.Append("LEFT JOIN sys.extended_properties PTB "); 
  42. strSql.Append("ON PTB.class=1 "); 
  43. strSql.Append("AND PTB.minor_id=0  "); 
  44. strSql.Append("AND C.[object_id]=PTB.major_id "); 
  45. //strSql.Append("-- AND PFD.name='Caption'  
  46. //-- 表说明对应的描述名称(一个表可以添加多个不同name的描述)   "); 
  47. strSql.Append("LEFT JOIN ");// -- 索引及主键信息 
  48. strSql.Append("( "); 
  49. strSql.Append("SELECT  "); 
  50. strSql.Append("IDXC.[object_id], "); 
  51. strSql.Append("IDXC.column_id, "); 
  52. strSql.Append("Sort=CASE INDEXKEY_PROPERTY(IDXC.[object_id],
  53. IDXC.index_id,IDXC.index_column_id,'IsDescending') "); 
  54. strSql.Append("WHEN 1 THEN 'DESC' WHEN 0 THEN 'ASC' ELSE '' END, "); 
  55. strSql.Append("PrimaryKey=CASE WHEN IDX.is_primary_key=1
  56.  THEN N'√'ELSE N'' END, "); 
  57. strSql.Append("IndexName=IDX.Name "); 
  58. strSql.Append("FROM sys.indexes IDX "); 
  59. strSql.Append("INNER JOIN sys.index_columns IDXC "); 
  60. strSql.Append("ON IDX.[object_id]=IDXC.[object_id] "); 
  61. strSql.Append("AND IDX.index_id=IDXC.index_id "); 
  62. strSql.Append("LEFT JOIN sys.key_constraints KC "); 
  63. strSql.Append("ON IDX.[object_id]=KC.[parent_object_id] "); 
  64. strSql.Append("AND IDX.index_id=KC.unique_index_id "); 
  65. strSql.Append("INNER JOIN  ");
  66. //对于一个列包含多个索引的情况,只显示第1个索引信息 
  67. strSql.Append("( "); 
  68. strSql.Append("SELECT [object_id], Column_id, index_id=MIN(index_id) "); 
  69. strSql.Append("FROM sys.index_columns "); 
  70. strSql.Append("GROUP BY [object_id], Column_id "); 
  71. strSql.Append(") IDXCUQ "); 
  72. strSql.Append("ON IDXC.[object_id]=IDXCUQ.[object_id] "); 
  73. strSql.Append("AND IDXC.Column_id=IDXCUQ.Column_id "); 
  74. strSql.Append("AND IDXC.index_id=IDXCUQ.index_id "); 
  75. strSql.Append(") IDX "); 
  76. strSql.Append("ON C.[object_id]=IDX.[object_id] "); 
  77. strSql.Append("AND C.column_id=IDX.column_id  "); 
  78. strSql.Append("WHERE O.name=N'" + TableName + "' "); 
  79. 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（）函数返回映射后的数据类型。

  1. public class TypeMap 
  2. { 
  3. //获取数据库（SqlServer和SqlCE）字段类型映射 
  4. private static string datafile =
  5.  System.Windows.Forms.Application.StartupPath + "\\TypeMap.txt"; 
  6.     
  7. //加载数据类型映射表 
  8. private static  string[] LoadTypeMap() 
  9. { 
  10. string s = string.Empty; 
  11. if (File.Exists(datafile)) 
  12. { 
  13. StreamReader sr = new StreamReader(datafile, Encoding.UTF8); 
  14. //读取数据到字符串中 
  15. s = sr.ReadToEnd(); 
  16. sr.Close(); 
  17. } 
  18. //替换掉字符串中的回车换行符 
  19. s= s.Replace("\r\n", ""); 
  20. return s.Split(';'); 
  21. } 
  22.  
  23. /// <summary> 
  24. /// 获取数据库（SqlServer和SqlCE）字段类型映射 
  25. /// </summary> 
  26. /// <param name="sourceType">SqlServer数据类型</param> 
  27. /// <returns>SqlCE数据类型</returns> 
  28. public static string GetMapedDataType(string sourceType) 
  29. { 
  30. string[] ss = TypeMap.LoadTypeMap(); 
  31. //在映射表中查找原始类型映射后的数据类型 
  32. foreach (string s in ss) 
  33. { 
  34. string[] sss = s.Split('='); 
  35. if (sss[0].ToLower() == sourceType.ToLower()) 
  36. { 
  37. //如果找到，则返回映射后的类型 
  38. return sss[1]; 
  39. } 
  40. } 
  41. //找不到，返回输入的类型 
  42. return sourceType; 
  43. } 
  44. } 

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

  1. StringBuilder strclass = new StringBuilder(); 
  2.  //strclass.AppendLine("if exists (select * from
  3.  //sysobjects where id = OBJECT_ID('[" + tablename + "]')
  4.  //and OBJECTPROPERTY(id, 'IsUserTable') = 1) "); 
  5.  //strclass.AppendLine("DROP TABLE [" + tablename + "]"); 
  6.  
  7.  //string PKfild = "";//主键字段 
  8.  //bool IsIden = false;//是否是标识字段 
  9.  StringBuilder ColdefaVal = new StringBuilder();//字段的默认值列表           
  10.  
  11.  Hashtable FildtabList = new Hashtable();//字段列表（字段名，字段类型） 
  12.  StringBuilder FildList = new StringBuilder();//字段列表 
  13.  
  14. DataTable dt = （1中语句查询后得到的表）; //包含表详细信息的DataTable 
  15.  
  16.  //开始生成创建表语句。 
  17.  //建表第一句…..终于开始了 
  18.  strclass.AppendLine("CREATE TABLE [" + tablename + "] ("); 
  19.  
  20.  if (dt != null) 
  21.  { 
  22.      DataRow[] dtrows; 
  23.      dtrows = dt.Select();  //这里可以做点简单的筛选 
  24.  
  25.      ////也可以这样 
  26.      //foreach (DataRow row in dt.Rows) 
  27.      //{ 
  28.      //   //。。。。。。。  
  29.      //} 
  30.  
  31.      foreach (DataRow row in dtrows) 
  32.      { 
  33. string columnName = row["ColumnName"].ToString(); 
  34. string columnType = TypeMap.GetMapedDataType(row["TypeName"].ToString());
  35. //转换后的数据类型 
  36.  
  37. string IsIdentity = row["IsIdentity"].ToString(); 
  38. string Length = row["Length"].ToString(); 
  39. string Preci = row["Preci"].ToString(); 
  40. string Scale = row["Scale"].ToString(); 
  41. string ispk = row["isPK"].ToString(); 
  42. string isnull = row["cisNull"].ToString(); 
  43. string defaultVal = row["defaultVal"].ToString(); 
  44.  
  45. strclass.Append("[" + columnName + "] [" + columnType + "] "); 
  46.  
  47. switch (columnType.Trim()) 
  48. { 
  49.   case "varchar": 
  50.   case "char": 
  51.   case "nchar": 
  52.   case "binary": 
  53.   case "nvarchar": 
  54.   case "varbinary": 
  55.       { 
  56.  string len = this.GetDataTypeLenVal(columnType.Trim(), Length);
  57. //获取字符串长度（注2） 
  58.  strclass.Append(" (" + len + ")"); 
  59.       } 
  60.       break; 
  61.   case "decimal": 
  62.   case "numeric": 
  63.       strclass.Append(" (" + Preci + "," + Scale + ")"); 
  64.       break; 
  65. } 
  66. if (isnull == "√") 
  67. { 
  68.   strclass.Append(" NULL"); 
  69. } 
  70. else 
  71. { 
  72.   strclass.Append(" NOT NULL"); 
  73. } 
  74. if (defaultVal != "") 
  75. { 
  76.   strclass.Append(" DEFAULT " + defaultVal); 
  77. } 
  78. strclass.AppendLine(","); 
  79.  
  80. FildtabList.Add(columnName, columnType); 
  81. FildList.Append("[" + columnName + "],"); 
  82.  
  83. //if(defaultVal!="") 
  84. //{ 
  85. //  ColdefaVal.Append("CONSTRAINT [DF_"+tablename+"_"+columnName+"]
  86. // DEFAULT "+defaultVal+" FOR ["+columnName+"],"); 
  87. //} 
  88.  
  89. //if((ispk=="√")&&(PKfild=="")) 
  90. //{                      
  91. //    PKfild=columnName;//得到主键 
  92. //} 
  93.      } 
  94.  } 
  95.  string s = strclass.ToString(); 
  96.  
  97.             //去掉strclass（）中最后一个豆号 
  98.  int n = s.LastIndexOf(','); 
  99.  string ss = s.Substring(0, n) + ")"; 
  100.  strclass.Remove(0, strclass.ToString().Length); 
  101.  strclass.Append(ss); 
  102.  strclass.Append(";");//添加一个分号（;）用于分割字符串 
  103.  strclass.AppendLine(""); 
  104.  strclass.AppendLine(""); 
  105.  
  106.             //去掉FildList（字段列表）中最后一个豆号 
  107.  s = FildList.ToString(); 
  108.  n = s.LastIndexOf(','); 
  109.  ss = s.Substring(0, n); 
  110.  FildList.Remove(0, FildList.ToString().Length); 
  111.  FildList.Append(ss); 
  112.  
  113.  //开始获取表中数据，dtdata中包含了查询数据库得到的数据 
  114.  DataTable dtdata = this.getData(dbname,tablename); 
  115.  //生成插入数据语句 
  116.  if (dtdata != null) 
  117.  { 
  118.      foreach (DataRow row in dtdata.Rows)//循环表数据 
  119.      { 
  120. StringBuilder strfild = new StringBuilder(); 
  121. StringBuilder strdata = new StringBuilder(); 
  122. //字段数组 
  123. string[] split = FildList.ToString().Split(new Char[] { ',' }); 
  124.  
  125. foreach (string fild in split)//循环一行数据的各个字段 
  126. { 
  127.   string colname = fild.Substring(1, fild.Length - 2); 
  128.   string coltype = ""; 
  129.   foreach (DictionaryEntry myDE in FildtabList) 
  130.   { 
  131.       if (myDE.Key.ToString() == colname) 
  132.       { 
  133.  coltype = myDE.Value.ToString(); 
  134.       } 
  135.   } 
  136.   string strval = ""; 
  137.   switch (coltype) 
  138.   { 
  139.       case "binary": 
  140.  { 
  141.    //我的数据库中没有binary型数据，这里没有处理; 
  142.  } 
  143.  break; 
  144.       case "bit": 
  145.  { 
  146.    strval = (row[colname].ToString().ToLower() == "true") ? "1" : "0"; 
  147.  } 
  148.  break; 
  149.       default: 
  150.  strval = row[colname].ToString().Trim().Replace(';','；'); 
  151.  break; 
  152.   } 
  153.   strdata.Append("'" + strval.Replace('\'', '°') + "',"); //字段中剔除分号“’” 
  154.   strfild.Append("[" + colname.Replace('\'', '°') + "],");//数据中剔除分号“’” 
  155.                      
  156. } 
  157.                     //还是三删除最后一个逗号 
  158. n = strdata.ToString().LastIndexOf(','); 
  159. ss = strdata.ToString().Substring(0, n); 
  160. strdata.Remove(0,strdata.ToString().Length); 
  161. strdata.Append(ss); 
  162.  
  163. n = strfild.ToString().LastIndexOf(','); 
  164. ss = strfild.ToString().Substring(0, n); 
  165. strfild.Remove(0, strfild.ToString().Length); 
  166. strfild.Append(ss); 
  167.  
  168.  
  169. //导出数据INSERT语句 
  170. strclass.Append("INSERT [" + tablename + "] ("); 
  171. strclass.Append(strfild.ToString());  //插入字段集 
  172. strclass.Append(") VALUES ( "); 
  173. strclass.Append(strdata.ToString().Replace(';','；');
  174. //数据值。并将英文分号替换为全角分号。 
  175. strclass.AppendLine(");"); 
  176.                 //一条插入语句完成 
  177.      } 
  178.  } 
  179.          
  180.  //删除语句中最后一个分号 
  181.  n = strclass.ToString().LastIndexOf(';'); 
  182.  ss = strclass.ToString().Substring(0,n); 
  183.  strclass.Remove(0, strclass.ToString().Length); 
  184.  strclass.Append(ss); 
  185.  
  186.  return strclass.ToString(); 
  187. }  

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

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

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

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

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

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

using System.Data.SqlServerCe;

开始：

  1. public class SDF 
  2. { 
  3. string fileName = "C:\\123.sdf"; //要创建的SQLCE数据库位置 
  4. public SDF(string file) 
  5. { 
  6. fileName = file; 
  7. } 
  8. /// <summary> 
  9. /// 创建数据库函数 
  10. /// </summary> 
  11. /// <param name="file"></param> 
  12. private void CreateDB(string file) 
  13. { 
  14. fileName = file; 
  15. //SQLCE连接字符串 
  16. string connStr = "Data Source=" + file; 
  17. try 
  18. { 
  19. if (File.Exists(file)) 
  20. { 
  21. File.Delete(file); 
  22. } 
  23. //在指定位置创建数据库 
  24. SqlCeEngine eng = new SqlCeEngine(); 
  25. eng.LocalConnectionString = connStr; 
  26. eng.CreateDatabase(); 
  27. } 
  28. catch (Exception exp) 
  29. { 
  30. throw exp; 
  31. } 
  32. } 
  33.  
  34. //执行传入的脚本，在SqlCE数据库中创建表并插入数据 
  35. private void CreateTab(string ScriptStr) 
  36. { 
  37. string connStr = "Data Source=" + fileName; 
  38. string[] ss = ScriptStr.Split(';');//分割脚本语句。 
  39. using (SqlCeConnection conn = new SqlCeConnection(connStr)) 
  40. { 
  41. //ＳＱＬＣＥ中逐条执行语句。。。 
  42. SqlCeCommand cmd = new SqlCeCommand(); 
  43. cmd.Connection = conn; 
  44. conn.Open(); 
  45. foreach (string sqlstr in ss) 
  46. { 
  47. cmd.CommandText = sqlstr;//循环 
  48. cmd.ExecuteNonQuery(); 
  49. } 
  50. conn.Close(); 
  51. } 
  52. } 
  53.  
  54. //通过文件名和脚本文件组装数据库 
  55. public void Create(string File, string Script) 
  56. { 
  57. this.CreateDB(File); 
  58. this.CreateTab(Script); 
  59. } 
  60. } 

在程序中调用方法

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

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

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

clip_image004

6、总结

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

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

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

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

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

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

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

(hanshuyujifen)
本站文章除注明转载外，均为本站原创或编译欢迎任何形式的转载，但请务必注明出处，尊重他人劳动，同学习共成长。转载请注明：文章转载自：罗索实验室 [http://www.rosoo.net/a/201007/9791.html]
本文出处：博客园　作者：hanshuyujifen
顶一下
(2)
100%
踩一下
(0)
0%
------分隔线----------------------------
相关文章
发表评论
请自觉遵守互联网相关的政策法规，严禁发布色情、暴力、反动的言论。
评价:
表情:
用户名: 验证码:点击我更换图片
最新评论 进入详细评论页>>
栏目列表
将本文分享到微信
织梦二维码生成器
推荐内容
热点内容