代码：
using System;
using System.Data.SqlClient;
namespace dbsql
{
public class MainClass
{
public SqlConnection mssql = new SqlConnection();
public static void Main(string[] args)
{
// Variables
string nameVal = "";
string dateVal = "";
string catVal = "";
double amntVal = 0;
string sqlRec = "";
int idVal = 0;
int cnt = 1;
// Configure and open SQL connection
MainClass mc = new MainClass();
mc.OpenSQLConnection("localhost", "trainning");
//MainClass.OpenSQLConnection("localhost", "trainning");
/*
* Truncate the target table(s)
*/
using (SqlCommand sqlDelete = new SqlCommand("DELETE FROM expenses_stage", mc.mssql))
{
sqlDelete.ExecuteNonQuery();
}
try
{
/*
* Retrieve records from source
*/
SqlDataReader sqlReader = null;
SqlCommand selectExp = new SqlCommand("SELECT * FROM expenses_source ORDER BY ID", mc.mssql);
sqlReader = selectExp.ExecuteReader();
while (sqlReader.Read())
{
/*
* Read source data into vars, trim for clean strings
*/
idVal = Convert.ToInt32(sqlReader["ID"]);
nameVal = sqlReader["Name"].ToString().Trim();
dateVal = sqlReader["Date"].ToString().Trim();
catVal = sqlReader["Category"].ToString().Trim();
amntVal = Convert.ToInt32(sqlReader["Amount"]);
sqlRec = "==> " + idVal + " | " + nameVal + " | " + dateVal + " | " + catVal + " | " + amntVal;
/*
* Check for blank, NULL, 0 values
*/
if (idVal == 0)
{
Console.Write("ID is NULL : ");
Console.WriteLine(sqlRec);
}
else if (nameVal == "")
{
Console.Write("Name is NULL : ");
Console.WriteLine(sqlRec);
}
else if (dateVal == "")
{
Console.Write("Date is NULL : ");
Console.WriteLine(sqlRec);
}
else if (catVal == "")
{
Console.Write("Category is NULL : ");
Console.WriteLine(sqlRec);
}
else if (amntVal == 0)
{
Console.Write("Amount is NULL : ");
Console.WriteLine(sqlRec);
}
else
/*
* If food cost is over $200, reject
*/
if (catVal == "Food" && amntVal > 200)
{
Console.Write("Food expense is too high : ");
Console.WriteLine(sqlRec);
}
else
{
Console.Write("Good record : ");
Console.WriteLine(sqlRec);
Console.WriteLine(" ====> AER FIRE--Inserting record!");
/*
* For every source record, insert two records to the target
*/
for (cnt = 1; cnt <= 2; cnt++)
{
if (cnt == 1)
{
SqlCommand insertExp = new SqlCommand("INSERT INTO expenses_stage (ID, Name, Date, Category, Amount) " +
"VALUES (" + idVal + ", '" + nameVal + "', '" + dateVal + "', '" + catVal + "', " + amntVal + ")", mc.mssql);
insertExp.ExecuteNonQuery();
}
else
{
SqlCommand insertExp = new SqlCommand("INSERT INTO expenses_stage (ID, Name, Date, Category, Amount) " +
"VALUES (" + idVal + ", '" + nameVal + "', '" + dateVal + "', 'AP', " + amntVal + ")", mc.mssql);
insertExp.ExecuteNonQuery();
}
}
}
}
}
catch (Exception e)
{
Console.WriteLine(e.ToString());
Console.ReadKey();
}
mc.CloseSQLConnection();
Console.ReadKey();
} //Close main
public void OpenSQLConnection(string sqlServer, string sqlDatabase)
{
string connString = "server=" + sqlServer + ";" +
"Trusted_Connection=yes;" +
"database=" + sqlDatabase + "; " +
"connection timeout=30;" +
"MultipleActiveResultSets=true";
using (var mssql = new SqlConnection(connString))
{
// Open SQL connection
try
{
mssql.Open();
}
catch (Exception e)
{
Console.WriteLine(e.ToString());
}
}
}
public void CloseSQLConnection()
{
MainClass mc = new MainClass();
try
{
/*
* Close SQL connection
*/
mc.mssql.Close();
}
catch (Exception e)
{
Console.WriteLine(e.ToString());
Console.ReadKey();
}
}
}
}
我正在尝试自学C＃，以及如何使用它与数据库进行交互。该程序打开与SQL Server的连接，从源表中检索记录，对数据进行一点验证，然后为每个源记录将两个记录写入目标表。最初，所有这些代码都是线性的，并且使用Main方法，但是我希望能够重用Open和Close指令，并将Open指令用于其他服务器/数据库。
当创建OpenSQLConnectionandCloseSQLConnection方法时，我遇到了很多麻烦，因为实例是我不熟悉的。我有类似的错误
无法将类型字符串隐式转换为System.Data.SqlClient.SqlConnection”，阅读各种线程将我指向使用函数（无法隐式...，转换字符串时出错...）。
一旦实现了using函数，错误和警告就消失了，但是当我运行代码时，连接不起作用，并且我认为它实际上并没有打开。这是输出的样本部分（很长）。
在System.Data.ProviderBase.DbConnectionPool.TryGetConnection（的DbConnection owningObject，TaskCompletionSource 1重试，DbConnectionOptions USEROPTIONS，DbConnectionInternal oldConnection，DbConnectionInternal＆连接）在System.Data.ProviderBase.DbConnectionInternal.TryOpenConnectionInternal（的DbConnection outerConnection，DbConnectionFactory connectionFactory的，TaskCompletionSource 1重试，DbConnectionOptions USEROPTIONS ）在System.Data.SqlClient.SqlConnection.TryOpenInner（TaskCompletionSource 1重试）在c：\ Users \ tromik \ Documents中的dbsql.MainClass.OpenSQLConnection（String sqlServer，String sqlDatabase）在System.Data.SqlClient.SqlConnection.Open（）\ Projects \ dbsql \ Program.cs：第153行
1 retry, DbConnectionOptions userOptions, DbConnectionInternal& connection)
at System.Data.ProviderBase.DbConnectionFactory.TryGetConnection(DbConnection owningConnection, TaskCompletionSource1 retry, DbConnectionOptions userOptions)
at System.Data.ProviderBase.DbConnectionClosed.TryOpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory, TaskCompletionSource1 retry)
at System.Data.SqlClient.SqlConnection.TryOpen(TaskCompletionSource
ClientConnectionId：8eb63155-4ec6-4795-9f68-98b01f650ffc
错误编号：4060，状态：1，类别：11
SQL资料：
-- Create source table
CREATE TABLE [dbo].[expenses_source]
(
[ID] [int] NULL,
[Name] [nvarchar](255) NULL,
[Date] [datetime] NULL,
[Category] [nvarchar](255) NULL,
[Amount] [float] NULL
) ON [PRIMARY]
-- Create target table
CREATE TABLE [dbo].[expenses_stage]
(
[ID] [int] NULL,
[Name] [nvarchar](255) NULL,
[Date] [datetime] NULL,
[Category] [nvarchar](255) NULL,
[Amount] [float] NULL
) ON [PRIMARY]
-- Insert source data
INSERT [dbo].[expenses_source] ([ID], [Name], [Date], [Category], [Amount])
VALUES (1, 'Maryland Duford ', CAST(0x0000A33D00000000 AS DateTime), 'Gas', 70);
INSERT [dbo].[expenses_source] ([ID], [Name], [Date], [Category], [Amount])
VALUES (2, 'Suzanne Sechrist ', CAST(0x0000A35F00000000 AS DateTime), 'Food', 30);
INSERT [dbo].[expenses_source] ([ID], [Name], [Date], [Category], [Amount])
VALUES (3, 'Reggie Nally ', CAST(0x0000A35200000000 AS DateTime), 'Food', 22);
INSERT [dbo].[expenses_source] ([ID], [Name], [Date], [Category], [Amount])
VALUES (6, 'Stefani Rochell ', CAST(0x0000A2A600000000 AS DateTime), 'Hotel', 110);
INSERT [dbo].[expenses_source] ([ID], [Name], [Date], [Category], [Amount])
VALUES (10, 'Chong Hecht', CAST(0x0000A3AE00000000 AS DateTime), 'Office', 100);
INSERT [dbo].[expenses_source] ([ID], [Name], [Date], [Category], [Amount])
VALUES (8, 'Tiffaney Tooker ', CAST(0x0000A40C00000000 AS DateTime), 'Food', 500);
INSERT [dbo].[expenses_source] ([ID], [Name], [Date], [Category], [Amount])
VALUES (4, 'Joseph Takemoto ', NULL, 'Hotel', 100);
INSERT [dbo].[expenses_source] ([ID], [Name], [Date], [Category], [Amount])
VALUES (5, 'Bryon Bettis ', NULL, 'Food', 17);
INSERT [dbo].[expenses_source] ([ID], [Name], [Date], [Category], [Amount])
VALUES (9, 'Elfreda Byun ', NULL, 'Gas', 50);
INSERT [dbo].[expenses_source] ([ID], [Name], [Date], [Category], [Amount])
VALUES (11, 'Rosio Boggs ', CAST(0x0000A2F100000000 AS DateTime), NULL, 20);
INSERT [dbo].[expenses_source] ([ID], [Name], [Date], [Category], [Amount])
VALUES (7, 'Gregg Smithey ', CAST(0x0000A29500000000 AS DateTime), 'Food', 8);
如果更多的输出将帮助我很乐意。提前致谢！
您可以在using块中创建连接对象。当您退出连接范围时，using块将关闭连接，因为SqlConnection实现IDisposable。
本文收集自互联网，转载请注明来源。
如有侵权，请联系 [email protected] 删除。
我来说两句