Shulou(Shulou.com)05/31 Report--

This article mainly explains "C # how to develop Winform program call storage", the content of the article is simple and clear, easy to learn and understand, the following please follow the editor's ideas slowly in depth, together to study and learn "C # how to develop Winform program call storage" bar!

Data sheet and data preparation:

Create table Member (MemberId int primary key identity (1jue 1), MemberAccount nvarchar (20) unique, MemberPwd nvarchar (20), MemberName nvarchar (20), MemberPhone nvarchar (20) truncate table Memberinsert into Member (MemberAccount,MemberPwd,MemberName,MemberPhone) values ('liubei','123456',' Liu Bei', '4659874564') insert into Member (MemberAccount,MemberPwd,MemberName,MemberPhone) values ('guanyu','123456',' Guan Yu', '42354234124') insert into Member (MemberAccount,MemberPwd,MemberName MemberPhone) values ('zhangfei','123456',' Zhang Fei', '41253445') insert into Member (MemberAccount,MemberPwd,MemberName,MemberPhone) values ('zhangyun','123456',' Zhao Yun', '75675676547') insert into Member (MemberAccount,MemberPwd,MemberName,MemberPhone) values ('machao','123456',' Ma Chao', '532523523') 1. Call the exEC statement to execute the stored procedure

Because the method used to call stored procedures within SQL SERVER is:

Exec stored procedure name parameter 1, parameter 2, parameter 3.

So we can call the sql statement of exec in C # and let the sql statement call the stored procedure. Strictly speaking, this way can not be called C # calling stored procedure, in essence, it is still a called sql statement.

Example:

Requirements: using the way of calling stored procedures to achieve the display of data and the addition of data.

Main code:

SQL stored procedure code:

-- query the storage of all data in the Member table (no parameters) create proc procSelectMemberas select * from Membergoexec procSelectMember-- add membership information (with input parameters) create proc procInsertMember @ acc nvarchar (20), @ pwd nvarchar (20), @ memName nvarchar (20), @ memPhone nvarchar (20) as insert into Member (MemberAccount,MemberPwd,MemberName,MemberPhone) values (@ acc,@pwd,@memName,@memPhone) goexec procInsertMember 'sunwukong' '123456 'Sun WuKong', '13554856985'

Data display C# code:

Private void BindData () {DBHelper.PrepareSql ("exec procSelectMember"); this.dataGridView1.DataSource = DBHelper.ExecQuery ();} private void Form1_Load (object sender, EventArgs e) {BindData ();}

Add C# code to the data:

Private void btAdd_Click (object sender, EventArgs e) {DBHelper.PrepareSql (string.Format ("exec procInsertMember'{0}','{1}','{2}','{3}'", this.txtAccount.Text,this.txtPwd.Text,this.txtNickName.Text,this.txtPhone.Text)); DBHelper.ExecNonQuery ();} II. Call the stored procedure directly

Calling a stored procedure requires setting the CommandType execution command type to the CommandType.StoredProcedure stored procedure.

(1) call stored procedures with no parameters

Requirements: to achieve the display of data.

Main code:

SQL stored procedure code:

-- query the storage of all data in the Member table (no parameters) create proc procSelectMemberas select * from Membergo-- call exec procSelectMember

To support stored procedures, add methods to DBHelper:

Public static void PrepareProc (string sql) {OpenConn (); / / Open database connection adp = new SqlDataAdapter (sql, conn); adp.SelectCommand.CommandType = CommandType.StoredProcedure;}

Form code:

Private void BindData () {DBHelper.PrepareProc ("procSelectMember"); this.dataGridView1.DataSource = DBHelper.ExecQuery ();} private void Form1_Load (object sender, EventArgs e) {BindData ();} (2) call a stored procedure with input parameters

Requirements: realize the addition of data.

Main code:

SQL stored procedure code:

-- add membership information (with input parameters) create proc procInsertMember @ acc nvarchar (20), @ pwd nvarchar (20), @ memName nvarchar (20), @ memPhone nvarchar (20) as insert into Member (MemberAccount,MemberPwd,MemberName,MemberPhone) values (@ acc,@pwd,@memName,@memPhone) go-- call exec procInsertMember 'sunwukong','123456',' Sun WuKong', '13554856985'

Form code:

Private void btAdd_Click (object sender, EventArgs e) {DBHelper.PrepareProc ("procInsertMember"); DBHelper.SetParameter ("acc", this.txtAccount.Text); DBHelper.SetParameter ("pwd", this.txtPwd.Text); DBHelper.SetParameter ("memName", this.txtNickName.Text); DBHelper.SetParameter ("memPhone", this.txtPhone.Text); DBHelper.ExecNonQuery ();} (3) call stored procedures with input and output parameters

Requirements: enter the user name, click the "query phone" button, and display the name and number below.

Main code:

SQL stored procedures:

-- query the name and phone number according to the account number (with input parameters With output parameters) create proc procGetInfoByAcc @ acc nvarchar (20), @ memName nvarchar (20) output,@phone nvarchar (20) outputas select @ memName = (select MemberName from Member where MemberAccount=@acc) select @ phone = (select MemberPhone from Member where MemberAccount=@acc) go-- call declare @ name nvarchar (20) declare @ phone nvarchar (20) exec procGetInfoByAcc 'machao',@name output,@phone outputselect @ name,@phone

To support the output parameters, add methods to DBHelper:

/ set output parameters (no length specified, suitable for non-string) / Parameter name / Parameter type public static void SetOutParameter (string parameterName, SqlDbType dbType) {parameterName = "@" + parameterName.Trim (); SqlParameter parameter = new SqlParameter (parameterName, dbType); parameter.Direction = ParameterDirection.Output; adp.SelectCommand.Parameters.Add (parameter) } / set the output parameter (specified length, suitable for string) / parameter name / / parameter type / parameter length public static void SetOutParameter (string parameterName, SqlDbType dbType, int size) {parameterName = "@" + parameterName.Trim (); SqlParameter parameter = new SqlParameter (parameterName, dbType, size); parameter.Direction = ParameterDirection.Output; adp.SelectCommand.Parameters.Add (parameter) } / get the parameter content value / Parameter name / Parameter value public static object GetParameter (string parameterName) {parameterName = "@" + parameterName.Trim (); return adp.SelectCommand.Parameters [parameterName] .value;}

Form code:

Private void btSearch_Click (object sender, EventArgs e) {DBHelper.PrepareProc ("procGetInfoByAcc"); DBHelper.SetParameter ("acc", this.txtAccount.Text); DBHelper.SetOutParameter ("memName", SqlDbType.NVarChar, 20); DBHelper.SetOutParameter ("phone", SqlDbType.NVarChar, 20); DBHelper.ExecNonQuery (); this.lblName.Text = "name:" + DBHelper.GetParameter ("memName"). ToString () This.lblPhone.Text = "phone:" + DBHelper.GetParameter ("phone"). ToString ();} (4) call stored procedures with input and output parameters

Requirements: password upgrade, pass in username and password; if username password is correct, and password length

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.