1 连接Sql Server的ConnectionString:
Data Source=.;Initial Catalog=ViewVideo;User ID=sa;Password=XXXXXX;
2 连接MySql的ConnectionString:
Database=dbname;Data Source=192.168.1.1;Port=3306;User Id=root;Password=****;Charset=utf8;TreatTinyAsBoolean=false;
3 连接Oracle的ConnectionString：
Data Source=(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.117)(PORT = 1521))
)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = orcl.192.168.1.117)
)
);User Id=scott;Password=XXXXXX //不能有';'
OracleHelper.cs:
1 public class OracleHepler 2 { 3 //链接字符串 4 private static readonly string connStr = ConfigurationManager.ConnectionStrings["dbconnStr"].ConnectionString; 5 6 /// <summary> 7 /// 创建链接 8 /// </summary> 9 /// <returns>链接</returns> 10 public static OracleConnection CreateConnection() 11 { 12 OracleConnection conn = new OracleConnection(connStr); 13 conn.Open(); 14 return conn; 15 } 16 17 /// <summary> 18 /// 使用亦有链接的 非查询 19 /// </summary> 20 /// <param name="conn">链接</param> 21 /// <param name="sql">sql文本</param> 22 /// <param name="parameters">sql参数</param> 23 /// <returns>受影响行数</returns> 24 public static int ExecuteNonQuery(OracleConnection conn, string sql, params OracleParameter[] parameters) 25 { 26 using (OracleCommand cmd = new OracleCommand(sql, conn)) 27 { 28 cmd.Parameters.AddRange(parameters); 29 return cmd.ExecuteNonQuery(); 30 } 31 } 32 33 /// <summary> 34 /// 自己创建链接的 非查询 35 /// </summary> 36 /// <param name="sql">sql文本</param> 37 /// <param name="parameters">sql参数</param> 38 /// <returns>受影响行数</returns> 39 public static int ExecuteNonQuery(string sql, params OracleParameter[] parameters) 40 { 41 using (OracleConnection conn = CreateConnection()) 42 { 43 return ExecuteNonQuery(conn, sql, parameters); 44 } 45 } 46 47 /// <summary> 48 /// 使用已有链接的 带存储过程的Insert非查询，直接写存储过程参数 49 /// </summary> 50 /// <param name="conn">已有链接</param> 51 /// <param name="proName">存储过程名称</param> 52 /// <param name="strInsertSQL">执行插入的sql语句，或者其他操作sql语句</param> 53 /// <param name="seqName">序列的名称</param> 54 /// <returns>当前序列号，即ID</returns> 55 public static object ExecuteNonQueryWithProduce(OracleConnection conn, string proName, string strInsertSQL, string seqName) 56 { 57 using (OracleCommand cmd = new OracleCommand(proName, conn)) //命令中执行的不在是sql，而是存储过程 58 { 59 try 60 { 61 cmd.CommandType = CommandType.StoredProcedure; //标记该命令的类型不是sql，而是存储过程 62 //存储过程中有参数名称，以及设置对应参数的值 63 cmd.Parameters.Add(new OracleParameter("strInsertSQL", OracleDbType.Varchar2) { Value = strInsertSQL }); ////存储过程中的参入参数 strInsertSQL 64 cmd.Parameters.Add(new OracleParameter("seqName", OracleDbType.Varchar2) { Value = seqName }); // //存储过程中的传入参数 seqName 65 cmd.Parameters.Add(new OracleParameter("ID", OracleDbType.Int32) { Direction = ParameterDirection.Output }); //存储过程中的传出参数ID，只需要声明 66 //cmd.Parameters.AddRange(parameters); 67 cmd.ExecuteNonQuery(); 68 string newId = cmd.Parameters["ID"].Value.ToString(); //获得传出参数的ID的值 69 return newId; 70 } 71 catch (Exception ex) 72 { 73 throw new Exception(ex.ToString()); 74 } 75 } 76 } 77 78 /// <summary> 79 /// 自己创建链接的 带存储过程的Insert非查询，直接写存储过程参数 80 /// </summary> 81 /// <param name="proName">存储过程名称</param> 82 /// <param name="strInsertSQL">执行插入的sql语句，或者其他操作sql语句</param> 83 /// <param name="seqName">序列的名称</param> 84 /// <returns>当前序列号，即ID</returns> 85 public static object ExecuteNonQueryWithProduce(string proName, string strInsertSQL, string seqName) 86 { 87 using (OracleConnection conn = CreateConnection()) 88 { 89 return ExecuteNonQueryWithProduce(conn, proName, strInsertSQL, seqName); 90 } 91 } 92 93 /// <summary> 94 /// 使用已有链接的 带存储过程的Insert非查询，传存储过程参数 95 /// </summary> 96 /// <param name="conn">已有链接</param> 97 /// <param name="proName">存储过程名称</param> 98 /// <param name="parameters">存储过程中的传入、传出参数 数组</param> 99 /// <returns>当前序列号，即ID</returns> 100 public static object ExecuteNonQueryWithProduce(OracleConnection conn, string proName, params OracleParameter[] parameters) 101 { 102 using (OracleCommand cmd = new OracleCommand(proName, conn)) //命令中执行的不在是sql，而是存储过程 103 { 104 //try 105 //{ 106 cmd.CommandType = CommandType.StoredProcedure; //标记该命令的类型不是sql，而是存储过程 107 ////存储过程中有参数名称，以及设置对应参数的值 108 //cmd.Parameters.Add(new OracleParameter("strInsertSQL", OracleDbType.Varchar2) { Value = strInsertSQL }); ////存储过程中的参入参数 strInsertSQL 109 //cmd.Parameters.Add(new OracleParameter("seqName", OracleDbType.Varchar2) { Value = seqName }); // //存储过程中的传入参数 seqName 110 //cmd.Parameters.Add(new OracleParameter("ID", OracleDbType.Int32) { Direction = ParameterDirection.Output }); //存储过程中的传出参数ID，只需要声明 111 cmd.Parameters.AddRange(parameters); //参数中包括存储过程的传入传出参数，以及子sql语句中的参数 --------------****----------------- 112 int i = cmd.ExecuteNonQuery(); //直接返回执行插入之后，存储过程传出的变量值 113 object newId = cmd.Parameters["ID"].Value; //获得传出参数的ID的值 114 return newId; 115 //} 116 //catch (Exception ex) 117 //{ 118 // throw new Exception(ex.ToString()); 119 //} 120 } 121 } 122 123 /// <summary> 124 /// 自己创建链接的 带存储过程的Insert非查询，传存储过程参数 125 /// </summary> 126 /// <param name="proName">存储过程名称</param> 127 /// <param name="parameters">存储过程中的传入、传出参数 数组</param> 128 /// <returns>当前序列号，即ID</returns> 129 public static object ExecuteNonQueryWithProduce(string proName, params OracleParameter[] parameters) 130 { 131 using (OracleConnection conn = CreateConnection()) 132 { 133 return ExecuteNonQueryWithProduce(conn, proName, parameters); 134 } 135 } 136 137 138 /// <summary> 139 /// 使用已有链接的 单查询 140 /// </summary> 141 /// <param name="conn">链接</param> 142 /// <param name="sql">sql文本</param> 143 /// <param name="parameters">sql参数</param> 144 /// <returns>查询到的一条结果</returns> 145 public static object ExecuteScalar(OracleConnection conn, string sql, params OracleParameter[] parameters) 146 { 147 using (OracleCommand cmd = new OracleCommand(sql, conn)) 148 { 149 cmd.Parameters.AddRange(parameters); 150 return cmd.ExecuteScalar(); 151 } 152 } 153 154 /// <summary> 155 /// 自己创建链接的 单查询 156 /// </summary> 157 /// <param name="sql">sql文本</param> 158 /// <param name="parameters">sql参数</param> 159 /// <returns>查询到的一条结果</returns> 160 public static object ExecuteScalar(string sql, params OracleParameter[] parameters) 161 { 162 using (OracleConnection conn = CreateConnection()) 163 { 164 return ExecuteScalar(conn, sql, parameters); 165 } 166 } 167 168 /// <summary> 169 /// 使用已有链接的 reader查询 170 /// </summary> 171 /// <param name="conn">链接</param> 172 /// <param name="sql">sql文本</param> 173 /// <param name="parameters">sql参数</param> 174 /// <returns>查询到的结果集table</returns> 175 public static DataTable ExecuteReader(OracleConnection conn, string sql, params OracleParameter[] parameters) 176 { 177 DataTable table = new DataTable(); 178 using (OracleCommand cmd = new OracleCommand(sql, conn)) 179 { 180 cmd.Parameters.AddRange(parameters); 181 using (OracleDataReader reader = cmd.ExecuteReader()) 182 { 183 table.Load(reader); 184 } 185 } 186 return table; 187 } 188 189 /// <summary> 190 /// 自己创建链接的 reader查询 191 /// </summary> 192 /// <param name="sql">sql文本</param> 193 /// <param name="parameters">sql参数</param> 194 /// <returns>查询到的结果集table</returns> 195 public static DataTable ExecuteReader(string sql, params OracleParameter[] parameters) 196 { 197 using (OracleConnection conn = CreateConnection()) 198 { 199 return ExecuteReader(conn, sql, parameters); 200 } 201 } 202 203 /// <summary> 204 /// 使用已有链接的 stream查询 205 /// </summary> 206 /// <param name="conn">链接</param> 207 /// <param name="sql">sql文本</param> 208 /// <param name="parameters">sql参数</param> 209 /// <returns>查询到的结果流stream</returns> 210 public static System.IO.Stream ExecuteStream(OracleConnection conn, string sql, params OracleParameter[] parameters) 211 { 212 using (OracleCommand cmd = new OracleCommand(sql, conn)) 213 { 214 cmd.Parameters.AddRange(parameters); 215 using (System.IO.Stream stream = cmd.ExecuteStream()) 216 { 217 return stream; 218 } 219 } 220 } 221 222 /// <summary> 223 /// 自己创建链接的stream查询 224 /// </summary> 225 /// <param name="sql">sql文本</param> 226 /// <param name="parameters">sql参数</param> 227 /// <returns>查询到的结果流stream</returns> 228 public static System.IO.Stream ExecuteStream(string sql, params OracleParameter[] parameters) 229 { 230 using (OracleConnection conn = CreateConnection()) 231 { 232 return ExecuteStream(conn, sql, parameters); 233 } 234 } 235 }
DbHelper.cs
1 public class DbHelper 2 { 3 private static readonly string connStr = ConfigurationManager.ConnectionStrings["dbconnStr"].ConnectionString; 4 5 //创建连接 6 public static IDbConnection CreateConnection() 7 { 8 IDbConnection conn = new OracleConnection(connStr); 9 conn.Open(); 10 return conn; 11 } 12 13 //把字典中的参数都加入到cmd得参数集合中 14 public static void DictAddToCmdParameters(IDbCommand cmd,Dictionary<string,object> dict) 15 { 16 foreach(KeyValuePair<string,object> kvp in dict) 17 { 18 IDataParameter parameters = cmd.CreateParameter(); 19 parameters.ParameterName = kvp.Key; 20 parameters.Value = kvp.Value; 21 cmd.Parameters.Add(parameters); 22 } 23 } 24 25 //使用已有连接的 非查询 26 public static int ExecuteNonQuery(IDbConnection conn,string sql,Dictionary<string,object> dict) 27 { 28 using(IDbCommand cmd=conn.CreateCommand()) 29 { 30 cmd.CommandText = sql; 31 DictAddToCmdParameters(cmd, dict); 32 return cmd.ExecuteNonQuery(); 33 } 34 } 35 36 //使用已有连接的 非查询 带事务处理 37 public static int ExecuteNonQuery(IDbConnection conn,IDbTransaction tx,string sql,Dictionary<string,object> dict) 38 { 39 using(IDbCommand cmd=conn.CreateCommand()) 40 { 41 cmd.Transaction = tx; 42 cmd.CommandText = sql; 43 DictAddToCmdParameters(cmd, dict); 44 return cmd.ExecuteNonQuery(); 45 } 46 } 47 48 //自己创建连接的 非查询 49 public static int ExecuteNonQuery(string sql,Dictionary<string,object> dict) 50 { 51 using(IDbConnection conn=CreateConnection()) 52 { 53 return ExecuteNonQuery(sql,dict); 54 } 55 } 56 57 //使用已有连接的 单查询 58 public static object ExecuteScalar(IDbConnection conn,string sql,Dictionary<string,object> dict) 59 { 60 using(IDbCommand cmd=conn.CreateCommand()) 61 { 62 cmd.CommandText = sql; 63 DictAddToCmdParameters(cmd, dict); 64 return cmd.ExecuteScalar(); 65 } 66 } 67 68 //自己创建连接的 单查询 69 public static object ExecuteScalar(string sql,Dictionary<string,object> dict) 70 { 71 using(IDbConnection conn=CreateConnection()) 72 { 73 return ExecuteScalar(conn, sql, dict); 74 } 75 } 76 77 //使用已有连接的 Reader查询 78 public static DataTable ExecuteReader(IDbConnection conn,string sql,Dictionary<string,object> dict) 79 { 80 DataTable table = new DataTable(); 81 using(IDbCommand cmd=conn.CreateCommand()) 82 { 83 cmd.CommandText = sql; 84 DictAddToCmdParameters(cmd, dict); 85 using (IDataReader reader = cmd.ExecuteReader()) 86 { 87 table.Load(reader); 88 } 89 } 90 return table; 91 } 92 93 //自己创建连接的 Reader查询 94 public static DataTable ExecuteReader(string sql,Dictionary<string,object> dict) 95 { 96 using(IDbConnection conn=CreateConnection()) 97 { 98 return ExecuteReader(conn, sql, dict); 99 } 100 } 101 102 //使用已有连接的 DataAdapter查询 103 public static DataTable ExecuteDataAdapter(IDbConnection conn,string sql,Dictionary<string,object> dict) 104 { 105 using(IDbCommand cmd=conn.CreateCommand()) 106 { 107 cmd.CommandText = sql; 108 DictAddToCmdParameters(cmd, dict); 109 IDataAdapter adapter = GetDataAdapter(cmd);//获得查询的数据库结果 110 DataSet dataset = new DataSet(); 111 adapter.Fill(dataset); 112 return dataset.Tables[0]; 113 } 114 } 115 116 //自己创建连接的 DataAdapter查询 117 public static DataTable ExecuteDataAdapter(string sql,Dictionary<string,object> dict) 118 { 119 using(IDbConnection conn=CreateConnection()) 120 { 121 return ExecuteDataAdapter(conn, sql, dict); 122 } 123 } 124 125 //获得查询到的数据库结果 126 private static IDataAdapter GetDataAdapter(IDbCommand cmd) 127 { 128 IDataAdapter adapter;//DataAdapter的访问限制为protected,不能new 129 if (cmd is OracleCommand)//需引用Oracle.DataAccess.Client 130 { 131 OracleCommand oracmd = cmd as OracleCommand; 132 adapter = new OracleDataAdapter(oracmd); 133 } 134 //else if (cmd is MySqlCommand)//需引用mysql.data.client 135 //{ 136 // MySqlCommand mycom = cmd as MySqlCommand; 137 // adapter = new MySqlDataAdapter(mycom); 138 //} 139 else if (cmd is SqlCommand)//需引用System.Data.SqlClient; 140 { 141 SqlCommand sqlcom = cmd as SqlCommand; 142 adapter = new SqlDataAdapter(sqlcom); 143 } 144 else 145 { 146 throw new Exception("需要其他数据库的DataAdapter"); 147 } 148 return adapter; 149 } 150 151 /// <summary> 152 /// 执行SqlBulkCopy或OracleBulkCopy批量拷贝，传入一个实体的公共类型，约定其属性名与DB中列名相同 153 /// </summary> 154 /// <param name="type"></param> 155 /// <returns>返回该类型对应的表的架构</returns> 156 public static DataTable ExecuteBulkCopy(Type type) 157 { 158 //创建表的架构 159 DataTable table = new DataTable(); 160 DataColumn column = new DataColumn(); 161 //Type type = typeof(Person);//传入实体类Person 162 PropertyInfo[] properties = type.GetProperties(); 163 //创建表的列 164 foreach (PropertyInfo property in properties) 165 { 166 column.ColumnName = property.Name; 167 column.DataType = property.PropertyType; 168 table.Columns.Add(column); 169 } 170 return table; 171 } 172 173 /// <summary> 174 /// 遍历： 把每个数据中数据依次填充到该表的row中 175 /// </summary> 176 /// <param name="table"></param> 177 /// <param name="type"></param> 178 /// <param name="line"></param> 179 /// <returns>返回填充了一行数据的表</returns> 180 public static DataTable RowAddTable(DataTable table, Type type, string[] line)//line中个数必须与列数量相同 181 { 182 //填入表的行 183 DataRow row = table.NewRow(); 184 PropertyInfo[] properties = type.GetProperties(); 185 int i = 0; 186 foreach (PropertyInfo property in properties)//属性名对应列名 187 { 188 row[property.Name] = line[i]; 189 i++; 190 } 191 table.Rows.Add(row); 192 return table; 193 } 194 195 /// <summary> 196 /// 把当前表写入目标表（目标表和当前表列名相同） 197 /// </summary> 198 /// <param name="destTableName"></param> 199 /// <param name="currentTabele"></param> 200 public static bool BulkCopyMapping(string destTableName, DataTable currentTabele) 201 { 202 bool falg = false; 203 //using (SqlBulkCopy bulkCopy = new SqlBulkCopy(connStr)) 204 using (OracleBulkCopy bulkCopy = new OracleBulkCopy(connStr))//oracle在这里不能用事务,sql server可以 205 { 206 207 bulkCopy.DestinationTableName = destTableName; 208 foreach (DataColumn column in currentTabele.Columns) 209 { 210 bulkCopy.ColumnMappings.Add(column.ColumnName, column.ColumnName);//目标表和当前表列名相同 211 } 212 try 213 { 214 bulkCopy.WriteToServer(currentTabele);//把当前表写入目标表 215 falg = true; 216 } 217 catch (Exception ex) 218 { 219 falg = false; 220 throw new Exception(ex.ToString()); 221 } 222 } 223 return falg; 224 } 225 } 226 class Person//BulkCopy直接传了Type所以没用到 227 { 228 public int Id{get;set;} 229 public string Name{get;set;} 230 }
