C#手工DataSet&DataTable&SqlHelper.cs&Common.cs
SqlHelper.cs
using System; using System.Configuration;//这个一定要从项目的引用右键把其添加进来 using System.Data; using System.Data.SqlClient; namespace MySelfClassLibrary { class SQLHelper { //static string serverIP = ConfigurationManager.AppSettings["serverIP"];//准备(一) //static string connDbPwd = ConfigurationManager.AppSettings["connDbPwd"];//准备(二) private static string connStr = ConfigurationManager.ConnectionStrings["connStr"].ConnectionString;//准备(三) //private static readonly string connStr = string.Format(connStrTmp, serverIP, connDbPwd);//组装 public static bool TableExists(string tableName) { string connectionString = connStr; bool tableExists = false; using (SqlConnection connection = new SqlConnection(connectionString)) { connection.Open(); // 构建SQL查询，检查表是否存在 string sql = $@" SELECT COUNT(*) FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = '{tableName.ToUpper()}'"; using (SqlCommand command = new SqlCommand(sql, connection)) { int count = Convert.ToInt32(command.ExecuteScalar()); tableExists = count > 0; } } return tableExists; } //判断根据上面的库的连接字符串数据库是否可达 可连接成功 public static bool TestConntion() { bool b = false; SqlConnection conn = new SqlConnection(connStr); try { conn.Open(); //b = true; b = conn.State == ConnectionState.Open; } catch (Exception ex) { b = false; string msg = "Error: " + ex.Message; } finally { conn.Close(); } //return b;//为学习用下面的 if (conn.State == ConnectionState.Closed || conn.State == ConnectionState.Broken) { //连接不可用 return b; } else { //连接可用 return b; } } public static string GetConnectionStr() { return connStr; } //得到DataSet public static DataSet GetDataSet(string sql, params SqlParameter[] para) { DataSet ds = new DataSet(); using (SqlDataAdapter da = new SqlDataAdapter(sql, connStr)) { if (para != null) { da.SelectCommand.Parameters.AddRange(para); } da.Fill(ds); } return ds; } //得到DataTable方法(一) public static DataTable GetDataTable(string sql, params SqlParameter[] para) { DataTable dt = new DataTable(); using (SqlDataAdapter da = new SqlDataAdapter(sql, connStr)) { if (para != null)//不能用.length!=0来判断 { da.SelectCommand.Parameters.AddRange(para); } da.Fill(dt); } return dt; } //得到DataTable方法(二) public static DataTable GetDataTable2(string sql, params SqlParameter[] para) { DataTable dt = new DataTable(); using (SqlConnection conn = new SqlConnection(connStr)) { using (SqlCommand cmd = new SqlCommand(sql, conn)) { if (para != null) { cmd.Parameters.AddRange(para); } //conn.open();不需要 using (SqlDataAdapter sda = new SqlDataAdapter(cmd)) { sda.Fill(dt); } } } return dt; } //执行非查询SQL语句ExecuteNonQuery返回受影响的行数 public static int ExecuteNonQuery(string sql, params SqlParameter[] para) { using (SqlConnection conn = new SqlConnection(connStr)) { using (SqlCommand cmd = new SqlCommand(sql, conn)) { if (para != null) { cmd.Parameters.AddRange(para); } conn.Open(); return cmd.ExecuteNonQuery(); } } } //通过ExecuteScalar()得到左上角单元格的object类型的值 public static object ExecuteScalar(string sql, params SqlParameter[] para) { using (SqlConnection conn = new SqlConnection(connStr)) { using (SqlCommand cmd = new SqlCommand(sql, conn)) { if (para != null) { cmd.Parameters.AddRange(para); } conn.Open(); return cmd.ExecuteScalar(); } } } //SqlDataReader向前的阅读器 public static SqlDataReader ExecuteReader(string sql, params SqlParameter[] para) { using (SqlConnection conn = new SqlConnection(connStr)) { using (SqlCommand cmd = new SqlCommand(sql, conn)) { if (para != null) { cmd.Parameters.AddRange(para); } try { conn.Open(); return cmd.ExecuteReader(CommandBehavior.CloseConnection);//是个枚举,好记 ( 空格一下就出来了 } catch { conn.Close(); conn.Dispose(); throw; } } } } /// <summary> /// 一次性把DataTable插入到数据库的表 /// </summary> /// <param name="dt">要插入的DataTable</param> /// <param name="dbTableName">被插入数据库的表</param> /// <returns>布尔值插入是否成功</returns> public static bool SBCopy(DataTable dt, string dbTableName) { SqlTransaction tran = null;//声明一个事务对象 try { using (SqlConnection conn = new SqlConnection(connStr)) { conn.Open();//打开链接 using (tran = conn.BeginTransaction()) { //using (SqlBulkCopy sbCopy = new SqlBulkCopy(conn, SqlBulkCopyOptions.Default, tran)) using (SqlBulkCopy sbCopy = new SqlBulkCopy(conn)) { sbCopy.DestinationTableName = dbTableName; //指定服务器上目标表的名称 //加 解决 对应异常 插入失败的问题 foreach (DataColumn col in dt.Columns) { sbCopy.ColumnMappings.Add(col.ColumnName, col.ColumnName); } //sbCopy.BulkCopyTimeout = 0; //加结 sbCopy.WriteToServer(dt); tran.Commit(); //提交事务 return true; //返回True 执行成功！ } } } } catch //(Exception ex) { if (tran != null) tran.Rollback(); //LogHelper.Add(ex); return false;//返回False 执行失败！ } } } }
Common.cs
using System.Data; namespace MySelfClassLibrary { class Common { public static DataSet GetDataSetByHand() { DataSet ds = new DataSet(); ds.Tables.Add(GetDataTableByHand()); return ds; } public static DataTable GetDataTableByHand() { //创建表对象 DataTable dt = new DataTable { TableName = "baseInfo"//这个很重要,一定要和fastreport Designer设计器中定义字典时的dictionary1.frd中定义的表名对应起来 }; //设置表列 dt.Columns.Add("name");//列的名字也要和字典对应起来,我就犯一个一错误,字典中用的是name,而程序中定义的Datatalbe用的是"姓名"就对应不上了,导致报错,报表无法显示 dt.Columns.Add("sex"); dt.Columns.Add("age"); dt.Columns.Add("score"); //先插一行试试 DataRow dr1 = dt.NewRow(); dr1["name"] = "张三"; dr1["sex"] = "男"; dr1["age"] = "18"; dr1["score"] = "18"; dt.Rows.Add(dr1); for (int i = 0; i < 5; i++) { DataRow dr = dt.NewRow(); dr["name"] = "李四~" + i.ToString(); dr["sex"] = i % 2 == 0 ? "女" : "男"; dr["age"] = i + 20; dr["score"] = i + 100; dt.Rows.Add(dr); } return dt; } } }
