怎么在VS中调用存储过程来实现操作（增删改查）功能

2013-04-21 07:19:51 880 阅读 AI 写同款 · GEO 优化AI 选题数据监测一键分发 ›

VS2019 C# MySQL 学生信息增删改查（一、增删） VS2019 C# MySQL 学生信息增删改查由于刚刚学习VS2019，入手的第一个例子是编写一个简单的增删改查。这里，我以学生信息为例，进行详细的过程描述以及代码实现。一、新建一个Windows窗体应用程序 1、创建新项目界面，如下图所示。 2、更改项目名称，当然不修改也可以。我这里的是“StudentInfoSystem”；项目存放位置路径根据自己需要进行更改；“将解决方案和项目放在同一目录中”前面的勾选框，选不选都可以；点击“创建”，如下图所示。二、前期准备工作 1、将Form1属性中阅读详情

求大神指导一下，最好有源码，本人刚工作没多久，近期做一个项目，在SQL Server中写好了存储过程，在VS要怎么调用？

...全文

vs2010连接mysql数据库进行增删改查操作 .h文件 // STUDENTDlg.h : 头文件 // #pragma once #include "afxwin.h" #include "afxcmn.h" // CSTUDENTDlg 对话框 class CSTUDENTDlg : public CDialogEx { // 构造 public: CSTUDENTDlg(CWnd* pParen 阅读详情

rainJune001

.NET社区 62,273 成员 668,968 内容

+ 关注 + 加入社区

切换为时间正序

insus 2013-04-25

打赏
举报

Refer: 下载源程序来看， http://www.cnblogs.com/insus/archive/2013/04/21/3029483.html http://www.cnblogs.com/insus/archive/2013/04/21/3033467.html

hu_hujun 2013-04-25

打赏
举报

