So I have been programming in VB.net and want to learn C#.
I "successfully" created a simple program that retrieves data from Sql Server during Form1_Load
. The program also retrieves data from an MDB file and converts it into a connection string, which I can use throughout the system. I would like to ask for your comments regarding my first time coding in C#.
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Data;
using System.Windows.Forms;
using System.Data.SqlClient;
using System.Data.Odbc;
using System.Data.OleDb;
namespace TestInventoryApp
{
public partial class frmForex : Form
{
public bool blnFrx = false;
public bool blnFpass = true;
public frmForex()
{
InitializeComponent();
}
static public string connect()
{
//I used using because I have read somewhere that 'using' properly disposes an object
//This method checks if I can connect to sql server using MDBfile
string strCONNECTIONSTRING;
string TableName = "BACKUP";
string constr = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\MyProject\bin\system_common.mdb";
string connstr;
using (OleDbConnection dbCn = new OleDbConnection(constr))
using (OleDbCommand dbCo = dbCn.CreateCommand())
{
dbCo.CommandText = "SELECT m_TableType.TableType AS TableType,m_TableType.DatabaseSystem AS DatabaseSystem,m_TableType.ConnectionString AS ConnectionString FROM m_table,m_TableType WHERE m_table.TableType = m_TableType.TableType AND m_table.TableName = '" + TableName +"'";
dbCn.Open();
using (OleDbDataReader dbRE = dbCo.ExecuteReader())
{
if (dbRE.Read())
{
//This line of code gets a single string in the MDB file
//Then chops it into parts to make a connection string to sql server
strCONNECTIONSTRING = dbRE["ConnectionString"].ToString();
strCONNECTIONSTRING = strCONNECTIONSTRING.Substring(27);
int hhh = strCONNECTIONSTRING.IndexOf(";");
int aaa = strCONNECTIONSTRING.IndexOf(";")+ 1;
string dtsrc = strCONNECTIONSTRING.Substring(0, hhh);
strCONNECTIONSTRING = strCONNECTIONSTRING.Substring(aaa);
strCONNECTIONSTRING = strCONNECTIONSTRING.Substring(4);
hhh = strCONNECTIONSTRING.IndexOf(";");
aaa = strCONNECTIONSTRING.IndexOf(";")+ 1;
string userid = strCONNECTIONSTRING.Substring(0, hhh);
strCONNECTIONSTRING = strCONNECTIONSTRING.Substring(aaa);
strCONNECTIONSTRING = strCONNECTIONSTRING.Substring(4);
hhh = strCONNECTIONSTRING.IndexOf(";");
aaa = strCONNECTIONSTRING.IndexOf(";") + 1;
string password = strCONNECTIONSTRING.Substring(0, hhh);
strCONNECTIONSTRING = strCONNECTIONSTRING.Substring(aaa);
strCONNECTIONSTRING = strCONNECTIONSTRING.Substring(9);
string initcat = strCONNECTIONSTRING;
connstr = "Password=" + password + ";Persist Security Info=True;User ID="+ userid +";Initial Catalog=" + initcat + ";Data Source=" + dtsrc + "";
try
{
SqlConnection conn = new SqlConnection(connstr);
conn.Open();
}
// I'm still figuring out how to use messagebox.show(e.exception) in c#
catch (Exception e)
{
return "";
}
return connstr;
}
}
}
// an error occurs everytime, stating that "Not" all code paths return a value, hence this code
return "";
}
public void sp_FRX_getForexToday(string frxDate, string connected)
{
//This sub gets data from sql server
using (SqlConnection conn = new SqlConnection(connected))
using (SqlCommand sqlcomm = conn.CreateCommand())
{
sqlcomm.CommandText = "dbo.sp_FRX_getFRX";
sqlcomm.CommandType = CommandType.StoredProcedure;
sqlcomm.Parameters.AddWithValue("@Date", frxDate);
using (SqlDataAdapter dta = new SqlDataAdapter(sqlcomm))
using (DataTable dt = new DataTable())
{
dta.Fill(dt);
if (dt.Rows.Count > 0)
{
txtPHP.Text = dt.Rows[0][0].ToString();
txtJPY.Text = dt.Rows[0][1].ToString();
txtEUR.Text = dt.Rows[0][2].ToString();
}
else
{
txtPHP.Text = "";
txtJPY.Text = "";
txtEUR.Text = "";
}
}
}
}
private void Form1_Load(object sender, EventArgs e)
{
string connected;
connected = connect();
if (connected.Length > 0)
{
MessageBox.Show("Successful");
}
blnFpass = false;
sp_FRX_getForexToday("2019/06/03", connected);
}
}
}
Other things that I would like to learn:
- Regarding method
connect
, I would like to use the connection throughout the system, so I declared a local variable inForm1_Load
namedconnected
and put the connection in it. Is this the best practice? In Vb.Net, I could useconn.open
and declarePublic conn As New SqlClient.SqlConnection
then useconn
throughout the code. How can I do it in C#?
Thank you for your time and comments.
OleDbConnectionStringBuilder
to help you out. \$\endgroup\$ – dfhwze Jun 8 '19 at 8:36Other things that I would like to learn
almost makes this question off-topic (please see codereview.stackexchange.com/help/dont-ask). I wouldn't store the connection itself, but I would store the connection string. The connection string could be stored in in either the app or the program. \$\endgroup\$ – pacmaninbw Jun 8 '19 at 13:21