ado.net操作

浏览数：25 / 时间：2015年06月09日

1、sqlserver数据简单操作

　　sqlserver数据库连接字符串格式：server=服务器地址; pooling=true;max pool size=100; min pool size=5;packet size=4096;database=数据库名称;uid=用户名;pwd=密码;connection timeout=150;

/// <summary>
        /// 根据查询语句从数据库中获取，指定数据
        /// </summary>
        /// <param name="sql">查询sql</param>
        /// <param name="connStr">连接数据库字符串</param>
        /// <returns>结果数据</returns>
        public DataTable GetDataTable(string sql, string connStr)
        {
            SqlConnection conn = new SqlConnection(connStr);
            SqlCommand com = null;
            SqlDataAdapter adapter = null;
            try
            {
                conn.Open();
                com = conn.CreateCommand();
                com.CommandText = sql;
                adapter = new SqlDataAdapter(com);
                DataTable dataTab = new DataTable();
                adapter.Fill(dataTab);
                return dataTab;
            }
            catch (System.Exception ex)
            {
                LogManage.CreateObject().ExportLog(LogType.Exception, m_UserName,
                    string.Format("执行sql语句：{0} 失败！[异常信息：{1}]", sql, ex.Message), "SqlServerDataProvider.GetDataTable");
                return null;
            }
            finally
            {
                if (com != null)
                {
                    com.Dispose();
                }
                if (adapter != null)
                {
                    adapter.Dispose();
                }
                conn.Close();
            }
        }

/// <summary>
        /// 将数据插入指定数据表中
        /// </summary>
        /// <param name="dataTab">数据</param>
        /// <param name="tableName">表名</param>
        /// <param name="connStr">连接字符串</param>
        /// <returns>执行成功返回true</returns>
        public bool InsertDataIntoDB(DataTable dataTab, string tableName, string connStr)
        {
            SqlConnection conn = new SqlConnection(connStr);
            SqlCommand com = null;
            SqlTransaction tran = null;
            try
            {
                conn.Open();
                tran = conn.BeginTransaction();
                com = conn.CreateCommand();
                com.Transaction = tran;
                SqlBulkCopy bulk = new SqlBulkCopy(conn, SqlBulkCopyOptions.TableLock, tran);
                bulk.DestinationTableName = tableName;
                bulk.BatchSize = 1000;
                bulk.WriteToServer(dataTab);
                tran.Commit();
                return true;
            }
            catch (System.Exception ex)
            {
                if (tran != null)
                {
                    tran.Rollback();
                }
                LogManage.CreateObject().ExportLog(LogType.Exception, m_UserName,
                    string.Format("向表{0}中插入数据失败！[异常信息：{1}]", tableName, ex.Message), "SqlServerDataProvider.InsertDataIntoDB");
                return false;
            }
            finally
            {
                if (com != null)
                {
                    com.Dispose();
                }
                conn.Close();
            }
        }