你的位置:首页 > ASP.net教程

[ASP.net教程]对datagridview进行增删改(B)


 1 create DATABASE stu 2 ON 3 ( 4  name='stu.mdf', 5  filename='F:\胡浴东\数据库\stu数据库\stu.mdf', 6  size=5, 7  filegrowth=10 8 ) 9 log ON 10 ( 11  name='stu_log.ldf', 12  filename='F:\胡浴东\数据库\stu数据库\stu_log.ldf', 13  size=5, 14  filegrowth=10 15 ) 16 go 17  18 USE [stu] 19 GO 20 /****** Object: Table [dbo].[classinfo]  Script Date: 2015/5/11 10:14:54 ******/ 21 SET ANSI_NULLS ON 22 GO 23 SET QUOTED_IDENTIFIER ON 24 GO 25 SET ANSI_PADDING ON 26 GO 27 CREATE TABLE [dbo].[classinfo]( 28   [c_id] [int] IDENTITY(1,1) NOT NULL, 29   [c_dep_id] [int] NULL, 30   [c_name] [varchar](20) NULL, 31 PRIMARY KEY CLUSTERED  32 ( 33   [c_id] ASC 34 )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] 35 ) ON [PRIMARY] 36  37 GO 38 SET ANSI_PADDING OFF 39 GO 40 /****** Object: Table [dbo].[department]  Script Date: 2015/5/11 10:14:54 ******/ 41 SET ANSI_NULLS ON 42 GO 43 SET QUOTED_IDENTIFIER ON 44 GO 45 SET ANSI_PADDING ON 46 GO 47 CREATE TABLE [dbo].[department]( 48   [dep_id] [int] IDENTITY(1,1) NOT NULL, 49   [dep_name] [varchar](20) NULL, 50 PRIMARY KEY CLUSTERED  51 ( 52   [dep_id] ASC 53 )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] 54 ) ON [PRIMARY] 55  56 GO 57 SET ANSI_PADDING OFF 58 GO 59 /****** Object: Table [dbo].[logins]  Script Date: 2015/5/11 10:14:54 ******/ 60 SET ANSI_NULLS ON 61 GO 62 SET QUOTED_IDENTIFIER ON 63 GO 64 SET ANSI_PADDING ON 65 GO 66 CREATE TABLE [dbo].[logins]( 67   [l_no] [varchar](10) NOT NULL, 68   [l_pwd] [varchar](10) NOT NULL, 69   [l_question] [varchar](30) NOT NULL, 70   [l_answer] [varchar](30) NOT NULL, 71 PRIMARY KEY CLUSTERED  72 ( 73   [l_no] ASC 74 )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] 75 ) ON [PRIMARY] 76  77 GO 78 SET ANSI_PADDING OFF 79 GO 80 /****** Object: Table [dbo].[member]  Script Date: 2015/5/11 10:14:54 ******/ 81 SET ANSI_NULLS ON 82 GO 83 SET QUOTED_IDENTIFIER ON 84 GO 85 SET ANSI_PADDING ON 86 GO 87 CREATE TABLE [dbo].[member]( 88   [username] [varchar](20) NOT NULL, 89   [pwd] [varchar](6) NOT NULL, 90   [name] [varchar](20) NULL, 91   [gender] [char](2) NULL, 92   [age] [varchar](10) NULL, 93   [enjoy] [varchar](50) NULL, 94 PRIMARY KEY CLUSTERED  95 ( 96   [username] ASC 97 )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] 98 ) ON [PRIMARY] 99 100 GO101 SET ANSI_PADDING OFF102 GO103 /****** Object: Table [dbo].[result]  Script Date: 2015/5/11 10:14:54 ******/104 SET ANSI_NULLS ON105 GO106 SET QUOTED_IDENTIFIER ON107 GO108 CREATE TABLE [dbo].[result](109   [r_id] [int] IDENTITY(1,1) NOT NULL,110   [r_sub_id] [int] NULL,111   [r_stu_id] [int] NULL,112   [r_result] [int] NULL,113 PRIMARY KEY CLUSTERED 114 (115   [r_id] ASC116 )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]117 ) ON [PRIMARY]118 119 GO120 /****** Object: Table [dbo].[studentinfo]  Script Date: 2015/5/11 10:14:54 ******/121 SET ANSI_NULLS ON122 GO123 SET QUOTED_IDENTIFIER ON124 GO125 SET ANSI_PADDING ON126 GO127 CREATE TABLE [dbo].[studentinfo](128   [stu_id] [int] IDENTITY(1,1) NOT NULL,129   [stu_name] [varchar](20) NOT NULL,130   [stu_age] [int] NULL,131   [stu_c_id] [int] NULL,132   [stu_gender] [char](2) NULL,133   [stu_address] [varchar](30) NULL,134 PRIMARY KEY CLUSTERED 135 (136   [stu_id] ASC137 )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]138 ) ON [PRIMARY]139 140 GO141 SET ANSI_PADDING OFF142 GO143 /****** Object: Table [dbo].[subject]  Script Date: 2015/5/11 10:14:54 ******/144 SET ANSI_NULLS ON145 GO146 SET QUOTED_IDENTIFIER ON147 GO148 SET ANSI_PADDING ON149 GO150 CREATE TABLE [dbo].[subject](151   [sub_id] [int] IDENTITY(1,1) NOT NULL,152   [sub_name] [varchar](20) NULL,153 PRIMARY KEY CLUSTERED 154 (155   [sub_id] ASC156 )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]157 ) ON [PRIMARY]158 159 GO160 SET ANSI_PADDING OFF161 GO162 /****** Object: View [dbo].[v_cool]  Script Date: 2015/5/11 10:14:54 ******/163 SET ANSI_NULLS ON164 GO165 SET QUOTED_IDENTIFIER ON166 GO167 create view [dbo].[v_cool]168 as169 select s.stu_id,d.dep_name,c.c_name,s.stu_name,170 s.stu_age,s.stu_gender,s.stu_address171  from studentinfo s,classinfo c,department d172 where s.stu_c_id=c.c_id and c.c_dep_id=d.dep_id173 GO174 /****** Object: View [dbo].[v_ggg]  Script Date: 2015/5/11 10:14:54 ******/175 SET ANSI_NULLS ON176 GO177 SET QUOTED_IDENTIFIER ON178 GO179 create view [dbo].[v_ggg]180 as181 select s.stu_id 学员编号,s.stu_name 学员姓名,182 s.stu_age 年龄,c.c_name 班级,s.stu_gender 性别,183 s.stu_address 住址184   from studentinfo s,classinfo c185 where s.stu_c_id=c.c_id186 GO187 /****** Object: View [dbo].[View_cc]  Script Date: 2015/5/11 10:14:54 ******/188 SET ANSI_NULLS ON189 GO190 SET QUOTED_IDENTIFIER ON191 GO192 CREATE VIEW [dbo].[View_cc]193 AS194 SELECT  dbo.classinfo.c_name, dbo.department.dep_name, dbo.studentinfo.stu_age, dbo.studentinfo.stu_name, 195         dbo.studentinfo.stu_gender, dbo.studentinfo.stu_address196 FROM   dbo.classinfo INNER JOIN197         dbo.department ON dbo.classinfo.c_dep_id = dbo.department.dep_id INNER JOIN198         dbo.studentinfo ON dbo.classinfo.c_id = dbo.studentinfo.stu_c_id199 200 GO201 SET IDENTITY_INSERT [dbo].[classinfo] ON 202 203 INSERT [dbo].[classinfo] ([c_id], [c_dep_id], [c_name]) VALUES (1, 1, N'2014579')204 INSERT [dbo].[classinfo] ([c_id], [c_dep_id], [c_name]) VALUES (2, 2, N'2014789')205 INSERT [dbo].[classinfo] ([c_id], [c_dep_id], [c_name]) VALUES (3, 3, N'2014272')206 INSERT [dbo].[classinfo] ([c_id], [c_dep_id], [c_name]) VALUES (4, 3, N'2014274')207 SET IDENTITY_INSERT [dbo].[classinfo] OFF208 SET IDENTITY_INSERT [dbo].[department] ON 209 210 INSERT [dbo].[department] ([dep_id], [dep_name]) VALUES (1, N'环化系')211 INSERT [dbo].[department] ([dep_id], [dep_name]) VALUES (2, N'汽车系')212 INSERT [dbo].[department] ([dep_id], [dep_name]) VALUES (3, N'信息智能系')213 SET IDENTITY_INSERT [dbo].[department] OFF214 INSERT [dbo].[logins] ([l_no], [l_pwd], [l_question], [l_answer]) VALUES (N'cool', N'123', N'111', N'222')215 INSERT [dbo].[logins] ([l_no], [l_pwd], [l_question], [l_answer]) VALUES (N'jack', N'123', N'111', N'111')216 INSERT [dbo].[logins] ([l_no], [l_pwd], [l_question], [l_answer]) VALUES (N'徐凯伦', N'123', N'124', N'456')217 INSERT [dbo].[member] ([username], [pwd], [name], [gender], [age], [enjoy]) VALUES (N'tom', N'123', N'ss', N'男', N'90后', N'美女 帅哥 ')218 INSERT [dbo].[member] ([username], [pwd], [name], [gender], [age], [enjoy]) VALUES (N'tom1', N'123', N'ss', N'男', N'90后', N'美女 帅哥 ')219 SET IDENTITY_INSERT [dbo].[result] ON 220 221 INSERT [dbo].[result] ([r_id], [r_sub_id], [r_stu_id], [r_result]) VALUES (1, 1, 1, 88)222 INSERT [dbo].[result] ([r_id], [r_sub_id], [r_stu_id], [r_result]) VALUES (2, 1, 2, 91)223 INSERT [dbo].[result] ([r_id], [r_sub_id], [r_stu_id], [r_result]) VALUES (3, 1, 3, 90)224 INSERT [dbo].[result] ([r_id], [r_sub_id], [r_stu_id], [r_result]) VALUES (4, 1, 6, 87)225 INSERT [dbo].[result] ([r_id], [r_sub_id], [r_stu_id], [r_result]) VALUES (5, 2, 1, 90)226 INSERT [dbo].[result] ([r_id], [r_sub_id], [r_stu_id], [r_result]) VALUES (6, 2, 2, 88)227 INSERT [dbo].[result] ([r_id], [r_sub_id], [r_stu_id], [r_result]) VALUES (7, 2, 3, 92)228 INSERT [dbo].[result] ([r_id], [r_sub_id], [r_stu_id], [r_result]) VALUES (8, 2, 6, 92)229 INSERT [dbo].[result] ([r_id], [r_sub_id], [r_stu_id], [r_result]) VALUES (9, 3, 1, 89)230 INSERT [dbo].[result] ([r_id], [r_sub_id], [r_stu_id], [r_result]) VALUES (10, 3, 2, 92)231 INSERT [dbo].[result] ([r_id], [r_sub_id], [r_stu_id], [r_result]) VALUES (11, 3, 3, 94)232 INSERT [dbo].[result] ([r_id], [r_sub_id], [r_stu_id], [r_result]) VALUES (12, 3, 6, 95)233 SET IDENTITY_INSERT [dbo].[result] OFF234 SET IDENTITY_INSERT [dbo].[studentinfo] ON 235 236 INSERT [dbo].[studentinfo] ([stu_id], [stu_name], [stu_age], [stu_c_id], [stu_gender], [stu_address]) VALUES (1, N'浩然刘', 25, 3, N'男', N'湖北宜昌')237 INSERT [dbo].[studentinfo] ([stu_id], [stu_name], [stu_age], [stu_c_id], [stu_gender], [stu_address]) VALUES (2, N'柯梦', 20, 4, N'女', N'湖北十堰')238 INSERT [dbo].[studentinfo] ([stu_id], [stu_name], [stu_age], [stu_c_id], [stu_gender], [stu_address]) VALUES (3, N'林智健', 22, 3, N'男', N'湖北天门')239 INSERT [dbo].[studentinfo] ([stu_id], [stu_name], [stu_age], [stu_c_id], [stu_gender], [stu_address]) VALUES (4, N'刘畅', 19, 1, N'男', N'湖北十堰')240 INSERT [dbo].[studentinfo] ([stu_id], [stu_name], [stu_age], [stu_c_id], [stu_gender], [stu_address]) VALUES (6, N'齐白石', 20, 4, N'男', N'湖北襄樊')241 INSERT [dbo].[studentinfo] ([stu_id], [stu_name], [stu_age], [stu_c_id], [stu_gender], [stu_address]) VALUES (7, N'刘佳', 22, 4, N'男', N'湖北十堰')242 INSERT [dbo].[studentinfo] ([stu_id], [stu_name], [stu_age], [stu_c_id], [stu_gender], [stu_address]) VALUES (9, N'张馨文', 19, 3, N'男', N'甘肃')243 SET IDENTITY_INSERT [dbo].[studentinfo] OFF244 SET IDENTITY_INSERT [dbo].[subject] ON 245 246 INSERT [dbo].[subject] ([sub_id], [sub_name]) VALUES (1, N'C#')247 INSERT [dbo].[subject] ([sub_id], [sub_name]) VALUES (2, N'html')248 INSERT [dbo].[subject] ([sub_id], [sub_name]) VALUES (3, N'sqlserver')249 INSERT [dbo].[subject] ([sub_id], [sub_name]) VALUES (4, N'环境监测')250 INSERT [dbo].[subject] ([sub_id], [sub_name]) VALUES (5, N'汽车维修')251 SET IDENTITY_INSERT [dbo].[subject] OFF252 SET ANSI_PADDING ON253 254 GO255 /****** Object: Index [UQ__classinf__29397C81D09B43E9]  Script Date: 2015/5/11 10:14:54 ******/256 ALTER TABLE [dbo].[classinfo] ADD UNIQUE NONCLUSTERED 257 (258   [c_name] ASC259 )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]260 GO261 SET ANSI_PADDING ON262 263 GO264 /****** Object: Index [UQ__departme__7BE54950D8CB39D9]  Script Date: 2015/5/11 10:14:54 ******/265 ALTER TABLE [dbo].[department] ADD UNIQUE NONCLUSTERED 266 (267   [dep_name] ASC268 )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]269 GO270 SET ANSI_PADDING ON271 272 GO273 /****** Object: Index [UQ__subject__99BD2209CE37D31B]  Script Date: 2015/5/11 10:14:54 ******/274 ALTER TABLE [dbo].[subject] ADD UNIQUE NONCLUSTERED 275 (276   [sub_name] ASC277 )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]278 GO279 ALTER TABLE [dbo].[studentinfo] ADD DEFAULT ('湖北十堰') FOR [stu_address]280 GO281 ALTER TABLE [dbo].[classinfo] WITH CHECK ADD FOREIGN KEY([c_dep_id])282 REFERENCES [dbo].[department] ([dep_id])283 GO284 ALTER TABLE [dbo].[result] WITH CHECK ADD FOREIGN KEY([r_stu_id])285 REFERENCES [dbo].[studentinfo] ([stu_id])286 GO287 ALTER TABLE [dbo].[result] WITH CHECK ADD FOREIGN KEY([r_sub_id])288 REFERENCES [dbo].[subject] ([sub_id])289 GO290 ALTER TABLE [dbo].[studentinfo] WITH CHECK ADD FOREIGN KEY([stu_c_id])291 REFERENCES [dbo].[classinfo] ([c_id])292 GO293 ALTER TABLE [dbo].[member] WITH CHECK ADD CHECK (([gender]='女' OR [gender]='男'))294 GO295 ALTER TABLE [dbo].[studentinfo] WITH CHECK ADD CHECK (([stu_age]>=(18) AND [stu_age]<=(30)))296 GO297 ALTER TABLE [dbo].[studentinfo] WITH CHECK ADD CHECK (([stu_gender]='女' OR [stu_gender]='男'))298 GO299 EXEC sys.sp_addextendedproperty @name=N'MS_DiagramPane1', @value=N'[0E232FF0-B466-11cf-A24F-00AA00A3EFFF, 1.00]300 Begin DesignProperties = 301  Begin PaneConfigurations = 302    Begin PaneConfiguration = 0303     NumPanes = 4304     Configuration = "(H (1[11] 4[56] 2[16] 3) )"305    End306    Begin PaneConfiguration = 1307     NumPanes = 3308     Configuration = "(H (1 [50] 4 [25] 3))"309    End310    Begin PaneConfiguration = 2311     NumPanes = 3312     Configuration = "(H (1 [50] 2 [25] 3))"313    End314    Begin PaneConfiguration = 3315     NumPanes = 3316     Configuration = "(H (4 [30] 2 [40] 3))"317    End318    Begin PaneConfiguration = 4319     NumPanes = 2320     Configuration = "(H (1 [56] 3))"321    End322    Begin PaneConfiguration = 5323     NumPanes = 2324     Configuration = "(H (2 [66] 3))"325    End326    Begin PaneConfiguration = 6327     NumPanes = 2328     Configuration = "(H (4 [50] 3))"329    End330    Begin PaneConfiguration = 7331     NumPanes = 1332     Configuration = "(V (3))"333    End334    Begin PaneConfiguration = 8335     NumPanes = 3336     Configuration = "(H (1[56] 4[18] 2) )"337    End338    Begin PaneConfiguration = 9339     NumPanes = 2340     Configuration = "(H (1 [75] 4))"341    End342    Begin PaneConfiguration = 10343     NumPanes = 2344     Configuration = "(H (1[66] 2) )"345    End346    Begin PaneConfiguration = 11347     NumPanes = 2348     Configuration = "(H (4 [60] 2))"349    End350    Begin PaneConfiguration = 12351     NumPanes = 1352     Configuration = "(H (1) )"353    End354    Begin PaneConfiguration = 13355     NumPanes = 1356     Configuration = "(V (4))"357    End358    Begin PaneConfiguration = 14359     NumPanes = 1360     Configuration = "(V (2))"361    End362    ActivePaneConfig = 0363   End364  Begin DiagramPane = 365    Begin Origin = 366     Top = -96367     Left = 0368    End369    Begin Tables = 370     Begin Table = "classinfo"371       Begin Extent = 372        Top = 6373        Left = 38374        Bottom = 126375        Right = 180376       End377       DisplayFlags = 280378       TopColumn = 0379      End380     Begin Table = "department"381       Begin Extent = 382        Top = 6383        Left = 218384        Bottom = 107385        Right = 367386       End387       DisplayFlags = 280388       TopColumn = 0389      End390     Begin Table = "studentinfo"391       Begin Extent = 392        Top = 108393        Left = 218394        Bottom = 247395        Right = 376396       End397       DisplayFlags = 280398       TopColumn = 2399      End400    End401   End402  Begin SQLPane = 403   End404  Begin DataPane = 405    Begin ParameterDefaults = ""406    End407   End408  Begin CriteriaPane = 409    Begin ColumnWidths = 11410     Column = 1440411     Alias = 900412     Table = 1170413     Output = 720414     Append = 1400415     NewValue = 1170416     SortType = 1350417     SortOrder = 1410418     GroupBy = 1350419     Filter = 1350420     Or = 1350421     Or = 1350422     Or = 1350423    End424   End425 End426 ' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'VIEW',@level1name=N'View_cc'427 GO428 EXEC sys.sp_addextendedproperty @name=N'MS_DiagramPaneCount', @value=1 , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'VIEW',@level1name=N'View_cc'429 GO

