如何在csharp中调用存储过程 [英] how to call the stored procedure in csharp

问题描述

给出最佳示例，以调用csharp

Give the best examples for call the stored procedure in csharp

推荐答案

Dim sqlConnection1 As New SqlConnection("Your Connection String")
Dim cmd As New SqlCommand
Dim reader As SqlDataReader

cmd.CommandText = "StoredProcedureName"
cmd.CommandType = CommandType.StoredProcedure
cmd.Connection = sqlConnection1

sqlConnection1.Open()

reader = cmd.ExecuteReader()
' Data is accessible through the DataReader object here.'
' Use Read method (true/false) to see if reader has records and advance to next record'
' You can use a While loop for multiple records (While reader.Read() ... End While)'
If reader.Read() Then
  someVar = reader(0)
  someVar2 = reader(1)
  someVar3 = reader("NamedField")
End If

sqlConnection1.Close()



--and in sql server code


CREATE PROCEDURE StoredProcedureName
AS
SELECT * FROM table

我会给你 Google [ ^ ].

完美的方法是做事情，这是正确的方法.您需要相应地隔离事物.
为此，首先创建一个具有所有与数据访问相关的功能的类文件.例如

The perfect way is to do the things is the proper way. you need to segregate the things accordingly.
for that first make a class file which will have all your Data access related function. For example

//in DataAccess.cs
//this function will return your a data table when you pass your SQL Command to it.
public static DataTable GetTable(SqlCommand cmd)
{
    DataTable tbl = null;
    SqlConnection conn = null;
    try
    {
        conn = //your DB Connection;
        cmd.Connection = conn;
        tbl = new DataTable();
        SqlDataReader dr = cmd.ExecuteReader();
        tbl.Load(dr);
        dr.Close();
        dr.Dispose();
    }
    catch (Exception ex)
    {
        throw ex;
    }
    finally
    {
        if (conn != null)
        {
            conn.Close();
        }
    }
    return tbl;
}
//In YourClassFile.cs
//Make a function which will call your Stored Procedure for the operation you want to perform. its return type is DataRow.
public static DataRow SampleStoreProcCall(Int32 prama1,Int32 Param2, String Prama3)
        {
            DataRow row = null;
            DataTable tbl = null;
            SqlCommand cmd = new SqlCommand();
            cmd.CommandText = "Sb_SampleStoreProc";
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.AddWithValue("@prama1", prama1);
            cmd.Parameters.AddWithValue("@prama2", Param4);
            cmd.Parameters.AddWithValue("@prama3", Param3);

            tbl = DataAccess.GetTable(cmd); // Call this from your DataAccess.cs class file
            row = tbl.Rows[0];
            return row;
        }


//Call of SampleStoreProcCall function in your yourAspxPage.aspx.cs
DataRow row = YourClassFile.SampleStoreProcCall(Callprama1,CallParam2, CallPrama3);
txtbox1.txt = row["column1"].ToString();
txtbox2.txt = row["column2"].ToString();
txtbox3.txt = row["column3"].ToString();

希望这会有所帮助.

Hope this will help.

这篇关于如何在csharp中调用存储过程的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！