UserInfoDAL.cs类 using System; using System.Collections.Generic; using System.Linq; using System.Text; using System.Data.SqlClient; namespace DAL { public static class UserInfoDAL { /// <summary> /// 添加信息 /// </summary> /// <param name="user"></param> /// <returns></returns> public static int UserInfoInsert(Entity.UserInfo user) { int count = 0; SqlParameter parMentUserId = new SqlParameter("@UserId", System.Data.SqlDbType.Int); parMentUserId.Direction = System.Data.ParameterDirection.Output; user.UserId = parMentUserId.Value == DBNull.Value ? 0 : Convert.ToInt32(parMentUserId.Value); SqlParameter parMentUserName = new SqlParameter("@UserName", System.Data.SqlDbType.NVarChar, 50); parMentUserName.Value = user.UserName; SqlParameter parMentUserPwd = new SqlParameter("@UserPwd", System.Data.SqlDbType.NVarChar, 50); parMentUserPwd.Value = user.UserPwd; count = SqlHelper.ExecuteNonQuery("proc_UserInfoInsert", System.Data.CommandType.StoredProcedure, parMentUserId, parMentUserName, parMentUserPwd); return count; } /// <summary> /// 删除信息 /// </summary> /// <param name="id"></param> /// <returns></returns> public static int UserInfoDelete(int id) { int count = 0; SqlParameter parMentUserId = new SqlParameter("@UserId", System.Data.SqlDbType.Int); parMentUserId.Value = id; count = SqlHelper.ExecuteNonQuery("proc_UserInfoDelete", System.Data.CommandType.StoredProcedure, parMentUserId); return count; } /// <summary> /// 修改信息 /// </summary> /// <param name="user"></param> /// <returns></returns> public static int UserInfoUpdate(Entity.UserInfo user) { int count = 0; SqlParameter parMentUserId = new SqlParameter("@UserId", System.Data.SqlDbType.Int); parMentUserId.Value = user.UserId; SqlParameter parMentUserName = new SqlParameter("@UserName", System.Data.SqlDbType.NVarChar, 50); parMentUserName.Value = user.UserName; SqlParameter parMentUserPwd = new SqlParameter("@UserPwd", System.Data.SqlDbType.NVarChar, 50); parMentUserPwd.Value = user.UserPwd; count = SqlHelper.ExecuteNonQuery("proc_UserInfoUpdate", System.Data.CommandType.StoredProcedure, parMentUserId,parMentUserName,parMentUserPwd); return count; } /// <summary> /// 查询信息 /// </summary> /// <param name="page"></param> /// <returns></returns> public static List<Entity.UserInfo> UserInfoSelect(int page) { List<Entity.UserInfo> list = null; SqlParameter parMentPage = new SqlParameter("@Page", System.Data.SqlDbType.Int); parMentPage.Value = page; list = SqlHelper.ExecuteList<Entity.UserInfo>("proc_UserInfoSelect", System.Data.CommandType.StoredProcedure, parMentPage); return list; } /// <summary> /// 求出总数 /// </summary> /// <returns></returns> public static int UserInfoCount() { int count = 0; count = SqlHelper.ExecuteScalar("proc_UserINfoCount", System.Data.CommandType.StoredProcedure); return count; } /// <summary> /// 判断用户是否相同 /// </summary> /// <param name="userName"></param> /// <returns></returns> public static int UserRepeatUserName(string userName) { int count = 0; SqlParameter parMentRepeatUserName = new SqlParameter("@UserName", System.Data.SqlDbType.NVarChar, 50); parMentRepeatUserName.Value = userName; count = SqlHelper.ExecuteScalar("proc_RepeatUserName", System.Data.CommandType.StoredProcedure, parMentRepeatUserName); return count; } /// <summary> /// 根据id查询信息 /// </summary> /// <param name="id"></param> /// <returns></returns> public static List<Entity.UserInfo> UserInfoSelectId(int id) { List<Entity.UserInfo> list = null; SqlParameter parMentUserId = new SqlParameter("@UserId", System.Data.SqlDbType.Int); parMentUserId.Value = id; list = SqlHelper.ExecuteList<Entity.UserInfo>("proc_UserInfoSelectId", System.Data.CommandType.StoredProcedure, parMentUserId); return list; } } } BLL层 UserInfoBLL.cs类 using System; using System.Collections.Generic; using System.Linq; using System.Text; namespace BLL { public static class UserInfoBLL { /// <summary> /// 添加信息 /// </summary> /// <param name="user"></param> /// <returns></returns> public static int UserInfoInsert(Entity.UserInfo user) { return DAL.UserInfoDAL.UserInfoInsert(user); } /// 删除信息 /// </summary> /// <param name="id"></param> /// <returns></returns> public static int UserInfoDelete(int id) { return DAL.UserInfoDAL.UserInfoDelete(id); } /// <summary> /// 修改信息 /// </summary> /// <param name="user"></param> /// <returns></returns> public static int UserInfoUpdate(Entity.UserInfo user) { return DAL.UserInfoDAL.UserInfoUpdate(user); } /// <summary> /// 查询信息 /// </summary> /// <param name="page"></param> /// <returns></returns> public static List<Entity.UserInfo> UserInfoSelect(int page) { return DAL.UserInfoDAL.UserInfoSelect(page); } /// <summary> /// 求出总数 /// </summary> /// <returns></returns> public static int UserInfoCount() { return DAL.UserInfoDAL.UserInfoCount(); } /// <summary> /// 判断用户是否相同 /// </summary> /// <param name="userName"></param> /// <returns></returns> public static int UserRepeatUserName(string userName) { return DAL.UserInfoDAL.UserRepeatUserName(userName); } /// <summary> /// 根据id查询信息 /// </summary> /// <param name="id"></param> /// <returns></returns> public static List<Entity.UserInfo> UserInfoSelectId(int id) { return DAL.UserInfoDAL.UserInfoSelectId(id); } } }

hu_hujun 2013-04-25

打赏
举报

DAL层 SqlHelper.cs类 using System; using System.Collections.Generic; using System.Linq; using System.Text; using System.Data.SqlClient; using System.Reflection; using System.Data; namespace DAL { public class SqlHelper { // private const string CONNECTION_SQL = @"Data Source=.\sql2008;Initial Catalog=JDDB;User ID=sa"; private const string CONNECTION_SQL = @"Data Source=MICROSO-OIQ0VNG;Initial Catalog=DemoInfo;Integrated Security=True"; public static List<T> ExecuteList<T>(string sql) { List<T> lists = new List<T>(); using (SqlConnection con = new SqlConnection(CONNECTION_SQL)) { if (con.State != System.Data.ConnectionState.Open) { con.Open(); } using (SqlCommand cmd = new SqlCommand(sql, con)) { using (SqlDataReader dr = cmd.ExecuteReader(System.Data.CommandBehavior.CloseConnection)) { while (dr.Read()) { T obj = ExecuteReader<T>(dr); if (obj != null) { lists.Add(obj); } } } } } return lists; } private static T ExecuteReader<T>(SqlDataReader dr) { T obj = default(T); Type type = typeof(T); obj = Activator.CreateInstance<T>(); PropertyInfo[] propertyInfos = type.GetProperties(); int columnCount = dr.FieldCount; foreach (PropertyInfo propertyInfo in propertyInfos) { for (int i = 0; i < columnCount; i++) { string columnName = dr.GetName(i); string propertyName = propertyInfo.Name; if (string.Compare(columnName, propertyName, true) == 0) { object value = dr[i]; if (value != null && value != DBNull.Value) { propertyInfo.SetValue(obj, value, null); break; } } } } return obj; } /// <summary> /// 根据实体类查询数据 /// </summary> /// <typeparam name="T"></typeparam> /// <param name="commandText"></param> /// <param name="commandType"></param> /// <param name="values"></param> /// <returns></returns> public static T ExecuteEntity<T>(string commandText, CommandType commandType = CommandType.Text, params SqlParameter[] values) { T obj = default(T); using (SqlConnection con = new SqlConnection(CONNECTION_SQL)) { if (con.State != System.Data.ConnectionState.Open) { con.Open(); } using (SqlCommand cmd = new SqlCommand(commandText, con)) { cmd.CommandType = commandType; cmd.Parameters.AddRange(values); using (SqlDataReader dr = cmd.ExecuteReader(System.Data.CommandBehavior.CloseConnection)) { while (dr.Read()) { obj = ExecuteReader<T>(dr); break; } } } } return obj; } /// <summary> /// 执行不查询的操作 /// </summary> /// <param name="commandText"></param> /// <param name="commandType"></param> /// <param name="values"></param> /// <returns></returns> public static int ExecuteNonQuery(string commandText, CommandType commandType = CommandType.Text, params SqlParameter[] values) { int count = 0; using (SqlConnection con = new SqlConnection(CONNECTION_SQL)) { if (con.State != System.Data.ConnectionState.Open) { con.Open(); } using (SqlCommand cmd = new SqlCommand(commandText, con)) { cmd.CommandType = commandType; cmd.Parameters.AddRange(values); count = cmd.ExecuteNonQuery(); } } return count; } /// <summary> /// 执行返回一行一列的操作（聚合函数的操作） /// </summary> /// <param name="sql"></param> /// <returns></returns> public static int ExecuteScalar(string commandText, CommandType commandType = CommandType.Text, params SqlParameter[] values) { int count = 0; using (SqlConnection con = new SqlConnection(CONNECTION_SQL)) { if (con.State != System.Data.ConnectionState.Open) { con.Open(); } using (SqlCommand cmd = new SqlCommand(commandText, con)) { cmd.CommandType = commandType; cmd.Parameters.AddRange(values); count = (int)cmd.ExecuteScalar(); } } return count; } /// <summary> /// 查询所有的数据 /// </summary> /// <typeparam name="T"></typeparam> /// <param name="commandText"></param> /// <param name="commandType"></param> /// <param name="values"></param> /// <returns></returns> public static List<T> ExecuteList<T>(string commandText, CommandType commandType = CommandType.Text, params SqlParameter[] values) { List<T> list = new List<T>(); using (SqlConnection con = new SqlConnection(CONNECTION_SQL)) { if (con.State != System.Data.ConnectionState.Open) { con.Open(); } using (SqlCommand cmd = new SqlCommand(commandText, con)) { cmd.CommandType = commandType; cmd.Parameters.AddRange(values); using (SqlDataReader dr = cmd.ExecuteReader(System.Data.CommandBehavior.CloseConnection)) { while (dr.Read()) { T obj = ExecuteReader<T>(dr); list.Add(obj); } } } } return list; } } }

hu_hujun 2013-04-25

打赏
举报

SQL语句 --创建数据库 create database DemoInfo go --创建表 go create table UserInfo( UserId int not null primary key identity(1,1), UserName nvarchar(50) not null, UserPwd nvarchar(50) not null ) --添加 go create procedure proc_UserInfoInsert @UserId int output, @UserName nvarchar(50), @UserPwd nvarchar(50) as insert into UserInfo values(@UserName,@UserPwd) set @UserId=SCOPE_IDENTITY() go --删除 create procedure proc_UserInfoDelete @UserId int as delete UserInfo where UserId=@UserId go --修改 create procedure proc_UserInfoUpdate @UserId int, @UserName nvarchar(50), @UserPwd nvarchar(50) as update UserInfo set UserName=@UserName,UserPwd=@UserPwd where UserId=@UserId go --查询 create procedure proc_UserInfoSelect @Page int as select top 5 * from UserInfo where UserId not in(select top(5*@Page-5) UserId from UserInfo) go --求出数据库的总数 create procedure proc_UserINfoCount as select COUNT(*) from UserInfo go --判断输入的用户是否一样 create procedure proc_RepeatUserName @UserName nvarchar(50) as select COUNT(*) from UserInfo where UserName=@UserName go --根据id查询信息 create procedure proc_UserInfoSelectId @UserId int as select * from UserInfo where UserId=@UserId go select * from UserInfo

liu_lxx 2013-04-22

打赏
举报

comd.CommandType = CommandType.StoredProcedure; comd.CommandText = "PRO_YHDL";//存储过程名.. comd.Parameters.AddWithValue("@YHYX", Txt_Yhyx.Value);参数.

newtee 2013-04-22

打赏
举报

http://www.51cto.com/art/200806/76139.htm http://www.cnblogs.com/herbert/archive/2010/07/12/1775889.html 你翻翻VS帮助文档也有例子啊！

rainJune001 2013-04-22

打赏
举报

按照上面给的方法写了一遍还是不行啊...能不能给一个model啊，拜谢.....

rainJune001 2013-04-22

打赏
举报

我是这样写的，但是报错 public object Select() { SqlConnection con = new SqlConnection("Data Source=.;Initial Catalog=BookShop;Integrated Security=True"); SqlParameter[] para = new SqlParameter[] { new SqlParameter("@Title",SqlDbType.NVarChar,200) }; para[0].Value = "Books"; SqlCommand cmd = new SqlCommand(); cmd.Connection = con; cmd.CommandText = "book_tab"; cmd.CommandType = CommandType.StoredProcedure; cmd.Parameters.AddRange(para); con.Open(); object result = cmd.ExecuteReader(); return result; } } 但是报错，错误代码System.Data.SqlClient.SqlDataReader

newtee 2013-04-21

打赏
举报

commandtype改成StoredProcedure就行了，其他差不多。只要你平时不是拼字符串的（这种有安全性问题）。

wanhao110 2013-04-21

打赏
举报

http://www.cnblogs.com/Earloye/archive/2010/11/02/1867522.html 传送门

怎么在VS中调用存储过程来实现操作（增删改查）功能

相关推荐