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、最让人抓狂的是修改表部分的差异
这一部分单独再写一篇吧
时间不早了,来自老年人的叹息.....