C#使用ADO.Net连接数据库与DbProviderFactory实现多数据库访问
作者:springsnow 发布时间:2024-01-24 08:56:29
一、ADO.Net数据库连接字符串
1、OdbcConnection(System.Data.Odbc)
(1)SQL Sever
标准安全:" Driver={SQL Server}; Server=Aron1; Database=pubs; Uid=sa; Pwd=asdasd; "
信任的连接:" Driver={SQL Server}; Server=Aron1; Database=pubs; Trusted_Connection=yes; "
(2)SQL Native Client ODBC Driver(>=SQL Server 2005)
标准安全" Driver={SQL Native Client}; Server=Aron1; Database=pubs; UID=sa; PWD=asdasd; "
信任的连接" Driver={SQL Native Client}; Server=Aron1; Database=pubs; Trusted_Connection=yes; "
--Integrated Security=SSPI 等同于Trusted_Connection=yes
(3)Oracle:
新版本:"Driver={Microsoft ODBC for Oracle}; Server=OracleServer.world; Uid=Username; Pwd=asdasd; "
旧版本:"Driver={Microsoft ODBC Driver for Oracle}; ConnectString=OracleServer.world; Uid=myUsername; Pwd=myPassword; "
(4)Access:
标准安全:"Driver={Microsoft Access Driver (*.mdb)}; Dbq=C:\mydatabase.mdb; Uid=Admin; Pwd=; "
2、OleDbConnection(System.Data.OleDb)
(1)SQL Sever
标准安全:" Provider=sqloledb; Data Source=Aron1; Initial Catalog=pubs; User Id=sa; Password=asdasd; "
信任的连接:" Provider=sqloledb; Data Source=Aron1; Initial Catalog=pubs; Integrated Security=SSPI; "
(use serverName\instanceName as Data Source to use an specifik SQLServer instance, only SQLServer2000)
(2)SQL Native Client OLE DB Provider(>=SQL Server 2005)
标准安全:" Provider=SQLNCLI; Server=Aron1; Database=pubs; UID=sa; PWD=asdasd; "
信任的连接:" Provider=SQLNCLI; Server=Aron1; Database=pubs; Trusted_Connection=yes; "
--Integrated Security=SSPI 等同于Trusted_Connection=yes
(3)Oracle:
标准安全:"Provider=msdaora; Data Source=MyOracleDB; User Id=UserName; Password=asdasd; "
This one's from Microsoft, the following are from Oracle
标准安全:"Provider=OraOLEDB.Oracle; Data Source=MyOracleDB; User Id=Username; Password=asdasd; "
信任的连接:"Provider=OraOLEDB.Oracle; Data Source=MyOracleDB; OSAuthent=1; "
(4)Access:
标准安全:
"Provider=Microsoft.Jet.OLEDB.4.0; Data Source=\somepath\mydb.mdb; User Id=admin; Password=; "
3、SqlConnection(Syste.Data.SqlClient) SQL专用
标准安全:
" Data Source=Aron1; Initial Catalog=pubs; User Id=sa; Password=asdasd; "
- 或者 -" Server=Aron1; Database=pubs; User ID=sa; Password=asdasd; Trusted_Connection=False"
信任的连接:" Data Source=Aron1; Initial Catalog=pubs; Integrated Security=SSPI; "
- 或者 -" Server=Aron1; Database=pubs; Trusted_Connection=True; "
–(use serverName\instanceName as Data Source to use an specifik SQLServer instance, 仅仅适用于SQLServer2000)
4、OracleConnection(System.Data.OracleClient\Oracle.ManagedDataAccess.Client) Oracle专用
标准安全:"Data Source=MyOracleDB; Integrated Security=yes; "
--This one works only with Oracle 8i release 3 or later
指定用户名和密码:"Data Source=MyOracleDB; User Id=username; Password=passwd; Integrated Security=no; "
--This one works only with Oracle 8i release 3 or later
指定主机:"Data Source=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP) (HOST=192.168.115.33) (PORT=1521)))(CONNECT_DATA=(SERVICE_NAME= testDemo))); User Id=oracle_test; Password=oracle"
其中Oracle数据库服务器IP:192.168.115.33
ServiceName:testDemo
用户名:oracle_test
密码:oracle
二、利用DbProviderFactory创建各种ADO.Net对象
DbProviderFactory是一个工厂类,工厂类的作用提供其他一系列相互之间有关系的类。在这里,DbProviderFactory就自动生成了包括DbConnection、DbCommand、 DbDataAdapter等一系列数据库操作的相关类。
1、配置文件ConnectionString节:
<configuration>
<connectionStrings>
<add name="default" connectionString="server=localhost; user id=sa; password=******; database=northwind"
providerName="System.Data.SqlClient"/>
</connectionStrings>
</configuration>
2、利用DbProviderFactory类自动查找数据库的驱动
ConnectionStringSettings settings = ConfigurationManager.ConnectionStrings["default"];
DbProviderFactory provider = DbProviderFactories.GetFactory(settings.ProviderName);
3、利用DbProviderFactory类实例创建各种ADO.Net对象。
using (DbConnection conn = provider.CreateConnection())
{
conn.ConnectionString = settings.ConnectionString;
conn.Open();
DbCommand cmd = conn.CreateCommand();
cmd.CommandText = "Select top 10 * From ShortTermBill";
//使用DbDataAdapter
DbDataAdapter da = provider.CreateDataAdapter();
da.SelectCommand = cmd;
DataSet ds = new DataSet();
da.Fill(ds);
da.Dispose();
Console.WriteLine(ds.Tables[0].Rows[0]["BillCode"]);
//使用DbDataReader
DbDataReader reader = cmd.ExecuteReader()
while (reader.Read())
{
Console.WriteLine(reader.GetString(0));
}
conn.Close();
}
三、利用DbConnection获取数据库架构信息
SQL Server 架构集合 - ADO.NET | Microsoft 官方文档
class Program
{
static void Main()
{
string connectionString = GetConnectionString();
using (SqlConnection connection = new SqlConnection(connectionString))
{
// Connect to the database then retrieve the schema information.
connection.Open();string[] columnRestrictions = new String[4];
// For the array, 0-member represents Catalog; 1-member represents Schema;
// 2-member represents Table Name; 3-member represents Column Name.
// Now we specify the Table_Name and Column_Name of the columns what we want to get schema information.
columnRestrictions[2] = "Device";
DataTable departmentIDSchemaTable = connection.GetSchema("Columns", columnRestrictions);
ShowColumns(departmentIDSchemaTable);
}
}
private static string GetConnectionString()
{
// To avoid storing the connection string in your code,
// you can retrieve it from a configuration file.
return "server=10.126.64.1;Database=TPM;user=it;pwd=;ApplicationIntent=ReadOnly;MultiSubnetFailover=True";
}
private static void ShowColumns(DataTable columnsTable)
{
var selectedRows = from info in columnsTable.AsEnumerable()
select new
{
TableCatalog = info["TABLE_CATALOG"],
TableSchema = info["TABLE_SCHEMA"],
TableName = info["TABLE_NAME"],
ColumnName = info["COLUMN_NAME"],
DataType = info["DATA_TYPE"],
ORDINAL_POSITION = info["ORDINAL_POSITION"],
COLUMN_DEFAULT = info["COLUMN_DEFAULT"],
IS_NULLABLE = info["IS_NULLABLE"],
CHARACTER_MAXIMUM_LENGTH = info["CHARACTER_MAXIMUM_LENGTH"],
NUMERIC_PRECISION = info["NUMERIC_PRECISION"],
NUMERIC_SCALE = info["NUMERIC_SCALE"],
DATETIME_PRECISION = info["DATETIME_PRECISION"],
};
Console.WriteLine("{0,-15},{1,-15},{2,-15},{3,-15},{4,-15},{5,-15},{6,-15},{7,-15},{8,-15},{9,-15},{10,-15},{11,-15}", "TableCatalog", "TABLE_SCHEMA",
"表名", "列名", "数据类型", "字段原始顺序", "列默认值", "是否可空", "字符串最大长度", "数字精度", "数字小数点位数", "日期精度"
);
foreach (var row in selectedRows)
{
Console.WriteLine("{0,-15},{1,-15},{2,-15},{3,-15},{4,-15},{5,-15},{6,-15},{7,-15},{8,-15},{9,-15},{10,-15},{11,-15}", row.TableCatalog,
row.TableSchema, row.TableName, row.ColumnName, row.DataType, row.ORDINAL_POSITION, row.COLUMN_DEFAULT, row.IS_NULLABLE
, row.CHARACTER_MAXIMUM_LENGTH, row.NUMERIC_PRECISION, row.NUMERIC_SCALE, row.DATETIME_PRECISION);
}
}
}
来源:https://www.cnblogs.com/springsnow/p/9433920.html
猜你喜欢
- 如下所示:device = torch.device("cuda:0" if torch.cuda.is_availab
- 一、python机器学习–线性回归线性回归是最简单的机器学习模型,其形式简单,易于实现,同时也是很多机器学习模型的基础。对于一个给定的训练集
- Geohash 是一种地址编码,它能把二维的经纬度编码成一维的字符串。比如,北海公园的编码是wx4g0ec1。Geohash 的原理、算法下
- (1)查看日期函数拓展phpinfo()<?phpphpinfo();打开上述页面之后,可以看到以下,证明已经安装日期拓展 浏览器输入
- type()函数:使用type()函数可以判断对象的类型,如果一个变量指向了函数或类,也可以用type判断。如:class Student(
- 本文以实例形式实现了python监控linux性能以及进程消耗性能的方法,具体实现代码如下:# -*- coding: utf-8 -*-&
- 字典(dict)对象是 Python 最常用的数据结构,社区曾有人开玩笑地说:"Python企图用字典装载整个世界",字
- 在源环境中启动CLion需要告知CLion ROS特定的环境变量。通过运行来源工作空间时,将在当前shell中检索这些变量source ./
- 一、前言构建命令行程序很酷:命令行可以按照我们的设定完成相应的工作,相比 GUI 界面程序,无需花费大量时间设计 GUI 界面。但要使命令行
- 本文实例讲述了Python列表推导式与生成器用法。分享给大家供大家参考,具体如下:1. 先看两个列表推导式def t1(): f
- UUID (Universally Unique Identifier,通用唯一标识)是一个128位的用于计算机系统中以识别信息的数目,虽然
- “占位图形”顾名思义是在准备好将最终图形添加到 Web 页之前使用的临时图形。使用它可以在没有理想的图形的情况下先行制作Web页面——在需要
- 设计中文网站的朋友都会有这样的体会,Dreamweaver功能虽然强大,但要按照中文的行文习惯实现每个
- 1. Python字典的clear()方法(删除字典内所有元素)#!/usr/bin/python# -*- coding: UTF-8 -
- 两组字符串数据,需要比较其中相同的数据,并将其值相加并组成一个新的字符串数据a1="sp2=20;sp1=34;"a2=
- Pygame的mixer 模块可以依据命令播放一个或多个声音,并且也可以将这些声音混合在一起。而获得声音需要四个步骤:一、启动mixer进程
- 朋友去面试。对方问他:说说你之前做的那个站,有什么地方好的?朋友就说:用户体验比别的站好。对方又问:你怎么知道用户体验比别的好?朋友于是又磕
- 如今大部分网站仍然采用表格嵌套内容的方式来制作网站,虽然此方法对于我们来说比较熟悉、比较上手;但是,它却阻碍了一种更好的、更有亲和力的、更灵
- 本文实例讲述了python 协程 gevent原理与用法。分享给大家供大家参考,具体如下:geventgreenlet已经实现了协程,但是这
- 主要内容所谓RPC,是远程过程调用(Remote Procedure Call)的简写,网上解释很多,简单来说,就是在当前进程调用其他进程的