数据库
 1 using System; 2 using System.Collections.Generic; 3 using System.Linq; 4 using System.Text; 5 using System.Threading.Tasks; 6 using System.Data; 7 using System.Data.SqlClient; 8  9 namespace Phone10 {11   class DBHelper12   {13     public static readonly string Constring = "Data Source=.;Initial Catalog=stu;Integrated Security=True";//连接数据库14     public static SqlConnection con = new SqlConnection(Constring);15 16     //打开数据库17     public static void ConOpen()18     {19       if (con.State==ConnectionState.Closed)20       {21         con.Open();22       }23     }24 25     //关闭数据库26     public static void ConClose()27     {28       if (con.State != ConnectionState.Closed)29       {30         con.Close();31       }32     }33 34     //查询有三种方法35     //方法一36     public static DataTable getTable(string sql)37     {38       ConOpen();39       SqlDataAdapter apt = new SqlDataAdapter(sql, con);40       DataTable dt = new DataTable();41       apt.Fill(dt);42       return dt;43     }44 45     //方法二46     public static SqlDataReader getReader(string sql)47     {48       ConOpen();49       SqlCommand cmd = new SqlCommand(sql,con);50       SqlDataReader reader = cmd.ExecuteReader();51       return reader;52     }53 54     //方法三55     public static Object Scalar(string sql)56     {57       ConOpen();58       SqlCommand cmd = new SqlCommand(sql,con);59       object obj = cmd.ExecuteScalar();60       ConClose();61       return obj;62     }63 64     //增加删除修改65     public static int ExecuteNonQuery(string sql)66     {67       int rult=0;68       try69       {70         ConOpen();71         SqlCommand cmd = new SqlCommand(sql, con);72         rult = cmd.ExecuteNonQuery();73       }74       catch (Exception)75       {76         ;77       }78       finally 79       {80         ConClose();81       }82       return rult;83     }84   }85 }

编码过程中的sql语句

 在找工作的过程中,datagridview大多是这样使用的,运行效果如下:

 

 1 using System; 2 using System.Collections.Generic; 3 using System.Linq; 4 using System.Text; 5 using System.Threading.Tasks; 6 using System.Data; 7 using System.Data.SqlClient; 8  9 namespace Phone10 {11   class DBHelper12   {13     public static readonly string Constring = "Data Source=.;Initial Catalog=Phone;Integrated Security=True";//连接数据库14     public static SqlConnection con = new SqlConnection(Constring);15 16     //打开数据库17     public static void ConOpen()18     {19       if (con.State==ConnectionState.Closed)20       {21         con.Open();22       }23     }24 25     //关闭数据库26     public static void ConClose()27     {28       if (con.State != ConnectionState.Closed)29       {30         con.Close();31       }32     }33 34     //查询有三种方法35     //方法一36     public static DataTable getTable(string sql)37     {38       ConOpen();39       SqlDataAdapter apt = new SqlDataAdapter(sql, con);40       DataTable dt = new DataTable();41       apt.Fill(dt);42       return dt;43     }44 45     //方法二46     public static SqlDataReader getReader(string sql)47     {48       ConOpen();49       SqlCommand cmd = new SqlCommand(sql,con);50       SqlDataReader reader = cmd.ExecuteReader();51       return reader;52     }53 54     //方法三55     public static Object Scalar(string sql)56     {57       ConOpen();58       SqlCommand cmd = new SqlCommand(sql,con);59       object obj = cmd.ExecuteScalar();60       ConClose();61       return obj;62     }63 64     //增加删除修改65     public static int ExecuteNonQuery(string sql)66     {67       int rult=0;68       try69       {70         ConOpen();71         SqlCommand cmd = new SqlCommand(sql, con);72         rult = cmd.ExecuteNonQuery();73       }74       catch (Exception)75       {76         ;77       }78       finally 79       {80         ConClose();81       }82       return rult;83     }84   }85 }

DBHelper
  1. using System;
  2. using System.Collections.Generic;
  3. using System.ComponentModel;
  4. using System.Data;
  5. using System.Drawing;
  6. using System.Linq;
  7. using System.Text;
  8. using System.Threading.Tasks;
  9. using System.Windows.Forms;
  10. namespace stu
  11. {
  12.     public partial class stu : Form
  13.     {
  14.         public stu()
  15.         {
  16.             InitializeComponent();
  17.         }
  18.         //返回
  19.         private void btn_esc_Click(object sender, EventArgs e)
  20.         {
  21.             Bind(string.Empty,string.Empty);
  22.         }
  23.         //绑定、查找
  24.         public void Bind(string cbo, string txt)
  25.         {
  26.             this.cbo_search.SelectedIndex = 0;
  27.             string sql = string.Format("select stu_id 编号, stu_name 姓名,c_name 班级, stu_age 年龄, stu_gender 性别, stu_address 地址 from studentinfo,classinfo where stu_c_id=c_id ");
  28.             if (!txt.Equals(""))
  29.             {
  30.                 switch (cbo)
  31.                 {
  32.                     case "姓名":
  33.                         sql += string.Format(" and stu_name like '%{0}%'", txt);
  34.                         break;
  35.                     case "班级":
  36.                         sql += string.Format(" and c_name like '%{0}%'", txt);
  37.                         break;
  38.                     case "年龄":
  39.                         sql += string.Format(" and stu_age like '%{0}%'", txt);
  40.                         break;
  41.                     case "性别":
  42.                         sql += string.Format(" and stu_gender like '%{0}%'", txt);
  43.                         break;
  44.                     case "地址":
  45.                         sql += string.Format(" and stu_address like '%{0}%'", txt);
  46.                         break;
  47.                 }
  48.             }
  49.             this.dgv_stu.DataSource = DBHelper.GetTable(sql);
  50.         }
  51.         //绑定班级
  52.         public void Binding()
  53.         {
  54.             string sql1 = string.Format("select c_id,c_name from classinfo");
  55.             this.cbo_class.DisplayMember = "c_name";
  56.             this.cbo_class.ValueMember = "c_id";
  57.             this.cbo_class.DataSource = DBHelper.GetTable(sql1);
  58.         }
  59.         //窗体加载事件
  60.         private void stu_Load(object sender, EventArgs e)
  61.         {
  62.             Bind(string.Empty,string.Empty);
  63.             Binding();
  64.         }
  65.         //查找
  66.         private void btn_search_Click(object sender, EventArgs e)
  67.         {
  68.             string cbo=this.cbo_search.Text;
  69.             string txt=this.txt_search.Text.Trim();
  70.             Bind(cbo,txt);
  71.         }
  72.         //非空验证
  73.         public bool Input()
  74.         {
  75.             foreach (System.Windows.Forms.Control cc in groupBox3.Controls)
  76.             {
  77.                 if (cc is TextBox)
  78.                 {
  79.                     if (cc.Text.Equals(""))
  80.                     {
  81.                         MessageBox.Show("文本框不能为空!");
  82.                         return false;
  83.                     }
  84.                 }
  85.             }
  86.             return true;
  87.         }
  88.         //清空
  89.         public void Clear()
  90.         {
  91.             foreach (System.Windows.Forms.Control cc in groupBox3.Controls)
  92.             {
  93.                 if (cc is TextBox)
  94.                 {
  95.                     if (!cc.Text.Equals(""))
  96.                     {
  97.                         cc.Text = string.Empty;
  98.                     }
  99.                 }
  100.             }
  101.             this.cbo_class.SelectedValue= true;
  102.             this.rdo_man.Checked = true;
  103.         }
  104.         //数据申明
  105.         public static int id;
  106.         public static int c_id;
  107.         public static string name = string.Empty;
  108.         public static string c_name = string.Empty;
  109.         public static int age =0;
  110.         public static string gender = string.Empty;
  111.         public static string address = string.Empty;
  112.         //获取行索引
  113.         public void ShuJu()
  114.         {
  115.             name = this.txt_name.Text.Trim();
  116.             c_id =Convert.ToInt32(this.cbo_class.SelectedValue.ToString());
  117.             try
  118.             {
  119.                 age=Convert.ToInt32(this.txt_age.Text.Trim());
  120.                 if (age<0)
  121.                 {
  122.                     MessageBox.Show("请输入正确的年龄!");
  123.                     return;
  124.                 }
  125.             }
  126.             catch (Exception)
  127.             {
  128.                 MessageBox.Show("请输入正确的年龄!");
  129.                 return;
  130.             }
  131.             gender=this.rdo_man.Checked==true?"男":"女";
  132.             address = this.txt_address.Text.Trim();
  133.         }
  134.         //添加
  135.         private void btn_add_Click(object sender, EventArgs e)
  136.         {
  137.             if (Input())
  138.             {
  139.                 ShuJu();
  140.                 string sql2 = string.Format("insert into studentinfo values('{0}','{1}','{2}','{3}','{4}')",name,age,c_id,gender,address);
  141.                 int rult1 = DBHelper.ExecuteNonQuery(sql2);
  142.                 if (rult1>0)
  143.                 {
  144.                     MessageBox.Show("添加成功!");
  145.                     Bind(string.Empty,string.Empty);
  146.                     Clear();
  147.                     return;
  148.                 }
  149.                 else
  150.                 {
  151.                     MessageBox.Show("添加失败!");
  152.                     return;
  153.                 }
  154.             }
  155.         }
  156.         //修改
  157.         private void btn_update_Click(object sender, EventArgs e)
  158.         {
  159.             if (Input())
  160.             {
  161.                 ShuJu();
  162.                 string sql3 = string.Format("update studentinfo set stu_name='{0}',stu_age='{1}',stu_c_id='{2}',stu_gender='{3}',stu_address='{4}' where stu_id='{5}'",name,age,c_id,gender,address,id);
  163.                 int rult2 = DBHelper.ExecuteNonQuery(sql3);
  164.                 if (rult2>-0)
  165.                 {
  166.                     MessageBox.Show("修改成功!");
  167.                     Bind(string.Empty,string.Empty);
  168.                     Clear();
  169.                     return;
  170.                 }
  171.                 else
  172.                 {
  173.                     MessageBox.Show("修改失败!");
  174.                     return;
  175.                 }
  176.             }
  177.         }
  178.         //单击任意单元格事件
  179.         private void dgv_stu_CellClick(object sender, DataGridViewCellEventArgs e)
  180.         {
  181.             int rowIndex=this.dgv_stu.CurrentCell.RowIndex;
  182.             if (rowIndex > -1)
  183.             {
  184.                 if (this.dgv_stu[0,rowIndex].Value.ToString()!=string.Empty)
  185.                 {
  186.                     id =Convert.ToInt32(this.dgv_stu[0, rowIndex].Value.ToString());
  187.                     this.txt_name.Text = this.dgv_stu[1, rowIndex].Value.ToString();
  188.                     this.cbo_class.Text = this.dgv_stu[2, rowIndex].Value.ToString();
  189.                     this.txt_age.Text = this.dgv_stu[3, rowIndex].Value.ToString();
  190.                     if (this.dgv_stu[4, rowIndex].Value.ToString() == "男")
  191.                     {
  192.                         this.rdo_man.Checked = true;
  193.                     }
  194.                     else
  195.                     {
  196.                         this.rdo_woman.Checked = true;
  197.                     }
  198.                     this.txt_address.Text = this.dgv_stu[5, rowIndex].Value.ToString();
  199.                 }
  200.                 else
  201.                 {
  202.                     MessageBox.Show("请选择有数据的行!");
  203.                     return;
  204.                 }
  205.             }
  206.         }
  207.         //删除
  208.         private void 删除ToolStripMenuItem_Click(object sender, EventArgs e)
  209.         {
  210.             if (Input())
  211.             {
  212.                 int rowIndex = this.dgv_stu.CurrentCell.RowIndex;
  213.                 if (rowIndex>-1)
  214.                 {
  215.                     if (this.dgv_stu[0,rowIndex].Value.ToString()!=string.Empty)
  216.                     {
  217.                         string sql4 = string.Format("delete studentinfo where stu_id='{0}'",id);
  218.                         int rult3 = DBHelper.ExecuteNonQuery(sql4);
  219.                         if (rult3>-1)
  220.                         {
  221.                             MessageBox.Show("删除成功!");
  222.                             Bind(string.Empty, string.Empty);
  223.                             Clear();
  224.                             return;
  225.                         }
  226.                         else
  227.                         {
  228.                             MessageBox.Show("删除失败!");
  229.                             return;
  230.                         }
  231.                     }
  232.                     else
  233.                     {
  234.                         MessageBox.Show("请选择有数据的行!");
  235.                         return;
  236.                     }
  237.                 }
  238.             }
  239.         }
  240.     }
  241. }