【C】C#操作Excel数据增删改查示例-Excel数据增删改查

欢迎您访问我爱IT技术网，今天小编为你分享的编程技术是：【C#操作Excel数据增删改查示例】，下面是详细的讲解！

C#操作Excel数据增删改查示例

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.OleDb;
using System.Data;

namespace MyStudy.DAL
{
/// <summary>
/// Excel文件处理类
/// </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>
/// 执行无参数的SQL语句
/// </summary>
/// <param name="sql">SQL语句</param>
/// <returns>返回受SQL语句影响的行数</returns>
public static int ExecuteCommand(string sql)
{
OleDbCommand cmd=new OleDbCommand(sql, Connection);
int result=cmd.ExecuteNonQuery();
connection.Close();
return result;
}

/// <summary>
/// 执行有参数的SQL语句
/// </summary>
/// <param name="sql">SQL语句</param>
/// <param name="values">参数集合</param>
/// <returns>返回受SQL语句影响的行数</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>
/// 返回单个值无参数的SQL语句
/// </summary>
/// <param name="sql">SQL语句</param>
/// <returns>返回受SQL语句查询的行数</returns>
public static int GetScalar(string sql)
{
OleDbCommand cmd=new OleDbCommand(sql, Connection);
int result=Convert.ToInt32(cmd.ExecuteScalar());
connection.Close();
return result;
}

/// <summary>
/// 返回单个值有参数的SQL语句
/// </summary>
/// <param name="sql">SQL语句</param>
/// <param name="parameters">参数集合</param>
/// <returns>返回受SQL语句查询的行数</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>
/// 执行查询无参数SQL语句
/// </summary>
/// <param name="sql">SQL语句</param>
/// <returns>返回数据集</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>
/// 执行查询有参数SQL语句
/// </summary>
/// <param name="sql">SQL语句</param>
/// <param name="parameters">参数集合</param>
/// <returns>返回数据集</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;
}
}
}

关于C#操作Excel数据增删改查示例的用户互动如下：

　　相关问题：

　　答： >>详细

　　相关问题：

　　答： >>详细

　　相关问题：

　　答： >>详细