C# web项目中sql数据库转sqlite数据库

最近做了一个小网站，用到了一个使用sql server 2005的.net cms系统，但是现在我所买虚拟主机的服务商，不给虚拟主机提供sql server服务了，那就转数据库吧，转啥好呢，思来想去，access？刚入行时候用了很久，简单够用，不过实在提不起兴趣了，sqlite？嗯...还没用过，只是简单看过介绍，听说性能还不错，那就试试吧，等等，不知道虚拟主机支持不支持？！百度！然而一大堆没啥用处的提问和回答，也许可能大概是我搜索的关键词不对，懒得管了，年龄大了，没有那个劲儿了，实践出真理，先上手试试验证一下吧，说干就干

先查查怎么在本地创建和管理数据库，然后选择使用了sqlitestudio这个软件，然后新建个test数据库->随便插条数据->然后在vs创建个test web项目->数据库文件扔进去->新建个页面，查下数据显示到页面->本地运行，ok！，发布，->上传虚拟主机，怀着稍稍激动的心情，打开网址，ok！完全可以！

这么简单吗？nonono，运行之前还是有点小小的障碍的：

首先项目需要用到system.data.sqlite.dll，到sqlite官网下一个吧，

然后添加引用，引用之后，还需要连接字符串，搜索(ss)! 嗯，和access很像，附个例子：

<add name="connectionstring" connectionstring="data source=|datadirectory|testdb.db;version=3;pooling=true;failifmissing=false" providername="system.data.sqlite" />

这样就可以运行了！具体的参数还有不少，ss一下，根据需求自己设置。

然后开始改cms吧，首先是转数据库，一看表，我尼玛，好多表，自己一个一个建吗？想想都想打消折腾的念头了，有没有什么工具可以借助呢？ss一下，还真有！

sql server to sqlite db convert 这是一位叫liron.levi老外写的，项目地址：https://www.codeproject.com/articles/26932/convert-sql-server-db-to-sqlite-db

简直神器，界面如下

 作者还给出了源代码，在源代码中可以看到数据类型对应的转换

        /// <summary>
        /// used when creating the create table ddl. creates a single row
        /// for the specified column.
        /// </summary>
        /// <param name="col">the column schema</param>
        /// <returns>a single column line to be inserted into the general create table ddl statement</returns>
        private static string buildcolumnstatement(columnschema col, tableschema ts, ref bool pkey)
        {
            stringbuilder sb = new stringbuilder();
            sb.append("\t[" + col.columnname + "]\t");

            // special treatment for identity columns
            if (col.isidentity)
            {
                if (ts.primarykey.count == 1 && (col.columntype == "tinyint" || col.columntype == "int" || col.columntype == "smallint" ||
                    col.columntype == "bigint" || col.columntype == "integer"))
                {
                    sb.append("integer primary key autoincrement");
                    pkey = true;
                }
                else
                    sb.append("integer");
            }
            else
            {
                if (col.columntype == "int")
                    sb.append("integer");
                else
                {
                    sb.append(col.columntype);
                }
                if (col.length > 0)
                    sb.append("(" + col.length + ")");
            }
            if (!col.isnullable)
                sb.append(" not null");

            if (col.iscasesensitivite.hasvalue && !col.iscasesensitivite.value)
                sb.append(" collate nocase");

            string defval = stripparens(col.defaultvalue);
            defval = discardnational(defval);
            _log.debug("default value before [" + col.defaultvalue + "] after [" + defval + "]");
            if (defval != string.empty && defval.toupper().contains("getdate"))
            {
                _log.debug("converted sql server getdate() to current_timestamp for column [" + col.columnname + "]");
                sb.append(" default (current_timestamp)");
            }
            else if (defval != string.empty && isvaliddefaultvalue(defval))
                sb.append(" default " + defval);

            return sb.tostring();
        }

然后就用这个工具把cms的sql server数据库转换成sqlite数据库文件，直接扔进cms项目中。

这样就行了吗？当然还有工作要做，原来项目中操作sql server的类库也要换成sqlite的，sql语句也要做相应的转换，下面就挑一些重点的说一说：

先说数据类型吧：

由于在工具的源代码中，主键不管什么类型的int转成sqlite都用的integer，有需求的可以自己改代码，不过最好要熟悉sqlite的数据类型。下面列出我在项目中所遇到的主要类型转换

sql server	c#	sqlite	c#
int	new sqlparameter("@id", sqldbtype.int,4)	integer	new sqliteparameter("@id", dbtype.int64,8)
nvarchar	new sqlparameter("@id", sqldbtype.nvarchar,50)	nvarchar	new sqliteparameter("@id", dbtype.string,50)
decimal	new sqlparameter("@id", sqldbtype.decimal)	numeric	new sqliteparameter("@id", dbtype.decimal)
tinyint	new sqlparameter("@id", sqldbtype.tinyint,1)	smallint	new sqliteparameter("@id", dbtype.int16,1)
ntext	new sqlparameter("@id", sqldbtype.ntext)	text	new sqliteparameter("@id", dbtype.string)
datetime	new sqlparameter("@id", sqldbtype.datetime)	datetime	new sqliteparameter("@id", dbtype.datetime)
image	new sqlparameter("@fs", sqldbtype.image)	binary	new sqliteparameter("@fs", dbtype.binary)

 

代码数据类型转换之后，就剩调试修改sql语句了，下面列出我在项目中遇到的比较主要的转换

1、top语句，在sqlite中要使用limit，和mysql差不多，例如

　　sql：select top 10 * from table_1

　　sqlite：select * from table_1 limit 10

2、在插入一条数据后，要获取最新的id

　　sql：select @@identity;

　　sqlite：select last_insert_rowid();

3、计算时间差

　　sql：where datediff(d,field_time,getdate())>=0

　　sqlite：where julianday(datetime('now','localtime'))-julianday(field_time)>=0

4、分页

　　sql：2005以上一般使用row_number()　　

　　　　select * from ( select row_number() over(order by id) as rows,* ) as t where rows between pageindex*pagesize and pageindex*pagesize+pagesize

　　sqlite：用 limit  offset

　　　　select * from table_1 limit pagesize offset (pageindex- 1) * pagesize 

5、最让人抓狂的是修改表部分的差异

　　这一部分单独再写一篇吧

时间不早了，来自老年人的叹息.....