数据库连接+SQL语句+存储过程+ADO.Net调用存储过程

 

using System.Data;-----------Use ADO.net namespace

using System.Data.SqlClient;-------------Use SQL Server data provider namespace

使用SQLserver

@"Server=(local)\sqlexpress;Database=northwind;Integrated Security=True"

string connstr = @"Server=(local);UID=sa;PWD=123456;database=Northwind";

 

using System.Data;

using System.Data.OleDb;

使用OleDB(Access数据库)

@“Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\temp\nwind.mdb”

 

 

---

select * from zhanghu

 

insert into zhanghu (username,password) values ('ivor','gooooool')

 

update zhanghu set username='shirley',password='19841225'
where id=5

 

delete zhanghu where id=3

---

private string connstring = @"server=ZHANGZHE-78DA76\SQLEXPRESS;database=mycompany;integrated security=true";

 

create procedure GetAll

AS

select * from zhanghu

后台调用并显示在GridView上：使用了Dataset

        SqlConnection conn = new SqlConnection(connstring);
        conn.Open();
        SqlDataAdapter adapter = new SqlDataAdapter();
        adapter.SelectCommand = new SqlCommand("GetALL", conn);//没有这一行会出错，空引用
        adapter.SelectCommand.CommandType = CommandType.StoredProcedure;
        DataSet ds = new DataSet();
        adapter.Fill(ds);
        conn.Close();
        GridView1.DataSource = ds;
        GridView1.DataBind();

 

create procedure Getid

AS

select username from zhanghu where id=1

后台调用并显示在lable上：使用了SqlDataReader

        SqlConnection conn = new SqlConnection(connstring);

        conn.Open();
        SqlCommand cmd = new SqlCommand("Getid", conn);
        //SqlCommand cmd = new SqlCommand();

        //cmd.Connection = conn;
        cmd.CommandType = CommandType.StoredProcedure;
        //cmd.CommandText = "Getid";

        SqlDataReader dr = cmd.ExecuteReader();
        while (dr.Read())
        {
            Label1.Text = dr["username"].ToString();
        }
        dr.Close();
        conn.Close();

--------------带参数的存储过程

create procedure GetOneRecord

@num int

AS

select * from zhanghu where id=@num

后台调用并显示在GridView上：使用了Dataset带参数的存储过程

        SqlConnection conn = new SqlConnection(connstring);
        conn.Open();
        SqlDataAdapter adapter = new SqlDataAdapter();
        adapter.SelectCommand = new SqlCommand("GetOneRecord", conn);
        adapter.SelectCommand.CommandType = CommandType.StoredProcedure;
        adapter.SelectCommand.Parameters.Add(new SqlParameter("@num", SqlDbType.Int));
        adapter.SelectCommand.Parameters["@num"].Value = 4;
        DataSet ds = new DataSet();
        adapter.Fill(ds);
        conn.Close();
        GridView1.DataSource = ds;
        GridView1.DataBind();

------------------普通读取数据库

        string sqlstr = "select * from zhanghu";
        SqlConnection conn = new SqlConnection(connstring);
        SqlCommand cmd = new SqlCommand(sqlstr, conn);
        conn.Open();
        SqlDataReader dr = cmd.ExecuteReader();
        while (dr.Read())
        {
            Label1.Text += dr["username"].ToString();
        }
        dr.Close();
        conn.Close();

---

参见C:\Program Files\ASP.NET Starter Kits\ASP.NET Portal (CSVS)\PortalCSVS\App_Code\Components\ContactsDB.cs例：

CREATE PROCEDURE Portal_AddContact
(
    @ModuleID int,
    @UserName nvarchar(100),
    @Name     nvarchar(50),
    @Role     nvarchar(100),
    @Email    nvarchar(100),
    @Contact1 nvarchar(250),
    @Contact2 nvarchar(250),
    @ItemID   int OUTPUT
)
AS

INSERT INTO Portal_Contacts
(
    CreatedByUser,
    CreatedDate,
    ModuleID,
    Name,
    Role,
    Email,
    Contact1,
    Contact2
)

VALUES
(
    @UserName,
    GetDate(),
    @ModuleID,
    @Name,
    @Role,
    @Email,
    @Contact1,
    @Contact2
)

SELECT
    @ItemID = @@Identity

 

public int AddContact(int moduleId, int itemId, String userName, String name, String role, String email, String contact1, String contact2) {

            if (userName.Length < 1) {
                userName = "unknown";
            }

            // Create Instance of Connection and Command Object
            SqlConnection myConnection = new SqlConnection(ConfigurationSettings.AppSettings["connectionString"]);
            SqlCommand myCommand = new SqlCommand("Portal_AddContact", myConnection);

            // Mark the Command as a SPROC
            myCommand.CommandType = CommandType.StoredProcedure;

            // Add Parameters to SPROC
            SqlParameter parameterItemID = new SqlParameter("@ItemID", SqlDbType.Int, 4);
            parameterItemID.Direction = ParameterDirection.Output;
            myCommand.Parameters.Add(parameterItemID);

            SqlParameter parameterModuleID = new SqlParameter("@ModuleID", SqlDbType.Int, 4);
            parameterModuleID.Value = moduleId;
            myCommand.Parameters.Add(parameterModuleID);

            SqlParameter parameterUserName = new SqlParameter("@UserName", SqlDbType.NVarChar, 100);
            parameterUserName.Value = userName;
            myCommand.Parameters.Add(parameterUserName);

            SqlParameter parameterName = new SqlParameter("@Name", SqlDbType.NVarChar, 100);
            parameterName.Value = name;
            myCommand.Parameters.Add(parameterName);

            SqlParameter parameterRole = new SqlParameter("@Role", SqlDbType.NVarChar, 100);
            parameterRole.Value = role;
            myCommand.Parameters.Add(parameterRole);

            SqlParameter parameterEmail = new SqlParameter("@Email", SqlDbType.NVarChar, 100);
            parameterEmail.Value = email;
            myCommand.Parameters.Add(parameterEmail);

            SqlParameter parameterContact1 = new SqlParameter("@Contact1", SqlDbType.NVarChar, 100);
            parameterContact1.Value = contact1;
            myCommand.Parameters.Add(parameterContact1);

            SqlParameter parameterContact2 = new SqlParameter("@Contact2", SqlDbType.NVarChar, 100);
            parameterContact2.Value = contact2;
            myCommand.Parameters.Add(parameterContact2);

            myConnection.Open();
            myCommand.ExecuteNonQuery();
            myConnection.Close();

            return (int)parameterItemID.Value;
        }