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官网下一个吧,http://system.data.sqlite.org/index.html/doc/trunk/www/downloads.wiki

然后添加引用,引用之后,还需要连接字符串,搜索(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();
        }
View Code

然后就用这个工具把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、最让人抓狂的是修改表部分的差异

  这一部分单独再写一篇吧

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

第二天了,附上修改表的文章链接:sqlite修改表、表字段等与sql server的不同之处

 

posted @ 2019-11-16 21:24  伊禾棵  阅读(1316)  评论(5编辑  收藏  举报