1、C#常用的链接数据操作的函数，也是最实用的两个，你就可以对数据操作“任意妄为”！
<1>返回数据表格DataTable：用户查询
public static DataTable ExecuteDataTable(string connStr, string sql, CommandType commandType)
{
DataTable data = new DataTable();
using (SqlConnection connection = new SqlConnection(connStr))
{
using (SqlCommand command = new SqlCommand(sql, connection))
{
command.CommandType = commandType;
SqlDataAdapter adapter = new SqlDataAdapter(command);
adapter.Fill(data);
}
}
return data;
}
<2>执行语句，返回 int ：用于删除、更新
public static int ExecuteNonQuery(string connectionString, string commandText, CommandType commandType)
{
using (SqlConnection connection = new SqlConnection(connectionString))
{
SqlCommand command = new SqlCommand();
bool mustCloseConnection = false;
if (connection.State != ConnectionState.Open)
{
mustCloseConnection = true;
connection.Open();
}
else
{
mustCloseConnection = false;
}
command.Connection = connection;
command.CommandText = commandText;
int num = command.ExecuteNonQuery();
command.Parameters.Clear();
if (mustCloseConnection)
{
connection.Close();
}
return num;
}
}
2、.txt文件操作
<1>读取文本数据
/// <summary>
/// 读取文本数据
/// </summary>
/// <param name="path"></param>
/// <returns></returns>
public static string Read(string path)
{
string content = "";
StreamReader sr = null;
if (!File.Exists(path))
{
return content;
}
try
{
sr = new StreamReader(path, Encoding.UTF8);
String rows;
while ((rows = sr.ReadLine()) != null)
{
content = content + (rows + "\r\n");
}
}
catch (Exception ex)
{
//LogHelper.WriteErrorLog(ex.Message);
}
finally
{
sr.Close();
}
return content;
}
<2>覆盖写入数据
/// <summary>
/// 覆盖写入数据
/// </summary>
/// <param name="path"></param>
/// <param name="content"></param>
/// <returns></returns>
public static bool Write(string path, string content)
{
try
{
FileStream fs = new FileStream(path, FileMode.Create);
StreamWriter sw = new StreamWriter(fs);
//开始写入
sw.Write(content);
//清空缓冲区
sw.Flush();
//关闭流
sw.Close();
fs.Close();
return true;
}
catch (Exception ex)
{
return false;
}
}
<3>末尾追加数据
/// <summary>
/// 末尾追加文本
/// </summary>
/// <param name="path"></param>
/// <param name="content"></param>
/// <returns></returns>
public static bool AddData(string path, string content)
{
string pathFile = path + @"log\" + DateTime.Now.ToShortDateString().Replace("/", "-") + ".txt";
StreamWriter sw = null;
FileStream fs = null;
try
{
if (!File.Exists(pathFile))
{
fs = new FileStream(pathFile, FileMode.Create, FileAccess.Write);
sw = new StreamWriter(fs);
sw.WriteLine(content);
sw.Close();
fs.Close();
}
else
{
sw = File.AppendText(pathFile);
sw.Write("\r\n" + content + "\r\n");
sw.Flush();
sw.Close();
}
return true;
}
catch (Exception ex)
{
return false;
}
finally
{
if (fs != null)
{
fs.Close();
}
if (sw != null)
{
sw.Close();
}
}
}
3、excel文件操作
添加引用第三方动态库，Aspose.Cells.dll(excel),Aspose.Words.dll(Word)
<1>excel数据导入
/// <summary>
/// 简化对Aspose.Cells控件的使用，实现对Excel数据导入
/// </summary>
public class AsposeExcel
{
/// <summary>
/// 把所有Sheet里面的内容，分别导入到不同的DataTable对象里面
/// </summary>
/// <param name="filepath">Excel文件的全路径</param>
/// <param name="datatables">DataTable对象集合</param>
/// <param name="error">错误信息:返回错误信息，没有错误返回""</param>
/// <returns></returns>
public static bool ExcelFileToDataTables(string filepath, out DataTable[] datatables, out string error)
{
bool exportColumnName = true;
return ExcelFileToDataTables(filepath, out datatables, exportColumnName, out error);
}
/// <summary>
/// 把所有Sheet里面的内容，分别导入到不同的DataTable对象里面
/// </summary>
/// <param name="filepath">Excel文件的全路径</param>
/// <param name="datatables">DataTable对象集合</param>
/// <param name="error">错误信息:返回错误信息，没有错误返回""</param>
/// <returns></returns>
public static bool ExcelFileToDataTables(string filepath, out DataTable[] datatables, bool exportColumnName, out string error)
{
error = "";
datatables = null;
int nSheetsCount = 0;
try
{
if (File.Exists(filepath) == false)
{
error = "文件不存在";
datatables = null;
return false;
}
Aspose.Cells.Workbook workbook = new Aspose.Cells.Workbook(filepath);
nSheetsCount = workbook.Worksheets.Count;
datatables = new DataTable[nSheetsCount];
for (int i = 0; i < nSheetsCount; i++)
{
Aspose.Cells.Worksheet worksheet = workbook.Worksheets[i];
try
{
//为了避免有个别Sheet出现错误而导致全部不能出来，这里进行忽略处理
datatables[i] = worksheet.Cells.ExportDataTableAsString(0, 0, worksheet.Cells.MaxRow + 1, worksheet.Cells.MaxColumn + 1, exportColumnName);
datatables[i].TableName = worksheet.Name;//记录Sheet的名称
}
catch (System.Exception e)
{
error = e.Message;
continue;
}
}
return true;
}
catch (System.Exception e)
{
error = e.Message;
return false;
}
}
/// <summary>
/// Excel文件转换为DataSet.
/// </summary>
/// <param name="filepath">Excel文件的全路径</param>
/// <param name="dataset">DataSet:返回值</param>
/// <param name="error">错误信息:返回错误信息，没有错误返回""</param>
/// <returns>true:函数正确执行 false:函数执行错误</returns>
public static bool ExcelFileToDataSet(string filepath, out DataSet dataset, out string error)
{
dataset = new System.Data.DataSet();
DataTable[] datatables = null;
if (ExcelFileToDataTables(filepath, out datatables, out error))
{
dataset.Tables.AddRange(datatables);
return true;
}
else
{
error = "ExcelFileToDataSet: " + error;
return false;
}
}
}
<2>excel数据导出
/// <summary>
/// 把DataTabel转换成Excel文件
/// </summary>
/// <param name="datatable">DataTable对象</param>
/// <param name="filepath">目标文件路径,Excel文件的全路径</param>
/// <param name="error">错误信息:返回错误信息，没有错误返回""</param>
/// <returns></returns>
public static bool DataTableToExcel2(DataTable datatable, string filepath, out string error)
{
error = "";
Aspose.Cells.Workbook wb = new Aspose.Cells.Workbook();
try
{
if (datatable == null)
{
error = "DataTableToExcel:datatable 为空";
return false;
}
//为单元格添加样式
Aspose.Cells.Style style = wb.Styles[wb.Styles.Add()];
//设置居中
style.HorizontalAlignment = Aspose.Cells.TextAlignmentType.Center;
//设置背景颜色
style.ForegroundColor = System.Drawing.Color.FromArgb(153, 204, 0);
style.Pattern = BackgroundType.Solid;
style.Font.IsBold = true;
int rowIndex = 0;
for (int i = 0; i < datatable.Columns.Count; i++)
{
DataColumn col = datatable.Columns[i];
string columnName = col.Caption ?? col.ColumnName;
wb.Worksheets[0].Cells[rowIndex, i].PutValue(columnName);
wb.Worksheets[0].Cells[rowIndex, i].SetStyle(style);
}
rowIndex++;
foreach (DataRow row in datatable.Rows)
{
for (int i = 0; i < datatable.Columns.Count; i++)
{
wb.Worksheets[0].Cells[rowIndex, i].PutValue(row[i].ToString());
}
rowIndex++;
}
for (int k = 0; k < datatable.Columns.Count; k++)
{
wb.Worksheets[0].AutoFitColumn(k, 0, 150);
}
wb.Worksheets[0].FreezePanes(1, 0, 1, datatable.Columns.Count);
wb.Save(filepath);
return true;
}
catch (Exception e)
{
error = error + " DataTableToExcel: " + e.Message;
return false;
}
}
4、封装：独立创建类库项目，放入数据操作类，生成DLL动态库，其它项目直接引用DLL使用；
注：常用的、通用的对象类，建议都可以放入一个动态库中使用；可大大减少往后项目开发时间！
如：.txt 文件操作类，导出、导入execl文件类；（后续更新方法及代码…）
407
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
