如何在Microsoft Azure中的Sql数据库中存储数据

I face this problem when I store my data in Microsoft Azure Cloud.

当我将数据存储在Microsoft Azure Cloud中时,我遇到了这个问题。

here error log:

这里错误日志:

Server Error in '/' Application. 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: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified) Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

'/'应用程序中的服务器错误。建立与SQL Server的连接时发生与网络相关或特定于实例的错误。服务器未找到或无法访问。验证实例名称是否正确,以及SQL Server是否配置为允许远程连接。 (提供程序:SQL网络接口,错误:26 - 查找指定的服务器/实例时出错)说明:执行当前Web请求期间发生未处理的异常。请查看堆栈跟踪以获取有关错误及其源自代码的位置的更多信息。

Exception Details: System.Data.SqlClient.SqlException: 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: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified)

异常详细信息:System.Data.SqlClient.SqlException:建立与SQL Server的连接时发生与网络相关或特定于实例的错误。服务器未找到或无法访问。验证实例名称是否正确,以及SQL Server是否配置为允许远程连接。 (提供程序:SQL网络接口,错误:26 - 查找指定的服务器/实例时出错)

This is my code:

这是我的代码:

protected void Button1_Click(object sender, EventArgs e)
{
    int userId = 0;

    SqlConnection con = new SqlConnection("Data Source=asimo.database.windows.net;Initial Catalog=asimo;Persist Security Info=True;User ID=veeraiyan;Password=***********");
    con.Open();
    SqlCommand cmd = new SqlCommand("insert into registration(username,password,email)values('" + txtusername.Text + "','" + txtpassword.Text + "','" + txtemail.Text + "')", con);
    cmd.Parameters.AddWithValue("@username", txtusername.Text.Trim());
    cmd.Parameters.AddWithValue("@password", txtpassword.Text.Trim());
    cmd.Parameters.AddWithValue("@email", txtemail.Text.Trim());
    userId = cmd.ExecuteNonQuery();
    con.Close();

    string message = string.Empty;
    switch (userId)
    {
        case -1:
            message = "Username already exists.\\nPlease choose a different username.";
            break;
        case -2:
            message = "Supplied email address has already been used.";
            break;
        default:
            message = "Registration successful.\\nUser Id: " + userId.ToString();
            break;
    }
    ClientScript.RegisterStartupScript(GetType(), "alert", "alert('" + message + "');", true);
}

2 个解决方案

#1

The connectionstring for an Azure Sql Server database should be written as

Azure Sql Server数据库的连接字符串应写为

string cnString = @"Data Source=tcp:asimo.database.windows.net,1433;
                   Database=asimo;Trusted_Connection=False;
                   User ID=veeraiyan@asimo;Password=***********;
                   Encrypt=True;";

Said that, your code to insert a record and to retrieve the IDENTITY column UserID is not parameterized as it should be.

说,你的代码插入一个记录并检索IDENTITY列UserID没有参数化,因为它应该是。

The correct approach is the following

正确的方法如下

string cmdText = @"insert into registration
                   (username,password,email)
                   values(@username, @password, @email);
                   SELECT SCOPE_IDENTITY()";
using(SqlConnection con = new SqlConnection(cnString))
using(SqlCommand cmd = new SqlCommand(cmdText, con))
{
    con.Open();
    cmd.Parameters.AddWithValue("@username", txtusername.Text.Trim());
    cmd.Parameters.AddWithValue("@password", txtpassword.Text.Trim());
    cmd.Parameters.AddWithValue("@email", txtemail.Text.Trim());
    userId = Convert.ToInt32(cmd.ExecuteScalar());
    ...

Notice that to retrieve the UserID you don't get the return value of ExecuteNonQuery. This method returns only the number of rows inserted by your command. Instead you could put two commands together, the first one inserts the record, the second one returns the value of the last IDENTITY generated by your connection. The method used is ExecuteScalar that returns the first column of the first row (from the last SELECT statement).

请注意,要检索UserID,您不会获得ExecuteNonQuery的返回值。此方法仅返回命令插入的行数。相反,您可以将两个命令放在一起,第一个插入记录,第二个返回连接生成的最后一个IDENTITY的值。使用的方法是ExecuteScalar,它返回第一行的第一列(来自最后一个SELECT语句)。

#2

Check out https://www.google.com/search?q=agoogle&ie=&oe=#q=agoogle+c-sharp+azure+sql+connection+string for your connection string issue. Then, as suggested in comments, change:

退房https://www.google.com/search?q=agoogle&ie=&oe=#q=agoogle+c-sharp+azure+sql+connection+string为您的连接字符串的问题。然后,如评论中所建议,更改:

  SqlCommand cmd = new SqlCommand("insert into registration(username,password,email)values('" + txtusername.Text + "','" + txtpassword.Text + "','" + txtemail.Text + "')", con);

 //change your connection string to an Azure-compatible one...

to:

  SqlCommand cmd = new SqlCommand("insert into registration(username,password,email)values(@username,@password,@email, con);

and while you're at it, change:

当你在它的时候,改变:

 SqlConnection con = new SqlConnection("Data Source=asimo.database.windows.net;Initial Catalog=asimo;Persist Security Info=True;User ID=veeraiyan;Password=***********");

to:

using(SqlConnection con = new SqlConnection("Data Source=asimo.database.windows.net;Initial Catalog=asimo;Persist Security Info=True;User ID=veeraiyan;Password=***********")){

 //....

 }

#1