Shulou(Shulou.com)05/31 Report--

This article Xiaobian introduces in detail for you "C# how to use ADO.Net to connect to the database and achieve multi-database access", the content is detailed, the steps are clear, and the details are handled properly. I hope that this article "C# how to use ADO.Net to connect to the database and achieve multi-database access" can help you solve your doubts.

1. ADO.Net database connection string 1, OdbcConnection (System.Data.Odbc) (1) SQL Sever

Standard security: "Driver= {SQL Server}; Server=Aron1; Database=pubs; Uid=sa; Pwd=asdasd;"

Trusted connection: "Driver= {SQL Server}; Server=Aron1; Database=pubs; Trusted_Connection=yes;"

(2) SQL Native Client ODBC Driver (> = SQL Server 2005)

Standard security "Driver= {SQL Native Client}; Server=Aron1; Database=pubs; UID=sa; PWD=asdasd;"

Trusted connection

"Driver= {SQL Native Client}; Server=Aron1; Database=pubs; Trusted_Connection=yes;"-Integrated Security=SSPI is equivalent to Trusted_Connection=yes

(3) Oracle:

New version: "Driver= {Microsoft ODBC for Oracle}; Server=OracleServer.world; Uid=Username; Pwd=asdasd;"

Old version: "Driver= {Microsoft ODBC Driver for Oracle}; ConnectString=OracleServer.world; Uid=myUsername; Pwd=myPassword;"

(4) Access:

Standard security: "Driver= {Microsoft Access Driver (* .mdb)}; Dbq=C:\ mydatabase.mdb; Uid=Admin; Pwd=;"

2. OleDbConnection (System.Data.OleDb) (1) SQL Sever

Standard Security: Provider=sqloledb; Data Source=Aron1; Initial Catalog=pubs; User Id=sa; Password=asdasd;

Trusted connections:

"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)

Standard Security: Provider=SQLNCLI; Server=Aron1; Database=pubs; UID=sa; PWD=asdasd;

Trusted connections:

"Provider=SQLNCLI; Server=Aron1; Database=pubs; Trusted_Connection=yes;"-Integrated Security=SSPI is equivalent to Trusted_Connection=yes

(3) Oracle:

Standard Security:

"Provider=msdaora; Data Source=MyOracleDB; User Id=UserName; Password=asdasd;"

This one's from Microsoft, the following are from Oracle

Standard Security: Provider=OraOLEDB.Oracle; Data Source=MyOracleDB; User Id=Username; Password=asdasd;

Trusted connection: "Provider=OraOLEDB.Oracle; Data Source=MyOracleDB; OSAuthent=1;"

(4) Access:

Standard Security:

"Provider=Microsoft.Jet.OLEDB.4.0; Data Source=\ somepath\ mydb.mdb; User Id=admin; Password=;"

3. SqlConnection (Syste.Data.SqlClient) SQL only

Standard Security:

"Data Source=Aron1; Initial Catalog=pubs; User Id=sa; Password=asdasd;"

-or-"Server=Aron1; Database=pubs; User ID=sa; Password=asdasd; Trusted_Connection=False."

Trusted connections:

"Data Source=Aron1; Initial Catalog=pubs; Integrated Security=SSPI;"

-or-

"Server=Aron1; Database=pubs; Trusted_Connection=True;"-(use serverName\ instanceName as Data Source to use an specifik SQLServer instance, for SQLServer2000 only)

4. OracleConnection (System.Data.OracleClient\ Oracle.ManagedDataAccess.Client) Oracle only

Standard Security:

"Data Source=MyOracleDB; Integrated Security=yes;"-This one works only with Oracle 8i release 3 or later

Specify the user name and password:

"Data Source=MyOracleDB; User Id=username; Password=passwd; Integrated Security=no;"-This one works only with Oracle 8i release 3 or later

Specify the host:

"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"

Where Oracle database server IP:192.168.115.33

ServiceName:testDemo

User name: oracle_test

Password: oracle

Second, use DbProviderFactory to create all kinds of ADO.Net objects

DbProviderFactory is a factory class, and the role of the factory class provides a series of other related classes. Here, DbProviderFactory automatically generates related classes, including DbConnection, DbCommand, DbDataAdapter, and a series of database operations.

1. Configuration file ConnectionString section:

2. Use DbProviderFactory class to find the driver of database automatically.

ConnectionStringSettings settings = ConfigurationManager.ConnectionStrings ["default"]; DbProviderFactory provider = DbProviderFactories.GetFactory (settings.ProviderName)

3. Create all kinds of ADO.Net objects by using DbProviderFactory class instances.

Using (DbConnection conn = provider.CreateConnection ()) {conn.ConnectionString = settings.ConnectionString; conn.Open (); DbCommand cmd = conn.CreateCommand (); cmd.CommandText = "Select top 10 * From ShortTermBill"; / / use 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"]); / / use DbDataReader DbDataReader reader = cmd.ExecuteReader () while (reader.Read ()) {Console.WriteLine (reader.GetString (0));} conn.Close ();} III. Use DbConnection to obtain database schema information

SQL Server Schema Collection-ADO.NET | Microsoft official documentation

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 ("{0jinqiang 15}, {1jcmf15}, {2jcm15}, {4pyrmp15}, {5mithlich15}, {6mlle 15}, {7pyrr15}, {8pyrr15}, {9furo15}, {10jour 15}, {11je 15}", "TableCatalog", "TABLE_SCHEMA", "table name", "list name", "data type") "original order of fields", "column defaults", "whether empty", "maximum length of string", "numeric precision", "number of decimal places", "date precision") Foreach (var row in selectedRows) {Console.WriteLine ("{0memlyly15}, {1mairelle 15}, {2maewash15}, {3maewash15}, {4Mae liche15}, {5lemly15}, {6mlle 15}, {7je lyric 15}, {8je lyric 15}, {9je lyric 15}, {10jue 15}, {11jue 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) Read here, this article "how to use ADO.Net to connect to database and achieve multi-database access" has been introduced. If you want to master the knowledge points of this article, you still need to practice and use it before you can understand it. If you want to know more about related articles, welcome to follow the industry information channel.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.