code创建和放大器;连接到SQL Server数据库：什么错呢？ [英] Code to create & connect to a SQL Server database: Whats wrong with it?
问题描述
我是新的C＃和放大器;我想以编程方式创建和放大器;打开一个SQL Server数据库。
我有一个ASP.NET web应用程序，我创建和放大器;在页面加载应该从数据库拉一些数据（如果该数据块不存在，应该创建＆放大器;填充有默认数据）。
PS：不C＃的 System.Data.SqlClient的使用MySQL或SQLite的或别的东西。
现在我不能确定，如果我的code正确地创建一个SQL Server数据库和放大器;如果我连接到它正确。
你能告诉我，如果我的code是正确的和放;我怎么能提高呢？
更新：错误是
发生网络相关的或特定于实例的错误，
与SQL Server建立连接。服务器没有被发现或
无法访问。验证实例名称是否正确，以及
SQL Server配置为允许远程连接。 （provider：命名
管道提供程序，错误：40 - 无法打开到SQL连接
服务器）}我已表明其中误差低于code发生。
创建一个SQL Server数据库：
//当我运行这个功能没有文件似乎在我的项目目录中创建？
//虽然在我的App_Data文件夹一个ASPNETDB SQL数据库文件，所以这也许是
公共静态字符串DEF_DB_NAME =mydb.db; //这是正确的扩展名？
私人布尔populateDbDefData（）
{
布尔解析度= FALSE;
的SqlConnection的myconn =新的SqlConnection（服务器=本地主机;集成安全性= SSPI;数据库高手）;
字符串str =CREATE DATABASE+ DEF_DB_NAME +ON PRIMARY+
（NAME =+ DEF_DB_NAME +_Data，+
文件名=+ DEF_DB_NAME +密度纤维板，+
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 =新的SqlCommand（STR，的myconn）;
尝试
{
myConn.Open（）; //错误这里发生
myCommand.ExecuteNonQuery（）;
insertDefData（的myconn）;
}
赶上（System.Exception的前）
{
RES = FALSE;
}
最后
{
如果（myConn.State == ConnectionState.Open）
myConn.Close（）;
RES = TRUE;
} 返回水库;
}下面是我的SQL Server数据库code连接：我是pretty确保它无法连接 - 如果我尝试使用变量康恩，它说的连接未打开。这可能意味着，我要么无法接通或甚至未能创造了第一名的DB：
私人布尔连接（）
{
布尔解析度= FALSE;
尝试
{
康恩=新的SqlConnection（用户ID =用户名;+
密码=密码; +
服务器=本地主机; +
Trusted_Connection =是; +
数据库=+ DEF_DB_NAME +，+
连接超时= 30）;
conn.Open（）;
返回true;
}
赶上（例外五）
{
} 返回false;
}
解决方案您可能已经得到这个想通了，但以防万一人最终在这里同样的问题（像我一样），这里就是我得到了这个工作。
您错误是SqlConnection的没有被打开时，因为它是没有找到适当的服务器。如果您在使用SQL服务器前preSS版本（如我），你应该设置这样的SqlConnection对象：
的SqlConnection的myconn =新的SqlConnection（服务器=本地主机\\\\ SQLEX $ P $干燥综合征;集成安全性= SSPI;数据库=主;）;一旦你解决这个错误，虽然，你是要失败的下一行，当您尝试执行查询。在文件名需要用单引号分隔，但你只能有一个在延长后结束;你还需要之前之一。
此外，这是完整的物理文件路径，它不会使用当前目录下，你必须指定一个路径。确保位置是其中一个数据库服务器将有机会时，它的运行，否则你将得到一个抛出SQLException与沿线的错误消息：
有关文件目录查找\\ filename.mdf与操作系统错误5失败（访问被拒绝）。 CREATE DATABASE失败。列出的某些文件名不能创建。
在code，我结束了使用看起来像这样：
公共静态字符串DB_NAME =MYDB; //你不需要这里的延伸，这是数据库名称不是文件名
公共静态字符串DB_PATH =C：\\\\ \\\\的数据;公共BOOL的CreateDatabase（）
{
布尔STAT = TRUE;
串sqlCreateDBQuery;
的SqlConnection的myconn =新的SqlConnection（服务器=本地主机\\\\ SQLEX $ P $干燥综合征;集成安全性= SSPI;数据库=主;）; sqlCreateDBQuery =CREATE DATABASE
+ DB_NAME
+ON PRIMARY
+（NAME =+ DB_NAME +_Data
+FILENAME ='+ DB_PATH + DB_NAME +密度纤维板，
+SIZE = 2MB，
+FILEGROWTH = 10％）
+LOG ON（NAME =+ DB_NAME +_log
+FILENAME ='+ DB_PATH + DB_NAME +Log.ldf'
+SIZE = 1MB，
+FILEGROWTH = 10％）; 的SqlCommand myCommand =新的SqlCommand（sqlCreateDBQuery，的myconn）;
尝试
{
myConn.Open（）;
myCommand.ExecuteNonQuery（）;
}
赶上（System.Exception的）
{
STAT = FALSE;
}
最后
{
如果（myConn.State == ConnectionState.Open）
{
myConn.Close（）;
}
myConn.Dispose（）;
}
返回统计;
}I am new to C# & I am trying to programatically create & open a SQL Server database.
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).
PS: does C#'s
System.Data.SqlClientuse MySQL or SQLite or something else?Right now I am unsure if my code correctly creates a SQL Server database & if I connect to it correctly.
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)"}"
I have indicated where in the code below the error occurs.
Creating a SQL Server database:
// 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:
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; }
解决方案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 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:
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.
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; }
这篇关于code创建和放大器;连接到SQL Server数据库：什么错呢？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
