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)){mandType = 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;mandText = 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文件类；（后续更新方法及代码…）
