Using stored procedures in C #

Question

I am working on a database application in C #. I had a requirement to display data on a table, and now I have done it. But my business logic is tightly tied to my code. Now I want to continue and use stored procedures with my code. What are the changes I need to make. A simple list of steps will suffice :)

SqlConnection myConnection = new SqlConnection("user id=dbblabla;" + 
            "password=1234;server=localhost\\SQLEXPRESS;" + 
            "Trusted_Connection=yes;" + 
            "database=myDB; " + 
            "connection timeout=30");

try
{
    myConnection.Open();
} catch (SqlException excep){
    Console.WriteLine(excep.StackTrace);
}

String selectionQuery = "SELECT * FROM myTable";
SqlDataAdapter myAdapter = new SqlDataAdapter(selectionQuery,myConnection);

DataSet ds = new DataSet();
myAdapter.Fill(ds,"AllInfo");

dataGridSearchOutput.DataSource = ds.Tables["AllInfo"].DefaultView;

I started by creating a new SQL command, but I'm not sure I'm using the right path.

SqlCommand newCommand = new SqlCommand("SELECT * FROM PMInfo");
newCommand.CommandType = CommandType.StoredProcedure;

Answer 1

stored procedure

CREATE PROCEDURE addemp
     @eno int,
     @ename varchar(50),
     @edate datetime
AS
  declare @p int

  select @p=count(*) from emp
      where eno=@eno
  if @p=0
     begin
       insert into emp
         values (@eno,@ename,@edate)
     end        
    RETURN

C # code

    SqlConnection cn = new SqlConnection(@"conn_str");
    SqlCommand cmd = new SqlCommand("addemp", cn);
    cmd.CommandType = CommandType.StoredProcedure;

    cmd.Parameters.AddWithValue("@eno", 10);
    cmd.Parameters.AddWithValue("@ename", "Mr.Xyz");
    cmd.Parameters.AddWithValue("@edate", DateTime.Parse("1-1-2002"));

    cn.Open();
    cmd.ExecuteNonQuery();
    cn.Close();

Answer 2

stored procedure

CREATE PROCEDURE  procedure 

AS
BEGIN

    SET NOCOUNT ON;
    SELECT field1,field2 from tbl
END
GO

code

using(SqlConnection con = new SqlConnection("user id=dbblabla;password=1234;server=localhost\\SQLEXPRESS; database=myDB; connection timeout=30"))
        {
            SqlCommand cmd = new SqlCommand("procedure",con);
            cmd.CommandType= System.Data.CommandType.StoredProcedure;
            SqlDataAdapter da = new SqlDataAdapter(cmd);
            DataTable dt = new DataTable();
            da.Fill(dt);
            dataGridSearchOutput.DataSource = dt;           

        }

Why i used use check this link

Answer 3

SqlCommand CommandType CommandType.StoredProcedure. . SqlConnection, SqlCommand SqlAdapter - . .

string connectionString = "user id=dbblabla;" + 
                        "password=1234;server=localhost\\SQLEXPRESS;" + 
                        "Trusted_Connection=yes;" + 
                        "database=myDB; " + 
                        "connection timeout=30";
DataSet ds = new DataSet();
using(SqlConnection myConnection = new SqlConnection(connectionString))
using (SqlCommand command = new SqlCommand("yourprocedure", myConnection))
{
    SqlDataAdapter myAdapter = new SqlDataAdapter();
    myAdapter.SelectCommand = command;
    myAdapter.Fill(ds,"AllInfo");
}
dataGridSearchOutput.DataSource = ds.Tables["AllInfo"].DefaultView;

Answer 4

, , - , . . -, , . , .. , havent .

, :

protected SqlCommand GetNewCmd()
{
    SqlCommand objCmd = new SqlCommand();
    objCmd.Connection = new SqlConnection(this.ConnString);
    objCmd.CommandType = CommandType.StoredProcedure;
    return objCmd;

}

protected SqlCommand GetNewCmd(string CmdText)
{
    SqlCommand objCmd = new SqlCommand(CmdText, 
                             new SqlConnection(this.ConnString));
    objCmd.CommandType = CommandType.StoredProcedure;
    return objCmd;
}

protected DataTable GetTable(SqlCommand objCmd)
{
    DataTable dt = new DataTable();
    SqlDataAdapter da = new SqlDataAdapter();

    try
    {
        da.SelectCommand = objCmd;
        da.Fill(dt);

        dt.DefaultView.AllowNew = false;
    }
    catch (Exception ex)
    {
        LogException(ex);
        throw;
    }
    finally
    {
        Close(objCmd);
        da.Dispose();
        da = null;

    }

    return dt;

}

GetTable(), GetDataSet(), ExecuteScalarInt(), ExecuteScalarGuid() .. .

, , , . , :

public DataTable GetStages(int id)
{
    SqlCommand cmd = GetNewCmd("dbo.GetStages");
    cmd.Parameters.Add("@ID", SqlDbType.Int).Value = id;
    return GetTable(cmd);
}

public void DeleteStage(int id)
{
    SqlCommand cmd = GetNewCmd("dbo.DeleteStage");
    cmd.Parameters.Add("@ID", SqlDbType.Int).Value = id;
    ExecuteNonQuery(cmd);
}

script . . , , .

, - , :

GridView1.DataSource = cApp.DB.GetStages(id);
GridView1.DataBind();

( - , .)   , .. .

, , -, . , . , . , , , .

, .

Answer 5

:

   SqlDataAdapter myAdapter = new SqlDataAdapter(); 
   myAdater.Connection = myConnection;

   SqlCommand newCommand = new SqlCommand("spPminfoList");
   newCommand.CommandType = CommandType.StoredProcedure;
   myAdapter.SelectCommand = newCommand;

, , spPminfoList .

Answer 6

SqlCommand newCommand = new SqlCommand("SELECT * FROM PMInfo");
newCommand.CommandType = CommandType.StoredProcedure;

, , :

SqlCommand newCommand = new SqlCommand("SELECT * FROM PMInfo");
newCommand.CommandType = CommandType.Text;

, :

SqlCommand newCommand = new SqlCommand("spYourProcedure");
newCommand.CommandType = CommandType.StoredProcedure;

. MSDN,

Answer 7

:

    CREATE PROCEDURE dbo.AddEmp
    @code varchar(10),
    @name varchar(10),
    @address varchar(10)
AS
    INSERT INTO Employee (Code,NameEmp,Address)
    VALUES (@code,@name,@address)
    RETURN

#:

    private void AddButton_Click(object sender, EventArgs e)
    {
        con.Open();
        SqlCommand cmd = new SqlCommand("AddEmp", con);
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.Parameters.AddWithValue("@code", SqlDbType.VarChar).Value = textBox1.Text.Trim();
        cmd.Parameters.AddWithValue("@name", SqlDbType.VarChar).Value = textBox2.Text.Trim();
        cmd.Parameters.AddWithValue("@address", SqlDbType.VarChar).Value = textBox3.Text.Trim();
        cmd.ExecuteNonQuery();
        MessageBox.Show("Employee added");
        con.Close();
    }

#

Answer 8

, , . SQL Server , SQL-, SQL Server . SQL Server , , ad hoc , .

, , ( , , ). SQL Server, , .

, , , SQL-. , , , SQL-.

Answer 9

, . , , :

: ExampleCode!= ProductionCode

Ad-hoc SQL , , . , , .

Answer 10

public class CustomerDataAccess {static string connectionstring = ConfigurationManager.ConnectionStrings ["testDB"]. ToString (); public List GetCustomerListByName (string customerName) {List customerList = new list ();