C # operation Excel data additions and deletions to check.
First create the Exceldb.xlsx file and add two sheets.
Work Table 1:
UserInfo tables, fields: UserId, UserName, age, address, createtime.
Work Table 2:
Order table, Fields: OrderNo, ProductName, Quantity, Money, saledate.
1, create ExcelHelper.cs class, Excel File Processing class
Copy Code code 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 Handling classes
</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>
Executing an SQL statement without parameters
</summary>
<param name= "SQL" >sql statement </param>
<returns> returns the number of rows affected by the SQL statement </returns>
public static int ExecuteCommand (String sql)
{
OleDbCommand cmd = new OleDbCommand (sql, Connection);
int result = cmd. ExecuteNonQuery ();
Connection. Close ();
return result;
}
<summary>
Execute an SQL statement with parameters
</summary>
<param name= "SQL" >sql statement </param>
<param name= "values" > Parameter set </param>
<returns> returns the number of rows affected by the SQL statement </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>
Returns an SQL statement with no arguments for a single value
</summary>
<param name= "SQL" >sql statement </param>
<returns> returns the number of rows queried by SQL statement </returns>
public static int getscalar (String sql)
{
OleDbCommand cmd = new OleDbCommand (sql, Connection);
int result = Convert.ToInt32 (cmd. ExecuteScalar ());
Connection. Close ();
return result;
}
<summary>
Returns 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 statement </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>
Execute query no parameter SQL statement
</summary>
<param name= "SQL" >sql statement </param>
<returns> Return Data Set </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 query with parameter SQL statement
</summary>
<param name= "SQL" >sql statement </param>
<param name= "Parameters" > Parameter set </param>
<returns> Return Data Set </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 entity classes
2.1 Create UserInfo.cs class, user information entity class.
Copy Code code as follows:
Using System;
Using System.Collections.Generic;
Using System.Linq;
Using System.Text;
Using System.Data;
Namespace Mystudy.model
{
<summary>
User Information entity classes
</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>
Converting a 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, order entity class.
Copy Code code 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>
Converting a 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 business logic Class
3.1 Create UserInfoBLL.cs class, user information business class.
Copy Code code 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 class
</summary>
public class USERINFOBLL
{
<summary>
Query 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>
Get total number of users
</summary>
public int Getusercount ()
{
int result = 0;
String sql = "Select COUNT (*) from [userinfo$]";
result = Excelhelper.getscalar (SQL);
return result;
}
<summary>
New 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, Order business class
Copy Code code 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 Business Class
</summary>
public class ORDERBLL
{
<summary>
Query 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>
Get Total Orders
</summary>
public int Getordercount ()
{
int result = 0;
String sql = "Select COUNT (*) from [order$]";
result = Excelhelper.getscalar (SQL);
return result;
}
<summary>
New 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 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 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;
}
}
}
