Note
Access to this page requires authorization. You can try signing in or changing directories.
Access to this page requires authorization. You can try changing directories.
Question
Thursday, August 13, 2020 4:07 PM
Hi
I am interested in finding out how can I create a helper class in order to connect to database and use stored procedure to for some operation. I would need to use different stored procedures but connecting to the same database, same table in my application.
I dont think writing the whole connect to database code at different section in my application is the right approach. This is my code.
try
{
using (SqlConnection connection = new SqlConnection(MainWindow.connstring))
{
connection.Open();
using (SqlCommand cmd = new SqlCommand("CheckWorkerIdv2", connection))
{
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add(new SqlParameter("@workerid", txtPasswordbox.Password));
SqlDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
userexists = reader.GetString(0);
userid = reader.GetInt32(1);
}
//if (userexists != "") String.IsNullOrEmpty(userexists)
if (!String.IsNullOrEmpty(userexists))
{
txtPasswordbox.Background = new SolidColorBrush(Color.FromRgb(170, 255, 0));
Window parentWindow = Window.GetWindow(this);
StackPanel sp = (StackPanel)parentWindow.FindName("splogindetails");
sp.Visibility = Visibility.Visible;
TextBox fillusername = (TextBox)parentWindow.FindName("loggedintextBox");
fillusername.Text = userexists;
fillusername.Visibility = Visibility.Visible;
TextBox useridtxt = (TextBox)parentWindow.FindName("workeridfromlogin");
useridtxt.Text = userid.ToString();
Button logout = (Button)parentWindow.FindName("btnlogout");
logout.Visibility = Visibility.Visible;
Label lblcurrentuser = (Label)parentWindow.FindName("LoggedinDetails");
lblcurrentuser.Visibility = Visibility.Visible;
Ellipse startstopbtn1 = (Ellipse)parentWindow.FindName("startstopbtn");
startstopbtn1.Visibility = Visibility.Visible;
this.Visibility = Visibility.Collapsed;
Border br = (Border)parentWindow.FindName("brdbuttonLegend");
br.Visibility = Visibility.Visible;
StackPanel sp1 = (StackPanel)parentWindow.FindName("buttonLegend");
sp1.Visibility = Visibility.Visible;
}
else
{
txtPasswordbox.Background = new SolidColorBrush(Color.FromRgb(222, 0, 0));
Window parentWindow = Window.GetWindow(this);
TextBox fillusername = (TextBox)parentWindow.FindName("loggedintextBox");
fillusername.Text = " ";
//label1.content = userexists.tostring();
MessageBox.Show("Please enter the Correct Worker Id");
}
}
}
}
catch (Exception ex)
{
LogHelper.Log(LogTarget.File, ex.Message);
}
Kindly advice.
All replies (2)
Thursday, August 13, 2020 4:24 PM
One idea is to use my BaseConnection libary via NuGet here.
Place the server and initial catalog in app.config (some others might suggest placing this information in connection string in app.config, your choice).
<?xml version="1.0" encoding="utf-8" ?>
<configuration>
<startup>
<supportedRuntime version="v4.0" sku=".NETFramework,Version=v4.7.2" />
</startup>
<appSettings>
<add key="DatabaseServer" value=".\SQLEXPRESS" />
<add key="NorthWinCatalog" value="NorthWind2020" />
</appSettings>
</configuration>
Used in a class
using System;
using System.Data;
using System.Data.SqlClient;
using BaseConnectionLibrary.ConnectionClasses;
using static System.Configuration.ConfigurationManager;
namespace WindowsFormsApp1
{
public class SqlServerOperations : SqlServerConnection
{
public SqlServerOperations()
{
DatabaseServer = AppSettings["DatabaseServer"];
DefaultCatalog = AppSettings["NorthWinCatalog"];
}
public bool TestConnection()
{
using (var connection = new SqlConnection(ConnectionString))
{
try
{
connection.Open();
return true;
}
catch (Exception e)
{
return false;
}
}
}
public void Demo(string password)
{
using (var connection = new SqlConnection(ConnectionString))
{
using (var cmd = new SqlCommand("CheckWorkerIdv2", connection))
{
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add(new SqlParameter("@workerid", password));
connection.Open();
SqlDataReader reader = cmd.ExecuteReader();
// TODO
}
}
}
}
}
Note in the above ConnectionString comes from BaseConnectionLibrary.
Also you can change DatabaseServer and DefaultCatalog anytime besides in the constructor.
Please remember to mark the replies as answers if they help and unmarked them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
NuGet BaseConnectionLibrary for database connections.
StackOverFlow
Friday, August 14, 2020 2:22 AM
Hi cally_kalpana,
Thank you for posting here.
It sounds like it just needs an ordinary method, because you say that the table you access is fixed, so you only need to use the stored procedure name and parameter name as parameters.
public static ??? GetDataTable(string spName,string paramName)
{ //Read the connection string in App.config.
string connString = ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;
using (SqlConnection conn = new SqlConnection(connString))
{
conn.Open();
using (SqlCommand command = new SqlCommand(spName,conn))
{
command.CommandType = CommandType.StoredProcedure;
command.Parameters.Add(new SqlParameter(paramName, txtPasswordbox.Password));
SqlDataReader reader = command.ExecuteReader();
while(reader.Read())
{
userexists = reader.GetString(0);
userid = reader.GetInt32(1);
}
return ???;
}
}
}
Best Regards,
Timon
MSDN Community Support
Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact [email protected].