C# 创建和连接到 SQL Server 数据库的代码：它有什么问题？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/9321206/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Code to create & connect to a SQL Server database: Whats wrong with it?
提问by sazr
I am new to C# & I am trying to programatically create & open a SQL Server database.
我是 C# 新手，我正在尝试以编程方式创建和打开 SQL Server 数据库。
I have a ASP.NET webapp I am creating & on page load it should pull some data from the database (if the db doesn't exist, it should be created & populated with default data).
我有一个 ASP.NET webapp 我正在创建 & 在页面加载时它应该从数据库中提取一些数据（如果数据库不存在，它应该被创建并用默认数据填充）。
PS: does C#'s System.Data.SqlClientuse MySQL or SQLite or something else?
PS：C#System.Data.SqlClient使用 MySQL 或 SQLite 还是其他什么？
Right now I am unsure if my code correctly creates a SQL Server database & if I connect to it correctly.
现在我不确定我的代码是否正确创建了 SQL Server 数据库以及我是否正确连接到它。
Can you tell me if my code is correct & how I could improve it?
你能告诉我我的代码是否正确以及我如何改进它？
UPDATE:Error is
更新：错误是
"A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server)"}"
“建立与 SQL Server 的连接时发生与网络相关或特定于实例的错误。未找到或无法访问服务器。请验证实例名称是否正确以及 SQL Server 是否配置为允许远程连接。（提供者：命名管道提供程序，错误：40 - 无法打开与 SQL Server 的连接)"}"
I have indicated where in the code below the error occurs.
我已经在下面的代码中指出了错误发生的位置。
Creating a SQL Server database:
创建 SQL Server 数据库：
// When I run this function no file seems to be created in my project directory?
// Although there is a ASPNETDB sql database file in my App_Data folder so this maybe it
public static string DEF_DB_NAME = "mydb.db"; // is this the correct extension?
private bool populateDbDefData()
{
bool res = false;
SqlConnection myConn = new SqlConnection("Server=localhost;Integrated security=SSPI;database=master");
string str = "CREATE DATABASE "+DEF_DB_NAME+" ON PRIMARY " +
"(NAME = " + DEF_DB_NAME + "_Data, " +
"FILENAME = " + DEF_DB_NAME + ".mdf', " +
"SIZE = 2MB, MAXSIZE = 10MB, FILEGROWTH = 10%) " +
"LOG ON (NAME = " + DEF_DB_NAME + "_Log, " +
"FILENAME = " + DEF_DB_NAME + "Log.ldf', " +
"SIZE = 1MB, " +
"MAXSIZE = 5MB, " +
"FILEGROWTH = 10%)";
SqlCommand myCommand = new SqlCommand(str, myConn);
try
{
myConn.Open(); // ERROR OCCURS HERE
myCommand.ExecuteNonQuery();
insertDefData(myConn);
}
catch (System.Exception ex)
{
res = false;
}
finally
{
if (myConn.State == ConnectionState.Open)
myConn.Close();
res = true;
}
return res;
}
Here's my connection to the SQL Server database code: I am pretty sure it fails to connect - if I try to use the variable conn, it says the connection is not open. Which could mean that I either failed to connect or failed to even create the db in the 1st place:
这是我与 SQL Server 数据库代码的连接：我很确定它无法连接 - 如果我尝试使用变量 conn，它表示连接未打开。这可能意味着我要么无法连接，要么甚至无法在第一名创建数据库：
private bool connect()
{
bool res = false;
try
{
conn = new SqlConnection("user id=username;" +
"password=password;" +
"Server=localhost;" +
"Trusted_Connection=yes;" +
"database="+DEF_DB_NAME+"; " +
"connection timeout=30");
conn.Open();
return true;
}
catch (Exception e)
{
}
return false;
}
回答by user978122
PS: does C#'s System.Data.SqlClient use MySQL or SQLite or something else?
PS：C# 的 System.Data.SqlClient 是否使用 MySQL 或 SQLite 或其他东西？
MySQL provides their own C# dll for connecting to their database, as do the majority of other database manufacturers. I recommend using theirs. I typically using the built-in SQL client for MS SQL (I am not aware if it can be used with other DBs).
MySQL 提供了自己的 C# dll 用于连接到他们的数据库，大多数其他数据库制造商也是如此。我建议使用他们的。我通常使用 MS SQL 的内置 SQL 客户端（我不知道它是否可以与其他数据库一起使用）。
As for this line: insertDefData(myConn) -> the method is not included in your code sample.
至于这一行： insertDefData(myConn) -> 该方法未包含在您的代码示例中。
As for SQL debugging in general, use the GUI for debugging. I know a lot of people who grew up on MySQL do not want to, or do not understand why you should use one, but it's really a good idea. If you are connecting to MySQL, I recommend MySQL WorkBench CE; if you are connecting to a MS database, SQL Management Studio is what you want. For others, GUIs should be available. The idea here is that you can selectively highlight sections of your query and run it, something not available via command-line. You can have several queries, and only highlight the ones you want to run. Plus, exploring the RDBMS is easier via a GUI.
对于一般的 SQL 调试，使用 GUI 进行调试。我知道很多在 MySQL 上长大的人不想，或者不明白为什么要使用 MySQL，但这确实是一个好主意。如果你连接到 MySQL，我推荐 MySQL WorkBench CE；如果您要连接到 MS 数据库，SQL Management Studio 就是您想要的。对于其他人，应该可以使用 GUI。这里的想法是您可以有选择地突出显示查询的部分并运行它，这是命令行无法提供的。您可以有多个查询，并且只突出显示您想要运行的那些。此外，通过 GUI 可以更轻松地探索 RDBMS。
And if you want to prevent a SQL injection attack, just Base64 encode the string data going in.
如果您想防止 SQL 注入攻击，只需对输入的字符串数据进行 Base64 编码。
As for the connection string itself, we will need some more data on what type of database, exactly, you are trying to connect to. Personally, I recommend just creating a separate SQL account to handle operations (easier to track, and you only need to give it permissions to what you want it to access; security and all that).
至于连接字符串本身，我们将需要更多有关您尝试连接的数据库类型的更多数据。就我个人而言，我建议只创建一个单独的 SQL 帐户来处理操作（更容易跟踪，您只需要为其授予您希望它访问的权限；安全性等等）。
回答by nbrooks
You have probably already got this figured out, but just in case people end up here with the same problem (like I did) here's how I got this working.
您可能已经弄清楚了这一点，但以防万一人们最终遇到同样的问题（就像我一样），这就是我如何工作的。
Your error is that the SqlConnection is not being opened, because it isn't finding an appropriate server. If you're using the SQL server express edition (as I am) you should set the SqlConnection object like this:
您的错误是 SqlConnection 没有被打开，因为它没有找到合适的服务器。如果您使用的是 SQL server express edition（就像我一样），您应该像这样设置 SqlConnection 对象：
SqlConnection myConn = new SqlConnection("Server=localhost\SQLEXPRESS;Integrated security=SSPI;database=master;");
Once you resolve that error though, you are going to fail on the next line when you try to execute the query. The "Filename" needs to be separated by single quotes, but you only have one on the end after the extension; you will also need one before.
但是，一旦您解决了该错误，当您尝试执行查询时，您将在下一行失败。“文件名”需要用单引号分隔，但扩展名后面只有一个；你之前也需要一个。
Also, that is the full physical file path, and it won't use the current directory context, you have to specify a path. Make sure that the location is one which the db server will have access to when it's running, otherwise you will get a SqlException being thrown with an error message along the lines of:
此外，这是完整的物理文件路径，它不会使用当前目录上下文，您必须指定一个路径。确保该位置是 db 服务器在运行时可以访问的位置，否则您将收到 SqlException 并带有以下行的错误消息：
Directory lookup for the file "...\filename.mdf" failed with the operating system error 5 (Access is denied). CREATE DATABASE failed. Some file names listed could not be created.
文件“...\filename.mdf”的目录查找失败，出现操作系统错误 5（访问被拒绝）。创建数据库失败。无法创建列出的某些文件名。
The code which I ended up using looks like this:
我最终使用的代码如下所示：
public static string DB_NAME = "mydb"; //you don't need an extension here, this is the db name not a filename
public static string DB_PATH = "C:\data\";
public bool CreateDatabase()
{
bool stat=true;
string sqlCreateDBQuery;
SqlConnection myConn = new SqlConnection("Server=localhost\SQLEXPRESS;Integrated security=SSPI;database=master;");
sqlCreateDBQuery = " CREATE DATABASE "
+ DB_NAME
+ " ON PRIMARY "
+ " (NAME = " + DB_NAME + "_Data, "
+ " FILENAME = '" + DB_PATH + DB_NAME + ".mdf', "
+ " SIZE = 2MB,"
+ " FILEGROWTH = 10%) "
+ " LOG ON (NAME =" + DB_NAME + "_Log, "
+ " FILENAME = '" + DB_PATH + DB_NAME + "Log.ldf', "
+ " SIZE = 1MB, "
+ " FILEGROWTH = 10%) ";
SqlCommand myCommand = new SqlCommand(sqlCreateDBQuery, myConn);
try
{
myConn.Open();
myCommand.ExecuteNonQuery();
}
catch (System.Exception)
{
stat=false;
}
finally
{
if (myConn.State == ConnectionState.Open)
{
myConn.Close();
}
myConn.Dispose();
}
return stat;
}
回答by Sasika Miyuran
Use this code,
使用此代码，
internal class CommonData
{
private static SqlConnection conn;
public static SqlConnection Connection
{
get { return conn; }
}
public static void ReadyConnection()
{
conn = new SqlConnection();
conn.ConnectionString = ConfigurationManager.ConnectionStrings["DBConnectionString"].ToString();
if (conn.State != ConnectionState.Open)
{
conn.Open();
}
}
public static int ExecuteNonQuery(SqlCommand command)
{
try
{
ReadyConnection();
command.Connection = conn;
int result = command.ExecuteNonQuery();
return result;
}
catch (Exception ex)
{
throw ex;
}
finally
{
command.Dispose();
if (conn.State == ConnectionState.Open) conn.Close();
conn.Dispose();
}
}
public static SqlDataReader ExecuteReader(SqlCommand command)
{
try
{
ReadyConnection();
command.Connection = conn;
SqlDataReader result = command.ExecuteReader(CommandBehavior.CloseConnection);
return result;
}
catch (Exception Ex)
{
throw Ex;
}
}
public static object ExecuteScalar(SqlCommand command)
{
try
{
ReadyConnection();
command.Connection = conn;
object value = command.ExecuteScalar();
if (value is DBNull)
{
return default(decimal);
}
else
{
return value;
}
}
catch (Exception ex)
{
throw ex;
}
}
public static void ClearPool()
{
SqlConnection.ClearAllPools();
}
}
