如何使用ado.net调用存储过程

    private void button1_Click(object sender, EventArgs e)
    {
        try
        {
            SqlConnection conn = new SqlConnection();
            conn.ConnectionString = "Data Source=*******;Initial Catalog=ChatApp;User ID=Chatapplication;Password=****";
            conn.Open();
            SqlCommand cmd = new SqlCommand();
            string chatroomidno = textBox1.Text;
            string chatroomname = textBox2.Text;
            //cmd.CommandText = "Select ChatRoomID=@ChatRoomID,ChatRoomName=@ChatRoomName from tblChatRoom";
            //cmd.Connection = conn;
            SqlDataAdapter adapt = new SqlDataAdapter("Chatroomapp",conn);
            adapt.SelectCommand.CommandType = CommandType.StoredProcedure;
            DataSet ds=new DataSet();
            DataTable dt = new DataTable();
            adapt.SelectCommand.Parameters.Add(new SqlParameter("@ChatRoomID", SqlDbType.VarChar, 100));
            adapt.SelectCommand.Parameters["@ChatRoomID"].Value = chatroomidno;
            adapt.SelectCommand.Parameters.Add(new SqlParameter("@ChatRoomName", SqlDbType.VarChar, 50));
            adapt.SelectCommand.Parameters["@ChatRoomName"].Value = chatroomname;
            adapt.Fill(ds, "tblChatRoom");
            if (dt.Rows.Count > 0)
            {
                MessageBox.Show("Connection Succedded");
            }
            else
            {
                MessageBox.Show("Connection Fails");
            }
        }
        catch (Exception ex)
        {
            MessageBox.Show("Error", ex.Message);
        }
    }

while compiling the program i got only connection fails message box,in the database i found correct,how to overcome the program to get the connection succeded message box

在编译程序时我只得到连接失败的消息框,在数据库中我发现正确,如何克服程序获取连接成功消息框

2 个解决方案

#1

Well, you're filling the ds data set - but then you're checking the dt data table for presence of rows... that's never going to work, of course!

好吧,你正在填充ds数据集 - 但是你正在检查dt数据表中是否存在行......当然,它永远不会起作用!

If you only need a single DataTable - just use and fill that data table alone - no need for the overhead of a DataSet. Also, put your SqlConnection and SqlCommand into using blocks like this:

如果您只需要一个DataTable - 只需单独使用和填充该数据表 - 就不需要DataSet的开销。另外,将SqlConnection和SqlCommand放入使用这样的块:

using (SqlConnection conn = new SqlConnection("Data Source=*******;Initial Catalog=ChatApp;User ID=Chatapplication;Password=****"))
using (SqlCommand cmd = new SqlCommand("Chatroomapp", conn))
{
    string chatroomidno = textBox1.Text;
    string chatroomname = textBox2.Text;

    SqlDataAdapter adapt = new SqlDataAdapter(cmd);
    adapt.SelectCommand.CommandType = CommandType.StoredProcedure;
    adapt.SelectCommand.Parameters.Add(new SqlParameter("@ChatRoomID", SqlDbType.VarChar, 100));
    adapt.SelectCommand.Parameters["@ChatRoomID"].Value = chatroomidno;
    adapt.SelectCommand.Parameters.Add(new SqlParameter("@ChatRoomName", SqlDbType.VarChar, 50));
    adapt.SelectCommand.Parameters["@ChatRoomName"].Value = chatroomname;

    // fill the data table - no need to explicitly call `conn.Open()` - 
    // the SqlDataAdapter automatically does this (and closes the connection, too)
    DataTable dt = new DataTable();
    adapt.Fill(dt);

    if (dt.Rows.Count > 0)
    {
       MessageBox.Show("Connection Succedded");
    }
    else
    {
       MessageBox.Show("Connection Fails");
    }
}

And just because you get back no rows in dt.Rows doesn't necessarily mean that your connection failed..... it could just be that there are no rows that match your search critieria! The connection worked just fine - but the SQL command just didn't return any rows.

并且只是因为你在dt.Rows中没有返回任何行并不一定意味着你的连接失败.....它可能只是没有与你的搜索标准匹配的行!连接工作正常 - 但SQL命令只是没有返回任何行。

#2

Connection failed means that something went wrong between your program and the database. No records returned does not mean that the connection failed. It just means that your table is empty - it contains no records.

连接失败意味着程序和数据库之间出现问题。没有返回记录并不意味着连接失败。它只是表示您的表是空的 - 它不包含任何记录。

Using ADO.NET and a stored procedures would have been a little different from what you have done it. If you need to check if the connection failed, maybe it is better to check the type of exception that is returned in the catch part.

使用ADO.NET和存储过程与您所做的有些不同。如果需要检查连接是否失败,可能最好检查catch部分中返回的异常类型。

Below is how I would have done it. I would have created a separate method that would have handled my call, and then in your button1_Click I would have just called this method:

以下是我将如何做到这一点。我会创建一个单独的方法来处理我的调用,然后在你的button1_Click我会调用这个方法:

public async Task<ChatRoom> GetAsync(string chatRoomId, string chatRoomName)
{
     try
     {
          string connectionString = ConfigurationManager.ConnectionStrings["Db"].ConnectionString;

          using (SqlConnection sqlConnection = new SqlConnection(connectionString))
          {
               await sqlConnection.OpenAsync();

               using (SqlCommand sqlCommand = new SqlCommand("ChatRooms_Get", sqlConnection))
               {
                    sqlCommand.CommandType = CommandType.StoredProcedure;
                    sqlCommand.Parameters.Add(new SqlParameter("@ChatRoomID", chatRoomId));
                    sqlCommand.Parameters.Add(new SqlParameter("@ChatRoomName", chatRoomName));

                    using (SqlDataReader sqlDataReader = await sqlCommand.ExecuteReaderAsync())
                    {
                         ChatRoom chatRoom = null;

                         if (await sqlDataReader.ReadAsync())
                         {
                              chatRoom = new ChatRoom();
                              chatRoom.Id = sqlDataReader.GetFieldValue<string>(0);
                              chatRoom.Name = sqlDataReader.GetFieldValue<string>(1);

                              chatRooms.Add(chatRoom);
                         }

                         return chatRoom;
                    }
               }
          }
     }
     catch (Exception exception)
     {
          // Try checking if the connection failed here

          throw exception;
     }
}

My chat room domain model could have looked like this:

我的聊天室域模型可能如下所示:

public class ChatRoom
{
     public string Id { get; set; }

     public string Name { get; set; }
}

And the stored procedure would have looked like this:

并且存储过程看起来像这样:

CREATE PROCEDURE [dbo].[ChatRooms_Get]
(
     @ChatRoomID VARCHAR(100),
     @ChatRoomName VARCHAR(50)
)
AS
BEGIN
     SET NOCOUNT ON;

     SELECT
          ChatRoomID,
          ChatRoomName
     FROM
          tblChatRoom
     WHERE
          ChatRoomID = @ChatRoomID
          AND ChatRoomName = @ChatRoomName;
END

GO

And then in the calling method you would get the chatroom and do with it whatever you need to do with it. For this example I just checked if it exists or not:

然后在调用方法中,您将获得聊天室,并使用它做任何事情。在这个例子中,我只是检查它是否存在:

try
{
     ChatRoom chatRoom = await chatRoomRepository.GetAsync(chatRoomId, chatRoomName);
     if (chatRoom != null)
     {
          MessageBox.Show("Record found");
     }
     else
     {
          MessageBox.Show("No record found");
     }
}
catch (Exception exception)
{
     throw exception;
}

I hope this can help.

我希望这可以提供帮助。