C # manipulate Excel data additions and deletions to check examples _ Practical tips

Source: Internet
Author: User
Tags file handling
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;
}
}
}
Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.