C # example of adding, deleting, modifying, and querying Excel Data

C # add, delete, modify, and query Excel Data.

First, create the exceldb.xlsx file and add two worksheets.

Worksheet 1:

UserInfo table, field: UserId, UserName, Age, Address, CreateTime.

Worksheet 2:

Order table, field: OrderNo, ProductName, Quantity, Money, SaleDate.

1. Create an ExcelHelper. cs class and an Excel file processing class.
Copy codeThe Code is as follows:
Using System;
Using System. Collections. Generic;
Using System. Linq;
Using System. Text;
Using System. Data. OleDb;
Using System. Data;

Namespace MyStudy. DAL
{
/// <Summary>
/// Excel File Processing class
/// </Summary>
Public class ExcelHelper
{
Private static string fileName = AppDomain. CurrentDomain. SetupInformation. ApplicationBase + @ "/ExcelFile/ExcelDB.xlsx ";

Private static OleDbConnection connection;
Public static OleDbConnection Connection
{
Get
{
String connectionString = "";
String fileType = System. IO. Path. GetExtension (fileName );
If (string. IsNullOrEmpty (fileType) return null;
If (fileType = ". xls ")
{
ConnectionString = "Provider = Microsoft. jet. OLEDB.4.0; "+" Data Source = "+ fileName +"; "+"; Extended Properties = \ "Excel 8.0; HDR = YES; IMEX = 2 \"";
}
Else
{
ConnectionString = "Provider = Microsoft. ACE. OLEDB.12.0; "+" Data Source = "+ fileName +"; "+"; Extended Properties = \ "Excel 12.0; HDR = YES; IMEX = 2 \"";
}
If (connection = null)
{
Connection = new OleDbConnection (connectionString );
Connection. Open ();
}
Else if (connection. State = System. Data. ConnectionState. Closed)
{
Connection. Open ();
}
Else if (connection. State = System. Data. ConnectionState. Broken)
{
Connection. Close ();
Connection. Open ();
}
Return connection;
}
}

/// <Summary>
/// Execute SQL statements without Parameters
/// </Summary>
/// <Param name = "SQL"> SQL statement </param>
/// <Returns> returns the number of rows affected by SQL statements. </returns>
Public static int ExecuteCommand (string SQL)
{
OleDbCommand cmd = new OleDbCommand (SQL, Connection );
Int result = cmd. ExecuteNonQuery ();
Connection. Close ();
Return result;
}

/// <Summary>
/// Execute SQL statements with Parameters
/// </Summary>
/// <Param name = "SQL"> SQL statement </param>
/// <Param name = "values"> parameter set </param>
/// <Returns> returns the number of rows affected by SQL statements. </returns>
Public static int ExecuteCommand (string SQL, params OleDbParameter [] values)
{
OleDbCommand cmd = new OleDbCommand (SQL, Connection );
Cmd. Parameters. AddRange (values );
Int result = cmd. ExecuteNonQuery ();
Connection. Close ();
Return result;
}

/// <Summary>
/// Return an SQL statement with no parameters for a single value
/// </Summary>
/// <Param name = "SQL"> SQL statement </param>
/// <Returns> returns the number of rows queried by SQL statements. </returns>
Public static int GetScalar (string SQL)
{
OleDbCommand cmd = new OleDbCommand (SQL, Connection );
Int result = Convert. ToInt32 (cmd. ExecuteScalar ());
Connection. Close ();
Return result;
}

/// <Summary>
/// Return an SQL statement with parameters for a single value
/// </Summary>
/// <Param name = "SQL"> SQL statement </param>
/// <Param name = "parameters"> parameter set </param>
/// <Returns> returns the number of rows queried by SQL statements. </returns>
Public static int GetScalar (string SQL, params OleDbParameter [] parameters)
{
OleDbCommand cmd = new OleDbCommand (SQL, Connection );
Cmd. Parameters. AddRange (parameters );
Int result = Convert. ToInt32 (cmd. ExecuteScalar ());
Connection. Close ();
Return result;
}

/// <Summary>
/// Run the SQL statement without Parameters
/// </Summary>
/// <Param name = "SQL"> SQL statement </param>
/// <Returns> return dataset </returns>
Public static DataSet GetReader (string SQL)
{
OleDbDataAdapter da = new OleDbDataAdapter (SQL, Connection );
DataSet ds = new DataSet ();
Da. Fill (ds, "UserInfo ");
Connection. Close ();
Return ds;
}

/// <Summary>
/// Execute the SQL statement with parameters in the query
/// </Summary>
/// <Param name = "SQL"> SQL statement </param>
/// <Param name = "parameters"> parameter set </param>
/// <Returns> return dataset </returns>
Public static DataSet GetReader (string SQL, params OleDbParameter [] parameters)
{
OleDbDataAdapter da = new OleDbDataAdapter (SQL, Connection );
Da. SelectCommand. Parameters. AddRange (parameters );
DataSet ds = new DataSet ();
Da. Fill (ds );
Connection. Close ();
Return ds;
}
}
}

2. Create an object class

2.1 create a UserInfo. cs class and a user information entity class.
Copy codeThe Code is as follows:
Using System;
Using System. Collections. Generic;
Using System. Linq;
Using System. Text;
Using System. Data;

Namespace MyStudy. Model
{
/// <Summary>
/// User Information Entity class
/// </Summary>
Public class UserInfo
{
Public int UserId {get; set ;}
Public string UserName {get; set ;}
Public int? Age {get; set ;}
Public string Address {get; set ;}
Public DateTime? CreateTime {get; set ;}

/// <Summary>
/// Convert the DataTable into List Data
/// </Summary>
Public static List <UserInfo> ToList (DataSet dataSet)
{
List <UserInfo> userList = new List <UserInfo> ();
If (dataSet! = Null & dataSet. Tables. Count> 0)
{
Foreach (DataRow row in dataSet. Tables [0]. Rows)
{
UserInfo user = new UserInfo ();
If (dataSet. Tables [0]. Columns. Contains ("UserId ")&&! Convert. IsDBNull (row ["UserId"])
User. UserId = Convert. ToInt32 (row ["UserId"]);

If (dataSet. Tables [0]. Columns. Contains ("UserName ")&&! Convert. IsDBNull (row ["UserName"])
User. UserName = (string) row ["UserName"];

If (dataSet. Tables [0]. Columns. Contains ("Age ")&&! Convert. IsDBNull (row ["Age"])
User. Age = Convert. ToInt32 (row ["Age"]);

If (dataSet. Tables [0]. Columns. Contains ("Address ")&&! Convert. IsDBNull (row ["Address"])
User. Address = (string) row ["Address"];

If (dataSet. Tables [0]. Columns. Contains ("CreateTime ")&&! Convert. IsDBNull (row ["CreateTime"])
User. CreateTime = Convert. ToDateTime (row ["CreateTime"]);

UserList. Add (user );
}
}
Return userList;
}
}
}

2.2 create Order. cs class and Order entity class.
Copy codeThe Code is as follows:
Using System;
Using System. Collections. Generic;
Using System. Linq;
Using System. Text;
Using System. Data;

Namespace MyStudy. Model
{
/// <Summary>
/// Order entity class
/// </Summary>
Public class Order
{
Public string OrderNo {get; set ;}
Public string ProductName {get; set ;}
Public int? Quantity {get; set ;}
Public decimal? Money {get; set ;}
Public DateTime? SaleDate {get; set ;}

/// <Summary>
/// Convert the DataTable into List Data
/// </Summary>
Public static List <Order> ToList (DataSet dataSet)
{
List <Order> orderList = new List <Order> ();
If (dataSet! = Null & dataSet. Tables. Count> 0)
{
Foreach (DataRow row in dataSet. Tables [0]. Rows)
{
Order order = new Order ();
If (dataSet. Tables [0]. Columns. Contains ("OrderNo ")&&! Convert. IsDBNull (row ["OrderNo"])
Order. OrderNo = (string) row ["OrderNo"];

If (dataSet. Tables [0]. Columns. Contains ("ProductName ")&&! Convert. IsDBNull (row ["ProductName"])
Order. ProductName = (string) row ["ProductName"];

If (dataSet. Tables [0]. Columns. Contains ("Quantity ")&&! Convert. IsDBNull (row ["Quantity"])
Order. Quantity = Convert. ToInt32 (row ["Quantity"]);

If (dataSet. Tables [0]. Columns. Contains ("Money ")&&! Convert. IsDBNull (row ["Money"])
Order. Money = Convert. ToDecimal (row ["Money"]);

If (dataSet. Tables [0]. Columns. Contains ("SaleDate ")&&! Convert. IsDBNull (row ["SaleDate"])
Order. SaleDate = Convert. ToDateTime (row ["SaleDate"]);

OrderList. Add (order );
}
}
Return orderList;
}
}
}

3. Create a business logic class

3.1 create UserInfoBLL. cs class and user information service class.
Copy codeThe Code is as follows:
Using System;
Using System. Collections. Generic;
Using System. Linq;
Using System. Text;
Using System. Data;
Using MyStudy. Model;
Using MyStudy. DAL;
Using System. Data. OleDb;

Namespace MyStudy. BLL
{
/// <Summary>
/// User Information Service
/// </Summary>
Public class UserInfoBLL
{
/// <Summary>
/// Query the user list
/// </Summary>
Public List <UserInfo> GetUserList ()
{
List <UserInfo> userList = new List <UserInfo> ();
String SQL = "SELECT * FROM [UserInfo $]";
DataSet dateSet = ExcelHelper. GetReader (SQL );
UserList = UserInfo. ToList (dateSet );
Return userList;
}

/// <Summary>
/// Obtain the total number of users
/// </Summary>
Public int GetUserCount ()
{
Int result = 0;
String SQL = "SELECT COUNT (*) FROM [UserInfo $]";
Result = ExcelHelper. GetScalar (SQL );
Return result;
}

/// <Summary>
/// Add user information
/// </Summary>
Public int AddUserInfo (UserInfo param)
{
Int result = 0;
String SQL = "INSERT INTO [UserInfo $] (UserId, UserName, Age, Address, CreateTime) VALUES (@ UserId, @ UserName, @ Age, @ Address, @ CreateTime )";
OleDbParameter [] oleDbParam = new OleDbParameter []
{
New OleDbParameter ("@ UserId", param. UserId ),
New OleDbParameter ("@ UserName", param. UserName ),
New OleDbParameter ("@ Age", param. Age ),
New OleDbParameter ("@ Address", param. Address ),
New OleDbParameter ("@ CreateTime", param. CreateTime)
};
Result = ExcelHelper. ExecuteCommand (SQL, oleDbParam );
Return result;
}

/// <Summary>
/// Modify user information
/// </Summary>
Public int UpdateUserInfo (UserInfo param)
{
Int result = 0;
If (param. UserId> 0)
{
String SQL = "UPDATE [UserInfo $] SET UserName = @ UserName, Age = @ Age, Address = @ Address WHERE UserId = @ UserId ";
OleDbParameter [] sqlParam = new OleDbParameter []
{
New OleDbParameter ("@ UserId", param. UserId ),
New OleDbParameter ("@ UserName", param. UserName ),
New OleDbParameter ("@ Age", param. Age ),
New OleDbParameter ("@ Address", param. Address)
};
Result = ExcelHelper. ExecuteCommand (SQL, sqlParam );
}
Return result;
}

/// <Summary>
/// Delete user information
/// </Summary>
Public int DeleteUserInfo (UserInfo param)
{
Int result = 0;
If (param. UserId> 0)
{
String SQL = "DELETE [UserInfo $] WHERE UserId = @ UserId ";
OleDbParameter [] sqlParam = new OleDbParameter []
{
New OleDbParameter ("@ UserId", param. UserId ),
};
Result = ExcelHelper. ExecuteCommand (SQL, sqlParam );
}
Return result;
}
}
}

3.2 Create OrderBLL. cs class and order service class
Copy codeThe Code is as follows:
Using System;
Using System. Collections. Generic;
Using System. Linq;
Using System. Text;
Using System. Data;
Using MyStudy. Model;
Using MyStudy. DAL;
Using System. Data. OleDb;

Namespace MyStudy. BLL
{
/// <Summary>
/// Order service type
/// </Summary>
Public class OrderBLL
{
/// <Summary>
/// Query the order list
/// </Summary>
Public List <Order> GetOrderList ()
{
List <Order> orderList = new List <Order> ();
String SQL = "SELECT * FROM [Order $]";
DataSet dateSet = ExcelHelper. GetReader (SQL );
OrderList = Order. ToList (dateSet );
Return orderList;
}

/// <Summary>
/// Obtain the total number of orders
/// </Summary>
Public int GetOrderCount ()
{
Int result = 0;
String SQL = "SELECT COUNT (*) FROM [Order $]";
Result = ExcelHelper. GetScalar (SQL );
Return result;
}

/// <Summary>
/// Add an order
/// </Summary>
Public int AddOrder (Order param)
{
Int result = 0;
String SQL = "INSERT INTO [Order $] (OrderNo, ProductName, Quantity, Money, SaleDate) VALUES (@ OrderNo, @ ProductName, @ Quantity, @ Money, @ SaleDate )";
OleDbParameter [] oleDbParam = new OleDbParameter []
{
New OleDbParameter ("@ OrderNo", param. OrderNo ),
New OleDbParameter ("@ ProductName", param. ProductName ),
New OleDbParameter ("@ Quantity", param. Quantity ),
New OleDbParameter ("@ Money", param. Money ),
New OleDbParameter ("@ SaleDate", param. SaleDate)
};
Result = ExcelHelper. ExecuteCommand (SQL, oleDbParam );
Return result;
}

/// <Summary>
/// Modify the order
/// </Summary>
Public int UpdateOrder (Order param)
{
Int result = 0;
If (! String. IsNullOrEmpty (param. OrderNo ))
{
String SQL = "UPDATE [Order $] SET ProductName = @ ProductName, Quantity = @ Quantity, Money = @ Money WHERE OrderNo = @ OrderNo ";
OleDbParameter [] sqlParam = new OleDbParameter []
{
New OleDbParameter ("@ OrderNo", param. OrderNo ),
New OleDbParameter ("@ ProductName", param. ProductName ),
New OleDbParameter ("@ Quantity", param. Quantity ),
New OleDbParameter ("@ Money", param. Money)
};
Result = ExcelHelper. ExecuteCommand (SQL, sqlParam );
}
Return result;
}

/// <Summary>
/// Delete the order
/// </Summary>
Public int DeleteOrder (Order param)
{
Int result = 0;
If (! String. IsNullOrEmpty (param. OrderNo ))
{
String SQL = "DELETE [Order $] WHERE OrderNo = @ OrderNo ";
OleDbParameter [] sqlParam = new OleDbParameter []
{
New OleDbParameter ("@ OrderNo", param. OrderNo ),
};
Result = ExcelHelper. ExecuteCommand (SQL, sqlParam );
}
Return result;
}
}
}