1. Database connection string
1、OdbcConnection（）
(1)SQL Sever
Standard safety:" 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 safety" 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=; Uid=Username; Pwd=asdasd; "
Old version:"Driver={Microsoft ODBC Driver for Oracle}; ConnectString=; Uid=myUsername; Pwd=myPassword; "
(4)Access:
Standard safety:"Driver={Microsoft Access Driver (*.mdb)}; Dbq=C:\; Uid=Admin; Pwd=; "
2、OleDbConnection()
(1)SQL Sever
Standard safety:" Provider=sqloledb; Data Source=Aron1; Initial Catalog=pubs; User Id=sa; Password=asdasd; "
Trusted connection:" 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 safety:" Provider=SQLNCLI; Server=Aron1; Database=pubs; UID=sa; PWD=asdasd; "
Trusted connection:" Provider=SQLNCLI; Server=Aron1; Database=pubs; Trusted_Connection=yes; "--Integrated Security=SSPI is equivalent to Trusted_Connection=yes
(3)Oracle:
Standard safety:"Provider=msdaora; Data Source=MyOracleDB; User Id=UserName; Password=asdasd; "
This one's from Microsoft, the following are from Oracle
Standard safety:"Provider=; Data Source=MyOracleDB; User Id=Username; Password=asdasd; "
Trusted connection:"Provider=; Data Source=MyOracleDB; OSAuthent=1; "
(4)Access:
Standard safety:
"Provider=.4.0; Data Source=\somepath\; User Id=admin; Password=; "
3. SqlConnection() SQL-specific
Standard safety:
" 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 connection:" 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 specik SQLServer instance, only for SQLServer2000)
4. OracleConnection(\) Oracle-specific
Standard safety:"Data Source=MyOracleDB; Integrated Security=yes; " --This one works only with Oracle 8i release 3 or later
Specify username 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 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"
Among them, Oracle database server IP: 192.168.115.33
ServiceName：testDemo
Username: oracle_test
Password: oracle
2. Create various objects using DbProviderFactory
DbProviderFactory is a factory class, and the function of the factory class provides a series of other related classes. Here, DbProviderFactory automatically generates related classes including DbConnection, DbCommand, DbDataAdapter and other database operations.
1. Configuration file ConnectionString section:
<configuration>
<connectionStrings>
<add name="default" connectionString="server=localhost; user id=sa; password=******; database=northwind"
providerName=""/>
</connectionStrings>
</configuration>2. Use the DbProviderFactory class to automatically find the driver of the database
ConnectionStringSettings settings = ["default"]; DbProviderFactory provider = ();
3. Create various objects using DbProviderFactory class instance.
using (DbConnection conn = ())
{
= ;
();
DbCommand cmd = ();
= "Select top 10 * From ShortTermBill";
//Use DbDataAdapter DbDataAdapter da = ();
= cmd;
DataSet ds = new DataSet();
(ds);
();
([0].Rows[0]["BillCode"]);
//Use DbDataReader DbDataReader reader = ()
while (())
{
((0));
}
();
}3. Use DbConnection to obtain database architecture information
SQL Server Schema Collection - | 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.
();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 = ("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 ()
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"],
};
("{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",
"Table name", "List name", "Data Type", "Original order of fields", "Column default values", "Is it empty?", "Maximum string length", "Digital Accuracy", "Number of decimal points", "Date accuracy"
);
foreach (var row in selectedRows)
{
("{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.ORDINAL_POSITION, row.COLUMN_DEFAULT, row.IS_NULLABLE
, row.CHARACTER_MAXIMUM_LENGTH, row.NUMERIC_PRECISION, row.NUMERIC_SCALE, row.DATETIME_PRECISION);
}
}
}This is what this article about C#’s use of connecting databases and DbProviderFactory to achieve multi-database access. I hope it will be helpful to everyone's learning and I hope everyone will support me more.