如何在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

//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中调用存储过程的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！