我想将数据库从SQL服务器转换为访问 [英] I want to convert the database from SQL server to access

问题描述

我想将数据库从SQL Server转换为Access。



我有一个包含SQL Server，ASP.NET和C＃的网站，以及我想将数据库从SQL Server转换为Access而不会丢失网站上的任何内容。



我该怎么做？



我尝试了什么：



我尝试用ole db建立连接字符串，但代码是插入和更新不同于SQL服务器



我只想更改连接

并且代码保持原样

解决方案

我建​​议这可能是一个错误。

这是一个网站：这总是意味着需要多用户访问数据库，有时是大规模的多用户。 SQL Server擅长这一点。



访问权限......并不擅长。事实上，我不使用可怕这个词的唯一原因是可以使用脏话。



Access是一个可以使用的单个用户数据库 - 以有限的方式 - 用于多用户访问;但它永远都是PITA，并且总会给你一些难以解决的间歇性问题。一旦用户数量开始上升，您就不会相信性能问题。



即使这样，它也不仅仅是更改连接字符串的情况，除非你的应用程序是从头开始编写的，并且考虑到单个文件的多用户访问权限。一些语法不同（SQL Server使用CASE，Access使用IIF），Access DB比SQL Server小，...



说真的，我不会甚至不考虑网站的Access DB。仅限SQL Server或MYSql！

Quote：

我只想更改连接

你不能。如果您的代码使用的是SqlConnection等对象，则您的站点只能与SQL Server一起使用。如果您想使用Oledb，则需要将所有SqlConnection更改为OleDbConnection，依此类推。如果您使用的是硬编码的SQL，那么您也需要更改它以符合ole db。如果您正在使用存储过程，则需要将它们转换为访问查询，除非您的存储过程非常基本，否则您也会遇到这种情况，并且可能需要重新编写部分代码才能适应。



如果您正在使用实体框架，那么对您来说情况会更糟。



简短的回答是什么你想做的事是不可能的。它涉及多少努力取决于你如何编写我们不知道的网站。如果您确实要将此转换为Access，那么建议您不要将站点从紧密耦合到SQL Server更改为与Access紧密耦合，而应该编写一个允许您使用的抽象层几乎在SQL和Access之间切换，如果需要，甚至可以轻松切换到MySql。

Quote：

我只想更改连接

并且代码保持原样

不可能。



您已经使用特定于SQL Server，SqlConnection，SqlCommand，SqlDataAdapter，... Sqlxxxx等的对象编写了针对SQL Server的代码。



您必须更改所有代码以使用Access，它使用OleDbConnection，OleDbCommand，OleDbDataAdapter，...，OleDbxxxx。



哦，并且不要我只想找到并替换Sqlwi OleDb。这也不会为你节省任何工作。 SQL语句本身也可能需要重写，参数类型也会改变。





现在，如果你使用了Entity Framework来实现处理所有数据库活动，根本不能使用Access。它不受支持。





访问不是SQL的完整实现。它是TSQL的子集，具有特定于Access的扩展。

I want to convert the database from SQL Server to Access.

I have a web site with SQL Server, ASP.NET and C#, and I want to convert the database from SQL Server to Access without losing anything on the website.

How can I do that ?

What I have tried:

I tried make connection string with ole db but the code of insert and update is different from SQL server

I just want to change the connection only
and the code remain as it is

解决方案

I'd suggest that is probably a mistake.
This is a website: which always implies multiuser access to the DB is required, sometimes massively multiuser. SQL Server is good at that.

Access is ... not good at that. In fact, the only reason I don't use the word "terrible" is that swear words are available.

Access is a single user database that can be used - in a limited way - for multiuser access; but it will always be a PITA, and will always give you intermittent problems that are vary hard to solve. And performance problems you won't believe once the number of users starts to rise.

Even then, it isn't just a case of changing the connection string, unless you app has been written from scratch with multiuser access to a single file in mind. Some of the syntax is different (SQL Server uses CASE, Access uses IIF for example), Access DB's are smaller than SQL Server, ...

Seriously, I wouldn't even consider an Access DB for a website. SQL Server or MYSql only!

Quote:

I just want to change the connection only

You can't. If your code is using objects like SqlConnection etc then your site can only work with SQL Server. If you want to use Oledb instead you'll need to change all of your SqlConnection to OleDbConnection and so on. If you are using hard-coded SQL then you'll need to change that too to be compliant with ole db. If you are using Stored Procedures you'll need to convert those to Access Queries and unless your stored procs are incredibly basic, you'll struggle with that too and will maybe have to re-write parts of your code to accommodate.

If you're using Entity Framework then it gets even worse for you.

The short answer is that what you want to do is impossible. How much effort it will involve depends on how you've written the site which we don't know. If you are indeed going to take on the effort of converting this to Access then as suggested you shouldn't change the site from being tightly coupled to SQL Server to being tightly coupled to Access, instead you should write an abstraction layer that allows you to pretty much switch between SQL and Access, or even easily switch to MySql if that becomes a requirement.

Quote:

I just want to change the connection only
and the code remain as it is

Impossible.

You've written your code against SQL Server using objects that are specific to SQL Server, SqlConnection, SqlCommand, SqlDataAdapter, ... Sqlxxxx, whatever.

You have to change ALL of that code to use Access instead, which uses OleDbConnection, OleDbCommand, OleDbDataAdapter, ..., OleDbxxxx.

Oh, and don't get the idea to just search and replace "Sql" with "OleDb". That's not going to save you any work either. The SQL statements themselves may also have to be rewritten, and parameter types changed.


Now, if you've used Entity Framework to handle all of your database activity, you cannot use Access at all. It's not supported.


Access is not a full implementation of SQL. It's a subset of TSQL, with extensions specific to Access.

这篇关于我想将数据库从SQL服务器转换为访问的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！