DataGridView CRUD 和将图像保存到 SQL Server (C#)
4.83/5 (27投票s)
在本文中，您将学习如何使用 C# 将图片保存到 SQL Server，并进行 DataGridView 的 CRUD 操作。
引言
本文将解释以下内容
- 在此示例演示中，我们将详细学习如何使用
DatGridViewWinForms 对学生档案管理进行 CRUD 操作。 - 如何将
Students的图片上传并插入/编辑到 SQL Server 数据库。 - 如何将 SQL Server 中的图片显示到
DataGridView图片列。 - 在
DataGridView中编辑和删除Image列。 - 使用我的
DataGridview助手类动态创建DataGridView（请参阅我的上一篇文章了解 DataGridView 助手类）。 - 我们将使用 DAL 类和
BizClass来执行 CRUD 操作。 - 如何在
DataGridview学生档案图片列中显示圆形图片。
必备组件
Using the Code
创建数据库和表
我们将创建一个 StudentDetails 表，用于学生档案的 CRUD 操作。以下是创建数据库和表查询的脚本。请在您的 SQL Server 中运行此脚本。我使用的是 SQL Server 2014。
--Script to create DB,Table and sample Insert data
USE MASTER;
-- 1) Check for the Database Exists .If the database is exist then drop and create new DB
IF EXISTS (SELECT [name] FROM sys.databases WHERE [name] = 'StudentsDB' )
BEGIN
ALTER DATABASE StudentsDB SET SINGLE_USER WITH ROLLBACK IMMEDIATE
DROP DATABASE StudentsDB ;
END
CREATE DATABASE StudentsDB
GO
USE StudentsDB
GO
-- 1) //////////// ToysDetails table
-- Create Table ToysDetails,
-- This table will be used to store the details like Toys Information
IF EXISTS ( SELECT [name] FROM sys.tables WHERE [name] = 'StudentDetails' )
DROP TABLE StudentDetails
GO
CREATE TABLE StudentDetails
(
std_ID int identity(1,1),
StudentName VARCHAR(100) NOT NULL,
Email VARCHAR(100) NOT NULL,
Phone VARCHAR(100) NOT NULL,
Address VARCHAR(100) NOT NULL,
IMAGEs varbinary(MAX)
CONSTRAINT [PK_StudentDetails] PRIMARY KEY CLUSTERED
(
[std_ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, _
ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
select * from StudentDetails
创建 Table 后，我们将为 CRUD 操作创建存储过程。
-- 1) Stored procedure to Select Student Details
-- Author : Shanu
-- Create date : 2015-12-01
-- Description : Student Details
-- Tables used : Student Details
-- Modifier : Shanu
-- Modify date : 2015-12-01
-- =============================================
-- exec USP_Student_Select ''
-- =============================================
CREATE PROCEDURE [dbo].[USP_Student_Select]
(
@StudentName VARCHAR(100) = ''
)
AS
BEGIN
select std_ID as StdNO,
StudentName as StdName,
Email as Email,
Phone as Phone,
Address as Address,
IMAGEs as StdImage
FROM StudentDetails
Where
StudentName like @StudentName +'%'
ORDER BY
StudentName
END
-- to Select by Student ID
CREATE PROCEDURE [dbo].[USP_StudentID_Select]
(
@std_ID int
)
AS
BEGIN
select std_ID as StdNO,
StudentName as StdName,
Email as Email,
Phone as Phone,
Address as Address,
IMAGEs as StdImage
FROM StudentDetails
Where
std_ID = @std_ID
END
-- To Insert Student Detail
CREATE PROCEDURE [dbo].[USP_Student_Insert]
(
@StudentName VARCHAR(100),
@Email VARCHAR(100) = '',
@Phone VARCHAR(100) = '',
@Address VARCHAR(100) = '',
@IMAGEs varbinary(MAX)
)
AS
BEGIN
IF NOT EXISTS (SELECT StudentName FROM StudentDetails WHERE StudentName=@StudentName)
BEGIN
INSERT INTO StudentDetails
(StudentName ,Email ,Phone ,Address ,IMAGEs)
VALUES
(@StudentName ,@Email ,@Phone ,@Address ,@IMAGEs)
Select 'Inserted' as results
END
ELSE
BEGIN
Select 'Exists' as results
END
END
-- To Update Student Detail
CREATE PROCEDURE [dbo].[USP_Student_Update]
( @std_ID Int=0,
@StudentName VARCHAR(100),
@Email VARCHAR(100) = '',
@Phone VARCHAR(100) = '',
@Address VARCHAR(100) = '',
@IMAGEs varbinary(MAX)
)
AS
BEGIN
UPDATE StudentDetails SET
StudentName = @StudentName ,
Email =@Email,
Phone =@Phone,
Address =@Address,
IMAGEs =@IMAGEs
WHERE
std_ID=@std_ID
Select 'Updated' as results
END
-- to Delete
CREATE PROCEDURE [dbo].[USP_Student_Delete]
( @std_ID Int=0 )
AS
BEGIN
DELETE FROM StudentDetails WHERE std_ID=@std_ID
Select 'Deleted' as results
END
在 Visual Studio 2015 中创建您的 Windows 应用程序
安装 Visual Studio 2015 后，单击开始，然后单击程序，然后选择Visual Studio 2015 - 单击Visual Studio 2015。
点击新建，然后点击项目，选择Windows并选择Windows 窗体应用程序。将您的项目名称输入为“DatagridViewCRUD”，然后点击确定。
创建 WinForms 项目后，我们将创建一个名为“Images”的文件夹用于添加图片，并创建一个名为“Helper”的文件夹用于从解决方案资源管理器创建 DataGridView 助手类、SQL BIZ 和 DAL 类。将所有图片添加到项目图片文件夹。并将编辑和删除等主要图片作为资源文件添加。
1. 创建 DatagridView 助手类
有关如何创建助手类及其用途，请参阅我的上一篇文章：使用 C# 创建 DatagGridView 助手类。
向 Helper 文件夹添加一个名为 ShanuDGVHelper.cs 的新类。将以下代码复制并粘贴到助手类中。
检查您的命名空间名称，如果您的命名空间名称与类文件不匹配，请将其更改为您的命名空间名称。例如，我们的项目名称是 DatagridViewCRUD，所以您可以在命名空间中看到它将是“DatagridViewCRUD”。如果您的项目名称不同，请将其更改。
助手类的完整详细信息可从上述链接中找到。
请注意，在此类文件中，图片列将使用资源文件。如果您的资源文件名称不同，请更改它。
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Windows.Forms;
using System.ComponentModel;
using System.Collections.Generic;
using System.IO;
using System.Diagnostics;
using System.Text.RegularExpressions;
using System.Drawing.Imaging;
using System.Runtime.InteropServices;
using System.Drawing.Text;
using System.Drawing.Drawing2D;
/// <summary>
/// Author : Shanu
/// Create date : 2015-12-01
/// Description :Student Register
/// Latest
/// Modifier :
/// Modify date :
namespace DatagridViewCRUD
{
public partial class Form1: Form
{#
region Attribute
// ReceptionSystemSrc.Helper.Webcamera.WebCam webcam;
Boolean keypadOn = false;
DataGridView Master_shanuDGV = new DataGridView();
Button btn = new Button();
Boolean Iscaptuered = false;
Helper.BizClass bizObj = new Helper.BizClass();
Helper.ShanuDGVHelper objshanudgvHelper = new Helper.ShanuDGVHelper();
DataSet ds = new DataSet();
PrivateFontCollection pfc = new PrivateFontCollection();
int ival = 0;#
endregion
public Form1()
{
InitializeComponent();
}
private void Form1_Load(object sender, EventArgs e)
{
try
{
MasterGrid_Initialize();
}
catch (Exception ex)
{}
}
public void MasterGrid_Initialize()
{
Helper.ShanuDGVHelper.Layouts(Master_shanuDGV, Color.White, Color.White,
Color.White, false, Color.SteelBlue, false, false,
false, Color.White, 46, 60, "small");
//Set Height,width and add panel to your selected control
Helper.ShanuDGVHelper.Generategrid(Master_shanuDGV, pnlGrid, 1000, 600, 10, 10);
// Color Image Column creation
Helper.ShanuDGVHelper.Templatecolumn
(Master_shanuDGV, ShanuControlTypes.imageEditColumn,
"Edit", "Edit", "Edit", true, 60, DataGridViewTriState.True,
DataGridViewContentAlignment.MiddleCenter,
DataGridViewContentAlignment.MiddleCenter,
Color.Transparent, null, "", "", Color.Black);
// Color Image Column creation
Helper.ShanuDGVHelper.Templatecolumn
(Master_shanuDGV, ShanuControlTypes.imageDelteColumn,
"Delete", "Delete", "Delete", true, 60, DataGridViewTriState.True,
DataGridViewContentAlignment.MiddleCenter,
DataGridViewContentAlignment.MiddleCenter,
Color.Transparent, null, "", "", Color.Black);
// Color Image Column creation
Helper.ShanuDGVHelper.Templatecolumn
(Master_shanuDGV, ShanuControlTypes.imageBoundcolumn,
"StdImage", "Image", "Image", true, 60, DataGridViewTriState.True,
DataGridViewContentAlignment.MiddleCenter,
DataGridViewContentAlignment.MiddleCenter,
Color.Transparent, null, "", "", Color.Black);
//// BoundColumn creation
Helper.ShanuDGVHelper.Templatecolumn
(Master_shanuDGV, ShanuControlTypes.BoundColumn,
"StdNO", "StdNO", "StdNO", true, 80, DataGridViewTriState.True,
DataGridViewContentAlignment.MiddleCenter,
DataGridViewContentAlignment.MiddleCenter,
Color.Transparent, null, "", "", Color.Black);
//// BoundColumn creation
Helper.ShanuDGVHelper.Templatecolumn
(Master_shanuDGV, ShanuControlTypes.BoundColumn,
"StdName", "StdName", "StdName", true, 180,
DataGridViewTriState.True, DataGridViewContentAlignment.MiddleCenter,
DataGridViewContentAlignment.MiddleCenter,
Color.Transparent, null, "", "", Color.Black);
//// BoundColumn creation
Helper.ShanuDGVHelper.Templatecolumn
(Master_shanuDGV, ShanuControlTypes.BoundColumn,
"Email", "Email", "Email", true, 180, DataGridViewTriState.True,
DataGridViewContentAlignment.MiddleCenter,
DataGridViewContentAlignment.MiddleCenter,
Color.Transparent, null, "", "", Color.Black);
//// BoundColumn creation
Helper.ShanuDGVHelper.Templatecolumn
(Master_shanuDGV, ShanuControlTypes.BoundColumn,
"Phone", "Phone", "Phone", true, 180, DataGridViewTriState.True,
DataGridViewContentAlignment.MiddleCenter,
DataGridViewContentAlignment.MiddleCenter,
Color.Transparent, null, "", "", Color.Black);
//// BoundColumn creation
Helper.ShanuDGVHelper.Templatecolumn
(Master_shanuDGV, ShanuControlTypes.BoundColumn,
"Address", "Address", "Address", true, 180,
DataGridViewTriState.True, DataGridViewContentAlignment.MiddleCenter,
DataGridViewContentAlignment.MiddleCenter,
Color.Transparent, null, "", "", Color.Black);
//// Color Image Column creation
//Helper.ShanuDGVHelper.Templatecolumn
//(Master_shanuDGV, ShanuControlTypes.ImageColumn,
"StaffID", "", "", true, 40, DataGridViewTriState.True,
DataGridViewContentAlignment.MiddleCenter,
DataGridViewContentAlignment.MiddleRight,
Color.Transparent, null, "", "", Color.Black);
//// Color Image Column creation
//Helper.ShanuDGVHelper.Templatecolumn
//(Master_shanuDGV, ShanuControlTypes.imageEditColumn,
"Edit", "", "", true, 38, DataGridViewTriState.True,
DataGridViewContentAlignment.MiddleCenter,
DataGridViewContentAlignment.MiddleRight,
Color.Transparent, null, "", "", Color.Black);
//// Color Image Column creation
//Helper.ShanuDGVHelper.Templatecolumn
//(Master_shanuDGV, ShanuControlTypes.imageDelteColumn,
"Delete", "", "", true, 38, DataGridViewTriState.True,
DataGridViewContentAlignment.MiddleCenter,
DataGridViewContentAlignment.MiddleRight,
Color.Transparent, null, "", "", Color.Black);
bindData();
//objshanudgvHelper.MasterDGVs_CellFormatting(Master_shanuDGV,
Master_shanuDGV.Columns["IMG"].Index, ShanuEventTypes.cellContentClick,
ShanuControlTypes.ImageColumn ds.Tables[0], "IMG");
Master_shanuDGV.CellFormatting +=
new DataGridViewCellFormattingEventHandler(MasterDGVs_CellFormatting);
Master_shanuDGV.SelectionChanged +=
new EventHandler(Master_shanuDGV_SelectionChanged);
Master_shanuDGV.CellContentClick +=
new System.Windows.Forms.DataGridViewCellEventHandler
(Master_shanuDGV_CellContentClick);
// Master_shanuDGV.DefaultCellStyle.ForeColor = Color.FromA#333333;
}
private void Master_shanuDGV_SelectionChanged(Object sender, EventArgs e)
{
Master_shanuDGV.ClearSelection();
}
private void bindData()
{
try
{
// Bind data to DGV.
SortedDictionary < string, string > sd =
new SortedDictionary < string, string > ()
{};
sd.Add("@StudentName", txtName.Text.Trim());
ds = bizObj.SelectList("USP_Student_Select", sd);
Master_shanuDGV.DataSource = null;
if (ds.Tables[0].Rows.Count > 0)
{
Master_shanuDGV.DataSource = ds.Tables[0];
}
}
catch (Exception ex)
{}
}
// Cell Content Click Event
private void Master_shanuDGV_CellContentClick
(object sender, DataGridViewCellEventArgs e)
{
if (Master_shanuDGV.Columns[e.ColumnIndex].Name == "Edit")
{
try
{
string studentID = ds.Tables[0].Rows[e.RowIndex]["StdNO"].ToString();
frmSudentAdd obj = new frmSudentAdd(studentID);
obj.ShowDialog();
bindData();
}
catch (Exception ex)
{}
}
else if (Master_shanuDGV.Columns[e.ColumnIndex].Name == "Delete")
{
try
{
string studentID = ds.Tables[0].Rows[e.RowIndex]["StdNO"].ToString();
if (MessageBox.Show("Are You Sure to Delete Student Details ?",
"Delete Student", MessageBoxButtons.YesNo) == DialogResult.Yes)
{
SortedDictionary < string, string > sd =
new SortedDictionary < string, string > ()
{};
sd.Add("@std_ID", studentID);
DataSet ds1 = new DataSet();
ds1 = bizObj.SelectList("USP_Student_Delete", sd);
if (ds1.Tables[0].Rows.Count > 0)
{
string result = ds1.Tables[0].Rows[0][0].ToString();
if (result == "Deleted")
{
MessageBox.Show("Student Deleted Successful, Thank You!",
"Successful", MessageBoxButtons.OK,
MessageBoxIcon.Information);
bindData();
}
}
}
}
catch (Exception ex)
{}
}
}#
region Image Colukmn
public static Image MakeCircleImage(Image img)
{
Bitmap bmp = new Bitmap(img.Width, img.Height);
using(GraphicsPath gpImg = new GraphicsPath())
{
gpImg.AddEllipse(0, 0, img.Width, img.Height);
using(Graphics grp = Graphics.FromImage(bmp))
{
grp.Clear(Color.White);
grp.SetClip(gpImg);
grp.DrawImage(img, Point.Empty);
}
}
return bmp;
}
void MasterDGVs_CellFormatting(object sender, DataGridViewCellFormattingEventArgs e)
{
try
{
if (Master_shanuDGV.Columns[e.ColumnIndex].Name == "StdImage")
{
if (ds.Tables[0].Rows[e.RowIndex]["StdImage"] != ""
&& ds.Tables[0].Rows[e.RowIndex]["StdImage"] != DBNull.Value)
{
byte[] bits = new byte[0];
bits = (byte[]) ds.Tables[0].Rows[e.RowIndex]["StdImage"];
MemoryStream ms = new MemoryStream(bits);
System.Drawing.Image imgSave = System.Drawing.Image.FromStream(ms);
e.Value = MakeCircleImage(imgSave);
}
else
{
System.Drawing.Image imgSave =
(Image) DatagridViewCRUD.Properties.Resources.gridUserImage;
e.Value = MakeCircleImage(imgSave);
}
}
}
catch (Exception ex)
{}
}
public Image byteArrayToImage(byte[] byteArrayIn)
{
using(MemoryStream mStream = new MemoryStream(byteArrayIn))
{
return Image.FromStream(mStream);
}
}#
endregion
private void btnSearch_Click(object sender, EventArgs e)
{
bindData();
}
private void btnStaffAdd_Click(object sender, EventArgs e)
{
frmSudentAdd obj = new frmSudentAdd("0");
obj.ShowDialog();
bindData();
}
}
}
2. 创建 DAL 和 Biz 类
- 业务逻辑：这里，业务逻辑是一个类。从 UI（我们的代码背后），我们将所有用户输入作为对象传递给业务逻辑类。
- 数据访问层：从业务逻辑类，我们将所有对象参数传递给此数据访问层类。此类将使用 ADO.NET 对象，如 Command（
Select）、Command Type（查询类型是文本或存储过程）、ExecuteNonQuery（执行Insert/Update 和Delete）、ExecuteDataset（返回select语句）和ExecuteScalar（返回单个数据）。
要创建 DAL 类，右键单击我们的 Helper 文件夹并添加新的类文件，并将其命名为 SQLDALClass.cs。
复制以下代码并粘贴到 DAL 类中。如我们之前所见，如果 Namespace 不同，则输入您的命名空间。
注意：在此类文件中，我将创建一个文本文件来存储 SQL 连接字符串。请将连接字符串更改为您的 SQL 连接。
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using System.IO;
using System.Windows.Forms;
/// <summary>
/// Author : Shanu
/// Create date : 2015-05-09
/// Description :MYSQL DBCONNECT Helper CLASS
/// Latest
/// Modifier :
/// Modify date :
/// </summary>
namespace DatagridViewCRUD.Helper
{
class SQLDALClass
{
public String ConnectionString = "server=.;
database=StudentsDB; user id=URID; password=PWD;";
public SqlConnection connection;#
region Initiallize
public SQLDALClass()
{
Initialize();
}
//Initialize values
private void Initialize()
{
ConnectionString = ReadConnectionString();
connection = new SqlConnection(ConnectionString);
}
public String ReadConnectionString()
{
string path = Application.StartupPath + @ "\DBConnection.txt";
String connectionString = "";
if (!File.Exists(path))
{
using(StreamWriter tw = File.CreateText(path))
{
tw.WriteLine("server=.; database=StudentsDB;
user id=URID; password=PWD;");
tw.Close();
ConnectionString = "server=.; database=StudentsDB;
user id=URID; password=PWD;";
}
}
else
{
TextReader tr = new StreamReader(path);
connectionString = tr.ReadLine();
tr.Close();
}
return connectionString;
}#
endregion# region DB ConnectionOpen
public bool OpenConnection()
{
try
{
connection.Open();
return true;
}
catch (SqlException ex)
{
writeLogMessage(ex.Message.ToString());
}
return false;
}#
endregion# region DB Connection Close
//Close connection
public bool CloseConnection()
{
try
{
connection.Close();
return true;
}
catch (SqlException ex)
{
writeLogMessage(ex.Message.ToString());
return false;
}
}#
endregion# region ExecuteNonQuery
for insert / Update and Delete
// For Student
// Insert
public DataSet SP_Student_ImageInsert(String SP_NAME, string StudentName,
string Email, string Phone, string Address, byte[] IMAGEs)
{
DataSet ds = new DataSet();
//open connection
if (OpenConnection() == true)
{
//create command and assign the query and connection from the constructor
SqlCommand cmd = new SqlCommand(SP_NAME, connection);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add("@StudentName", SqlDbType.VarChar);
cmd.Parameters.Add("@Email", SqlDbType.VarChar);
cmd.Parameters.Add("@Phone", SqlDbType.VarChar);
cmd.Parameters.Add("@Address", SqlDbType.VarChar);
cmd.Parameters.Add("@IMAGEs", SqlDbType.VarBinary);
cmd.Parameters["@StudentName"].Value = StudentName;
cmd.Parameters["@Email"].Value = Email;
cmd.Parameters["@Phone"].Value = Phone;
cmd.Parameters["@Address"].Value = Address;
if (IMAGEs == null)
{
cmd.Parameters["@IMAGEs"].Value = DBNull.Value;
}
else
{
cmd.Parameters["@IMAGEs"].Value = IMAGEs;
}
//Execute command
SqlDataAdapter da = new SqlDataAdapter(cmd);
da.Fill(ds);
//close connection
CloseConnection();
}
return ds;
}
// Update
public DataSet SP_Student_ImageEdit(String SP_NAME, int std_ID,
string StudentName, string Email, string Phone, string Address, byte[] IMAGEs)
{
DataSet ds = new DataSet();
//open connection
if (OpenConnection() == true)
{
//create command and assign the query and connection from the constructor
SqlCommand cmd = new SqlCommand(SP_NAME, connection);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add("@std_ID", SqlDbType.Int);
cmd.Parameters.Add("@StudentName", SqlDbType.VarChar);
cmd.Parameters.Add("@Email", SqlDbType.VarChar);
cmd.Parameters.Add("@Phone", SqlDbType.VarChar);
cmd.Parameters.Add("@Address", SqlDbType.VarChar);
cmd.Parameters.Add("@IMAGEs", SqlDbType.VarBinary);
cmd.Parameters["@std_ID"].Value = std_ID;
cmd.Parameters["@StudentName"].Value = StudentName;
cmd.Parameters["@Email"].Value = Email;
cmd.Parameters["@Phone"].Value = Phone;
cmd.Parameters["@Address"].Value = Address;
if (IMAGEs == null)
{
cmd.Parameters["@IMAGEs"].Value = DBNull.Value;
}
else
{
cmd.Parameters["@IMAGEs"].Value = IMAGEs;
}
//Execute command
SqlDataAdapter da = new SqlDataAdapter(cmd);
da.Fill(ds);
//close connection
CloseConnection();
}
return ds;
}#
endregion# region Write Log Message to textFile
public void writeLogMessage(String logMessage)
{
string path = Application.StartupPath + @ "\LogFile.txt";
if (!File.Exists(path))
{
using(StreamWriter tw = File.CreateText(path))
{
tw.WriteLine(logMessage);
tw.Close();
}
}
else
{
StreamWriter tr = new StreamWriter(path);
tr.WriteLine(logMessage);
tr.Close();
}
}#
endregion# region DataTable
for select result and
return as DataTable
//for select result and return as DataTable
public DataSet SP_Dataset_return(String ProcName,
params SqlParameter[] commandParameters)
{
DataSet ds = new DataSet();
//open connection
if (OpenConnection() == true)
{
//for Select Query
SqlCommand cmdSel = new SqlCommand(ProcName, connection);
cmdSel.CommandType = CommandType.StoredProcedure;
// Assign the provided values to these parameters based on parameter order
AssignParameterValues(commandParameters, commandParameters);
AttachParameters(cmdSel, commandParameters);
SqlDataAdapter da = new SqlDataAdapter(cmdSel);
da.Fill(ds);
//close connection
CloseConnection();
}
return ds;
}
private static void AttachParameters
(SqlCommand command, SqlParameter[] commandParameters)
{
if (command == null) throw new ArgumentNullException("command");
if (commandParameters != null)
{
foreach(SqlParameter p in commandParameters)
{
if (p != null)
{
// Check for derived output value with no value assigned
if ((p.Direction == ParameterDirection.InputOutput ||
p.Direction == ParameterDirection.Input) && (p.Value == null))
{
p.Value = DBNull.Value;
}
command.Parameters.Add(p);
}
}
}
}
private static void AssignParameterValues(SqlParameter[] commandParameters,
object[] parameterValues)
{
if ((commandParameters == null) || (parameterValues == null))
{
// Do nothing if we get no data
return;
}
// We must have the same number of values as we pave parameters to put them in
if (commandParameters.Length != parameterValues.Length)
{
throw new ArgumentException
("Parameter count does not match Parameter Value count.");
}
// Iterate through the SqlParameters,
// assigning the values from the corresponding position in the
// value array
for (int i = 0, j = commandParameters.Length; i < j; i++)
{
// If the current array value derives from IDbDataParameter,
// then assign its Value property
if (parameterValues[i] is IDbDataParameter)
{
IDbDataParameter paramInstance = (IDbDataParameter) parameterValues[i];
if (paramInstance.Value == null)
{
commandParameters[i].Value = DBNull.Value;
}
else
{
commandParameters[i].Value = paramInstance.Value;
}
}
else if (parameterValues[i] == null)
{
commandParameters[i].Value = DBNull.Value;
}
else
{
commandParameters[i].Value = parameterValues[i];
}
}
}
#endregion
}
}
3. 创建 Biz 类
与此类似，我们创建了 Biz 类，在其中我们创建了一个通过创建方法来传递参数对象的方法。这是我们的 Biz 类的完整代码。
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using System.IO;
using System.Windows.Forms;
/// <summary>
/// Author : Shanu
/// Create date : 2015-12-01
/// Description : Biz Class
/// Latest
/// Modifier :
/// Modify date :
/// </summary>
namespace DatagridViewCRUD.Helper
{
class BizClass
{
DatagridViewCRUD.Helper.SQLDALClass objDAL =
new DatagridViewCRUD.Helper.SQLDALClass();
//All Business Method here
# region ALL Business method here
public DataSet SelectList(String SP_NAME, SortedDictionary < string, string > sd)
{
try
{
return objDAL.SP_Dataset_return(SP_NAME, GetSdParameter(sd));
}
catch (Exception ex)
{
throw ex;
}
}
// Insert
public DataSet SP_student_ImageInsert(String SP_NAME, string StudentName,
string Email, string Phone, string Address, byte[] IMAGEs)
{
try
{
return objDAL.SP_Student_ImageInsert(SP_NAME,
StudentName, Email, Phone, Address, IMAGEs);
}
catch (Exception ex)
{
throw ex;
}
}
// EDIT
public DataSet SP_student_ImageEdit(String SP_NAME, int std_ID, string StudentName,
string Email, string Phone, string Address, byte[] IMAGEs)
{
try
{
return objDAL.SP_Student_ImageEdit(SP_NAME, std_ID,
StudentName, Email, Phone, Address, IMAGEs);
}
catch (Exception ex)
{
throw ex;
}
}#
endregion# region Methods Parameter
/// <summary>
/// This method Sorted-Dictionary key values to an array of SqlParameters
/// </summary>
public static SqlParameter[] GetSdParameter
(SortedDictionary < string, string > sortedDictionary)
{
SqlParameter[] paramArray = new SqlParameter[]
{};
foreach(string key in sortedDictionary.Keys)
{
AddParameter(ref paramArray, new SqlParameter(key, sortedDictionary[key]));
}
return paramArray;
}
public static void AddParameter(ref SqlParameter[] paramArray,
string parameterName, object parameterValue)
{
SqlParameter parameter = new SqlParameter(parameterName, parameterValue);
AddParameter(ref paramArray, parameter);
}
public static void AddParameter(ref SqlParameter[] paramArray,
string parameterName, object parameterValue, object parameterNull)
{
SqlParameter parameter = new SqlParameter();
parameter.ParameterName = parameterName;
if (parameterValue.ToString() == parameterNull.ToString())
parameter.Value = DBNull.Value;
else parameter.Value = parameterValue;
AddParameter(ref paramArray, parameter);
}
public static void AddParameter(ref SqlParameter[] paramArray,
string parameterName, SqlDbType dbType, object parameterValue)
{
SqlParameter parameter = new SqlParameter(parameterName, dbType);
parameter.Value = parameterValue;
AddParameter(ref paramArray, parameter);
}
public static void AddParameter(ref SqlParameter[] paramArray,
string parameterName, SqlDbType dbType,
ParameterDirection direction, object parameterValue)
{
SqlParameter parameter = new SqlParameter(parameterName, dbType);
parameter.Value = parameterValue;
parameter.Direction = direction;
AddParameter(ref paramArray, parameter);
}
public static void AddParameter(ref SqlParameter[] paramArray,
params SqlParameter[] newParameters)
{
SqlParameter[] newArray = Array.CreateInstance(typeof (SqlParameter),
paramArray.Length + newParameters.Length) as SqlParameter[];
paramArray.CopyTo(newArray, 0);
newParameters.CopyTo(newArray, paramArray.Length);
paramArray = newArray;
}#
endregion
}
}
3. 设计您的窗体 * 设计您的窗体
设计您的窗体，包含搜索字段，并添加一个名为 pnlGrid 的面板。我们将动态 DataGridView 添加到此面板中。在窗体加载时，我们将使用我们的 Helper 类设计 DataGridView，并将 DataGridView 添加到 Panel 中。
private void Form1_Load(object sender, EventArgs e)
{
try
{
MasterGrid_Initialize();
}
catch (Exception ex)
{}
}
public void MasterGrid_Initialize()
{
Helper.ShanuDGVHelper.Layouts(Master_shanuDGV, Color.White, Color.White, Color.White,
false, Color.SteelBlue, false, false, false, Color.White, 46, 60, "small");
//Set Height,width and add panel to your selected control
Helper.ShanuDGVHelper.Generategrid(Master_shanuDGV, pnlGrid, 1000, 600, 10, 10);
// Color Image Column creation
Helper.ShanuDGVHelper.Templatecolumn(Master_shanuDGV, ShanuControlTypes.imageEditColumn,
"Edit", "Edit", "Edit", true, 60, DataGridViewTriState.True,
DataGridViewContentAlignment.MiddleCenter, DataGridViewContentAlignment.MiddleCenter,
Color.Transparent, null, "", "", Color.Black);
// Color Image Column creation
Helper.ShanuDGVHelper.Templatecolumn(Master_shanuDGV, ShanuControlTypes.imageDelteColumn,
"Delete", "Delete", "Delete", true, 60, DataGridViewTriState.True,
DataGridViewContentAlignment.MiddleCenter, DataGridViewContentAlignment.MiddleCenter,
Color.Transparent, null, "", "", Color.Black);
// Color Image Column creation
Helper.ShanuDGVHelper.Templatecolumn(Master_shanuDGV, ShanuControlTypes.imageBoundcolumn,
"StdImage", "Image", "Image", true, 60, DataGridViewTriState.True,
DataGridViewContentAlignment.MiddleCenter, DataGridViewContentAlignment.MiddleCenter,
Color.Transparent, null, "", "", Color.Black);
//// BoundColumn creation
Helper.ShanuDGVHelper.Templatecolumn(Master_shanuDGV, ShanuControlTypes.BoundColumn,
"StdNO", "StdNO", "StdNO", true, 80, DataGridViewTriState.True,
DataGridViewContentAlignment.MiddleCenter, DataGridViewContentAlignment.MiddleCenter,
Color.Transparent, null, "", "", Color.Black);
//// BoundColumn creation
Helper.ShanuDGVHelper.Templatecolumn(Master_shanuDGV, ShanuControlTypes.BoundColumn,
"StdName", "StdName", "StdName", true, 180, DataGridViewTriState.True,
DataGridViewContentAlignment.MiddleCenter, DataGridViewContentAlignment.MiddleCenter,
Color.Transparent, null, "", "", Color.Black);
//// BoundColumn creation
Helper.ShanuDGVHelper.Templatecolumn(Master_shanuDGV, ShanuControlTypes.BoundColumn,
"Email", "Email", "Email", true, 180, DataGridViewTriState.True,
DataGridViewContentAlignment.MiddleCenter, DataGridViewContentAlignment.MiddleCenter,
Color.Transparent, null, "", "", Color.Black);
//// BoundColumn creation
Helper.ShanuDGVHelper.Templatecolumn(Master_shanuDGV, ShanuControlTypes.BoundColumn,
"Phone", "Phone", "Phone", true, 180, DataGridViewTriState.True,
DataGridViewContentAlignment.MiddleCenter, DataGridViewContentAlignment.MiddleCenter,
Color.Transparent, null, "", "", Color.Black);
//// BoundColumn creation
Helper.ShanuDGVHelper.Templatecolumn(Master_shanuDGV, ShanuControlTypes.BoundColumn,
"Address", "Address", "Address", true, 180, DataGridViewTriState.True,
DataGridViewContentAlignment.MiddleCenter, DataGridViewContentAlignment.MiddleCenter,
Color.Transparent, null, "", "", Color.Black);
bindData();
Master_shanuDGV.CellFormatting +=
new DataGridViewCellFormattingEventHandler(MasterDGVs_CellFormatting);
Master_shanuDGV.SelectionChanged += new EventHandler(Master_shanuDGV_SelectionChanged);
Master_shanuDGV.CellContentClick +=
new System.Windows.Forms.DataGridViewCellEventHandler(Master_shanuDGV_CellContentClick);
}
对于圆形图像显示，我们将使用 DatagridView CellFormatting 事件，对于编辑/删除，我们将使用 CellContentClick 事件。
4. 在网格中显示圆形图像
在 DatagridView CellFormatting 事件中，我们检查学生图片列。我们将每张学生图片传递给 MakeCircleImage 方法，以在 DataGridView 中以圆形显示学生的图片。
public static Image MakeCircleImage(Image img)
{
Bitmap bmp = new Bitmap(img.Width, img.Height);
using(GraphicsPath gpImg = new GraphicsPath())
{
gpImg.AddEllipse(0, 0, img.Width, img.Height);
using(Graphics grp = Graphics.FromImage(bmp))
{
grp.Clear(Color.White);
grp.SetClip(gpImg);
grp.DrawImage(img, Point.Empty);
}
}
return bmp;
}
void MasterDGVs_CellFormatting(object sender, DataGridViewCellFormattingEventArgs e)
{
try
{
if (Master_shanuDGV.Columns[e.ColumnIndex].Name == "StdImage")
{
if (ds.Tables[0].Rows[e.RowIndex]["StdImage"] != ""
&& ds.Tables[0].Rows[e.RowIndex]["StdImage"] != DBNull.Value)
{
byte[] bits = new byte[0];
bits = (byte[]) ds.Tables[0].Rows[e.RowIndex]["StdImage"];
MemoryStream ms = new MemoryStream(bits);
System.Drawing.Image imgSave = System.Drawing.Image.FromStream(ms);
e.Value = MakeCircleImage(imgSave);
}
else
{
System.Drawing.Image imgSave =
(Image) DatagridViewCRUD.Properties.Resources.gridUserImage;
e.Value = MakeCircleImage(imgSave);
}
}
}
catch (Exception ex)
{}
}
5. 搜索学生详情
在搜索按钮点击时，我将调用 bindData() 方法来绑定结果。我们将学生姓名作为参数与 SP 名称一起传递给我们的业务逻辑类，并从 Bizx 类中获取 Dataset 形式的结果，然后将结果绑定到 DataGridView。
private void btnSearch_Click(object sender, EventArgs e)
{
bindData();
}
private void bindData()
{
try
{
// Bind data to DGV.
SortedDictionary < string, string > sd = new SortedDictionary < string, string > ()
{};
sd.Add("@StudentName", txtName.Text.Trim());
ds = bizObj.SelectList("USP_Student_Select", sd);
Master_shanuDGV.DataSource = null;
if (ds.Tables[0].Rows.Count > 0)
{
Master_shanuDGV.DataSource = ds.Tables[0];
}
}
catch (Exception ex)
{}
}
6. 插入/编辑学生详情
我们将创建一个新的窗体用于 Insert 和 Update，并使用一个窗体来添加/编辑学生详细信息。此外，我们将设计我们的窗体，包含所有必要的控件，并添加一个 PictureBox 和上传按钮，将学生档案照片保存到 SQL Server。
从我们的主窗体，对于添加，我们传递参数 0；对于编辑，我们传递学生 ID 作为参数。
在这里，我们可以看到从我们的添加学生按钮点击，我们将参数“0”传递给学生添加窗体。
private void btnStaffAdd_Click(object sender, EventArgs e)
{
frmSudentAdd obj = new frmSudentAdd("0");
obj.ShowDialog();
bindData();
}
在 frmSudentAdd 窗体构造函数中，我们将获取从主窗体传递的学生 ID 并存储在局部变量中。
public frmSudentAdd(string StudentID)
{
InitializeComponent();
StudentIDS = StudentID;
}
在窗体加载时，我们将检查 StudentIDS 是否不等于 0，这意味着它是用于添加新学生。如果 StudentIDS 大于 0，则它是用于编辑。
private void frmSudentAdd_Load(object sender, EventArgs e)
{
try
{
isImageCaptuerd = false;
// setFont();
if (StudentIDS != "0")
{
displayVisitorDetails();
}
}
catch (Exception ex)
{}
}
如果传递了学生 ID 用于编辑，则学生 ID 将作为参数传递，以获取学生的详细信息并显示详细信息以供编辑。
private void displayVisitorDetails()
{
// lblUserType.Text = VisitorTypes;
try
{
SortedDictionary < string, string > sd = new SortedDictionary < string, string > ()
{};
//sd.Add("@searchType", VisitorTypes);
sd.Add("@std_ID", StudentIDS);
DataSet ds = new DataSet();
ds = bizObj.SelectList("USP_StudentID_Select", sd);
if (ds.Tables[0].Rows.Count > 0)
{
txtStudentID.Text = ds.Tables[0].Rows[0]["StdNO"].ToString();
txtstdName.Text = ds.Tables[0].Rows[0]["StdName"].ToString();
txtEmail.Text = ds.Tables[0].Rows[0]["Email"].ToString();
txtphone.Text = ds.Tables[0].Rows[0]["Phone"].ToString();
txtAddress.Text = ds.Tables[0].Rows[0]["Address"].ToString();
if (ds.Tables[0].Rows[0]["StdImage"].ToString() != "")
{
byte[] bits = new byte[0];
bits = (byte[]) ds.Tables[0].Rows[0]["StdImage"];
MemoryStream ms = new MemoryStream(bits);
this.picImage.Image = System.Drawing.Bitmap.FromStream(ms);
ms = null;
}
}
}
catch (Exception ex)
{}
}
上传图片：在上传图片按钮点击时，使用 OpenFileDialog 选择图片文件，并将所选图片添加到 PictureBox。
private void btnCaptuer_Click(object sender, EventArgs e)
{
try
{
isImageCaptuerd = false;
OpenFileDialog ofd = new OpenFileDialog();
ofd.Filter = "JPEG Files (*.jpeg)|*.jpeg|PNG Files
(*.png)|*.png|JPG Files (*.jpg)|*.jpg|GIF Files (*.gif)|*.gif";
ofd.Title = "Please Upload Image";
if (ofd.ShowDialog() == DialogResult.OK)
{
isImageCaptuerd = true;
picImage.Image = Image.FromFile(ofd.FileName);
}
}
catch (Exception ex)
{}
}
保存按钮点击
在保存按钮点击时，我们首先检查是新用户还是编辑现有用户。对于新用户，调用以下内容
private void btnSave_Click(object sender, EventArgs e)
{
if (StudentIDS != "0")
{
EditStaffDetails();
}
else
{
AddNewStaffDetails();
}
}
添加新学生
我们将所有参数以及作为 Byte 对象的 Image 传递给 BIZ 类。成功插入后，我们将向用户显示消息。
private void AddNewStaffDetails()
{
try
{
byte[] ImageData = null;
string result = "";
if (isImageCaptuerd == true)
{
try
{
if (picImage.Image != null)
{
ImageData = imgToByteArray(picImage.Image);
}
}
catch (Exception ex)
{}
}
SortedDictionary < string, string > sd = new SortedDictionary < string, string > ()
{};
DataSet ds = new DataSet();
ds = bizObj.SP_student_ImageInsert("USP_Student_Insert",
txtstdName.Text.Trim(), txtEmail.Text.Trim(), txtphone.Text.Trim(),
txtAddress.Text.Trim(), ImageData);
if (ds.Tables[0].Rows.Count > 0)
{
result = ds.Tables[0].Rows[0][0].ToString();
if (result == "Inserted")
{
MessageBox.Show("Student Added Successful, Thank You!",
"Successful", MessageBoxButtons.OK, MessageBoxIcon.Information);
this.Close();
}
else
{
MessageBox.Show
(result, "Error", MessageBoxButtons.OK, MessageBoxIcon.Warning);
}
}
}
catch (Exception ex)
{}
}
编辑学生详情
对于主窗体 DataGridView 单元格内容点击中的编辑，我们将获取选定的学生 ID 并将该 ID 传递给 frmStudentAdd 以编辑 student 详细信息。
private void Master_shanuDGV_CellContentClick(object sender, DataGridViewCellEventArgs e)
{
if (Master_shanuDGV.Columns[e.ColumnIndex].Name == "Edit")
{
string studentID = ds.Tables[0].Rows[e.RowIndex]["StdNO"].ToString();
frmSudentAdd obj = new frmSudentAdd(studentID);
obj.ShowDialog();
bindData();
}
}
我们可以编辑 Student 详细信息，也可以更新学生的任何新档案照片。我们将所有参数以及作为 Byte 对象的 Image 传递给 BIZ 类。成功更新后，我们将向用户显示消息。
private void EditStaffDetails()
{
try
{
byte[] ImageData = null;
string result = "";
if(picImage.Image!=null)
{
try
{
ImageData = imgToByteArray(picImage.Image);
}
catch (Exception ex)
{
}
}
SortedDictionary<string, string> sd = new SortedDictionary<string, string>() { };
DataSet ds = new DataSet();
int StudentID = Convert.ToInt32(StudentIDS);
ds = bizObj.SP_student_ImageEdit("USP_Student_Update",
StudentID,txtstdName.Text.Trim(),
txtEmail.Text.Trim(),
txtphone.Text.Trim(),
txtAddress.Text.Trim(),
ImageData);
if (ds.Tables[0].Rows.Count > 0)
{
result = ds.Tables[0].Rows[0][0].ToString();
if (result == "Updated")
{
MessageBox.Show("Student Updated Successful, Thank You!",
"Successful", MessageBoxButtons.OK, MessageBoxIcon.Information);
this.Close();
}
else
{
MessageBox.Show
(result, "Error", MessageBoxButtons.OK, MessageBoxIcon.Warning);
}
}
}
catch (Exception ex)
{
}
}
// Picbox to Byte Convert
public byte[] imgToByteArray(Image img)
{
using (MemoryStream mStream = new MemoryStream())
{
img.Save(mStream, img.RawFormat);
return mStream.ToArray();
}
}
7. 删除学生详情
在 DataGridView 单元格内容点击事件中，我们将检查点击的列是否等于删除。如果点击的列是删除，我们将显示确认框以供用户确认是否删除。如果用户确认删除，我们将删除选定的 Student 详细信息。
private void Master_shanuDGV_CellContentClick(object sender, DataGridViewCellEventArgs e)
{
if (Master_shanuDGV.Columns[e.ColumnIndex].Name == "Edit")
{
try
{
string studentID = ds.Tables[0].Rows[e.RowIndex]["StdNO"].ToString();
frmSudentAdd obj = new frmSudentAdd(studentID);
obj.ShowDialog();
bindData();
}
catch (Exception ex)
{}
}
else if (Master_shanuDGV.Columns[e.ColumnIndex].Name == "Delete")
{
try
{
string studentID = ds.Tables[0].Rows[e.RowIndex]["StdNO"].ToString();
if (MessageBox.Show("Are You Sure to Delete Student Details ?",
"Delete Student", MessageBoxButtons.YesNo) == DialogResult.Yes)
{
SortedDictionary < string, string > sd =
new SortedDictionary < string, string > ()
{};
sd.Add("@std_ID", studentID);
DataSet ds1 = new DataSet();
ds1 = bizObj.SelectList("USP_Student_Delete", sd);
if (ds1.Tables[0].Rows.Count > 0)
{
string result = ds1.Tables[0].Rows[0][0].ToString();
if (result == "Deleted")
{
MessageBox.Show("Student Deleted Successful, Thank You!",
"Successful", MessageBoxButtons.OK, MessageBoxIcon.Information);
bindData();
}
}
}
}
catch (Exception ex)
{}
}
}
关注点
注意：在 Bin/Debug 文件夹中，您可以找到“DBConnection.txt”文本文件。请根据您的本地连接更改 SQL 连接。此外，在 SQLDALClass 类文件中，我将创建一个文本文件来存储 SQL 连接字符串。请将连接字符串更改为您的 SQL 连接。
历史
- 2015年12月14日：初始版本