环境:
系统:win10
工具:Visual Studio 2019
数据库:sqlserver
DBHelper:
using System; using System.Collections.Generic; using System.Linq; using System.Text; using System.Threading.Tasks; using System.Data; using System.Data.SqlClient; namespace CShapDBHelper { public class DBHelper { private static string url = "server=.;database=girl1804;uid=sa;pwd=root"; /// <summary> /// 获取查询信息 /// </summary> /// <param name="sql"></param> /// <returns></returns> public static DataTable Query(string sql) { SqlConnection conn = new SqlConnection(url); SqlDataAdapter sdap = new SqlDataAdapter(sql, conn); DataSet ds = new DataSet(); sdap.Fill(ds); return ds.Tables[0]; } /// <summary> /// 修改 /// </summary> /// <param name="sql"></param> /// <returns></returns> public static int ExecuteNonQuery(string sql) { SqlConnection conn = new SqlConnection(url); conn.Open(); SqlCommand cmd = new SqlCommand(sql,conn); int rows = cmd.ExecuteNonQuery(); conn.Close(); return rows; } /// <summary> /// 存储过程·一般用不到,大型项目严禁使用 /// </summary> /// <param name="proName"></param> /// <param name="paras"></param> /// <returns></returns> public static bool ExcuteProcedure(string proName, SqlParameter[] paras) { SqlConnection conn = new SqlConnection(url); conn.Open(); SqlCommand cmd = new SqlCommand(proName, conn); cmd.CommandType = CommandType.StoredProcedure; for (int i = 0; i < paras.Length; i++) { cmd.Parameters.Add(paras[i]); } int rows = cmd.ExecuteNonQuery(); conn.Close(); return rows>0; } } }
数据库:
/* Navicat SQL Server Data Transfer Source Server : mysqlserver Source Server Version : 120000 Source Host : .:1433 Source Database : girl1804 Source Schema : dbo Target Server Type : SQL Server Target Server Version : 120000 File Encoding : 65001 Date: 2022-06-12 10:44:35 */ -- ---------------------------- -- Table structure for [dbo].[girlSix] -- ---------------------------- DROP TABLE [dbo].[girlSix] GO CREATE TABLE [dbo].[girlSix] ( [id] varchar(32) NOT NULL DEFAULT (replace(newid(),'-','')) , [createDate] datetime NOT NULL DEFAULT (getdate()) , [nickName] varchar(30) NOT NULL , [introduce] nvarchar(200) NOT NULL ) GO IF ((SELECT COUNT(*) from fn_listextendedproperty('MS_Description', 'SCHEMA', N'dbo', 'TABLE', N'girlSix', 'COLUMN', N'nickName')) > 0) EXEC sp_updateextendedproperty @name = N'MS_Description', @value = N'名字' , @level0type = 'SCHEMA', @level0name = N'dbo' , @level1type = 'TABLE', @level1name = N'girlSix' , @level2type = 'COLUMN', @level2name = N'nickName' ELSE EXEC sp_addextendedproperty @name = N'MS_Description', @value = N'名字' , @level0type = 'SCHEMA', @level0name = N'dbo' , @level1type = 'TABLE', @level1name = N'girlSix' , @level2type = 'COLUMN', @level2name = N'nickName' GO IF ((SELECT COUNT(*) from fn_listextendedproperty('MS_Description', 'SCHEMA', N'dbo', 'TABLE', N'girlSix', 'COLUMN', N'introduce')) > 0) EXEC sp_updateextendedproperty @name = N'MS_Description', @value = N'介绍' , @level0type = 'SCHEMA', @level0name = N'dbo' , @level1type = 'TABLE', @level1name = N'girlSix' , @level2type = 'COLUMN', @level2name = N'introduce' ELSE EXEC sp_addextendedproperty @name = N'MS_Description', @value = N'介绍' , @level0type = 'SCHEMA', @level0name = N'dbo' , @level1type = 'TABLE', @level1name = N'girlSix' , @level2type = 'COLUMN', @level2name = N'introduce' GO -- ---------------------------- -- Records of girlSix -- ---------------------------- INSERT INTO [dbo].[girlSix] ([id], [createDate], [nickName], [introduce]) VALUES (N'04e3d962adcb4a5b8fefaf8b46995e85', N'2020-05-27 09:05:52.000', N'董新颖', N'郭老师关门弟子之一。'); GO INSERT INTO [dbo].[girlSix] ([id], [createDate], [nickName], [introduce]) VALUES (N'568fc305930347d3bec1ddd08c71ad29', N'2020-05-27 09:01:09.000', N'王笑涵', N'北方有佳人,绝世而独立。'); GO INSERT INTO [dbo].[girlSix] ([id], [createDate], [nickName], [introduce]) VALUES (N'972ec358089042e0bf24fd9efca47bde', N'2020-05-27 08:59:49.000', N'牛龙珠', N'笑若桃花三月开,清风徐徐醉颜来。'); GO INSERT INTO [dbo].[girlSix] ([id], [createDate], [nickName], [introduce]) VALUES (N'BDFFC6A36A53408281EB8CA242C0E7A3', N'2020-05-27 08:42:31.000', N'闫春娜', N'珠缨旋转星宿摇,花蔓抖擞龙蛇动。'); GO INSERT INTO [dbo].[girlSix] ([id], [createDate], [nickName], [introduce]) VALUES (N'd1cdd67717e549caba16503787b55877', N'2021-02-17 15:27:41.357', N'小龙女', N'想过过过儿过过的日子'); GO INSERT INTO [dbo].[girlSix] ([id], [createDate], [nickName], [introduce]) VALUES (N'efb0ca854dac456b9d8c42d4c4b1bce0', N'2020-05-27 09:03:30.000', N'刘梓佳', N'明眸善睐,辅靥承权,瑰姿艳逸,怡静体闲,端的是好一个花王,富贵的牡丹。'); GO INSERT INTO [dbo].[girlSix] ([id], [createDate], [nickName], [introduce]) VALUES (N'f839343b980e45caafaa9d2c9797294b', N'2020-05-27 09:04:53.000', N'魏慧娟', N'脉脉眼中波,盈盈花盛处。'); GO -- ---------------------------- -- Indexes structure for table girlSix -- ---------------------------- -- ---------------------------- -- Primary Key structure for table [dbo].[girlSix] -- ---------------------------- ALTER TABLE [dbo].[girlSix] ADD PRIMARY KEY ([id]) GO
测试:
using System; using System.Collections.Generic; using System.Data; using System.Data.SqlClient; using System.Linq; using System.Text; using System.Threading.Tasks; namespace CShapDBHelper { class Program { static void Main(string[] args) { GetList(null); //添加 string id = System.Guid.NewGuid().ToString("N"); string addSql = string.Format("insert into girlSix values('{0}','{1}','{2}','{3}')",id,DateTime.Now,"夏天","保持每天好心情。"); int rows = DBHelper.ExecuteNonQuery(addSql); Console.WriteLine(rows>0?"增删改通用成功": "NonQuery Error"); //查询 GetList(id); } /// <summary> /// 查询 /// </summary> public static void GetList(string id) { string sql = "select * from girlSix"; if (!string.IsNullOrEmpty(id)) { sql += string.Format(" where id='{0}'",id); } DataTable dt = DBHelper.Query(sql); for (int i = 0; i < dt.Rows.Count; i++) { Console.WriteLine(dt.Rows[i][0]); Console.WriteLine(dt.Rows[i][1]); Console.WriteLine(dt.Rows[i][2]); Console.WriteLine(dt.Rows[i][3]); } } } }