Code:
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();
}
}
}
}
I'm trying to teach myself C# and how to use it to interact with a database. This program opens a connection to SQL Server, retrieves records from a source table, validates the data a bit, and then writes two records to the target table per source record. Initially all of this code was linear and in the Main method but I wanted to be able to re-use the Open and Close instructions, and use the Open instructions for other servers/databases.
When I created the OpenSQLConnection and CloseSQLConnection methods I had a lot of trouble as instances were something I was unfamiliar with. I got errors like
Cannot implicitly convert type string to System.Data.SqlClient.SqlConnection" and reading various threads pointed me to the using function (Cannot implicitly..., Error while converting string...).
Once I implemented the using function, the errors and warnings went away, but when I run the code the connection isn't working, and I don't think it's actually opening at all. Here is a sample portion of the output (It's pretty long).
at System.Data.ProviderBase.DbConnectionPool.TryGetConnection(DbConnection owningObject, TaskCompletionSource
1 retry, DbConnectionOptions userOptions, DbConnectionInternal& connection)1 retry, DbConnectionOptions userOptions, DbConnectionInternal oldConnection, DbConnectionInternal& connection)
at System.Data.ProviderBase.DbConnectionFactory.TryGetConnection(DbConnection owningConnection, TaskCompletionSource
at System.Data.ProviderBase.DbConnectionInternal.TryOpenConnectionInternal(DbConnection outerConnection, DbConnectionFactory connectionFactory, TaskCompletionSource1 retry, DbConnectionOptions userOptions)1 retry, DbConnectionOptions userOptions)
at System.Data.ProviderBase.DbConnectionClosed.TryOpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory, TaskCompletionSource
at System.Data.SqlClient.SqlConnection.TryOpenInner(TaskCompletionSource1 retry)1 retry)
at System.Data.SqlClient.SqlConnection.TryOpen(TaskCompletionSource
at System.Data.SqlClient.SqlConnection.Open()
at dbsql.MainClass.OpenSQLConnection(String sqlServer, String sqlDatabase) in c:\Users\tromik\Documents\Projects\dbsql\Program.cs:line 153
ClientConnectionId:8eb63155-4ec6-4795-9f68-98b01f650ffc
Error Number:4060,State:1,Class:11
SQL Stuff:
-- 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);
If more output would help I'm happy to oblige. Thanks in advance!
You have the connection object creation in a using block. The using block will close the connection when you exit its scope because the SqlConnection implements IDisposable.