Answer to Question #49968 in C# for bhavin

Question

The development team of SoftSols Inc. has revamped the software according to the requirements
of FlyHigh Airlines and is in the process of testing the software. While testing the software, the
team encounters the following issues:
The operations-related data of FlyHigh Airlines is stored in a central database. The software
fails to respond to user inputs, if there is a connectivity problem with the database. Add the code
snippet that the development team should use to ensure that the application shows a userfriendly
message, if such a situation arises in future.[
The application used to calculate the cost of carrying additional luggage results in erroneous
amount, if the weight of the luggage is a fractional number. Help the development team modify
the code snippet so that the cost of carrying additional luggage is calculated correctly.

Answer 1

Answer:
using System;
usingSystem.Collections.Generic;
usingSystem.ComponentModel;
usingSystem.Data;
usingSystem.Data.SqlClient;
usingSystem.Drawing;
usingSystem.Linq;
usingSystem.Text;
usingSystem.Windows.Forms;
usingSystem.Runtime.InteropServices;
usingSystem.Data.Sql;

namespace DbProgram
{

public partial classForm1 : Form
{
publicForm1()
{
InitializeComponent();
this.textBox4.Text= "";
if(Trusted_connection== true)
{
this.radioButton1.Checked=true;
this.radioButton2.Checked=false;
this.textBox4.Enabled=false;
this.textBox5.Enabled=false;
}
else
{
this.radioButton1.Checked=false;
this.radioButton2.Checked=true;
this.textBox4.Enabled=true;
this.textBox5.Enabled=true;
}
}
boolTrusted_connection;
privatevoid button2_Click_1(objectsender,EventArgs e)
{
Close();
}
privatevoid button3_Click(objectsender,EventArgs e)
{
if(radioButton1.Checked == true)
Trusted_connection =true;
else
Trusted_connection =false;
Close();
}
privatevoidradioButton1_CheckedChanged(object sender, EventArgse)
{
button3.Enabled =false;
if(this.radioButton1.Checked== true)
{ Trusted_connection =true; }
else
{ Trusted_connection =false; }
if(Trusted_connection == true)
{
this.textBox4.Enabled=false;
this.textBox5.Enabled=false;
}
else
{
this.textBox4.Enabled=true;
this.textBox5.Enabled=true;
}
}
privatevoidradioButton2_CheckedChanged(object sender, EventArgse)
{
button3.Enabled =false;
if(this.radioButton2.Checked== false)
{ Trusted_connection =true; }
else
{ Trusted_connection =false; }
if(Trusted_connection == true)
{
this.textBox4.Enabled=false;
this.textBox5.Enabled=false;
}
else
{
this.textBox4.Enabled=true;
this.textBox5.Enabled=true;
}
}
privatevoid button5_Click_1(objectsender,EventArgs e)
{
FolderBrowserDialogopenDirDialog= new FolderBrowserDialog();
openDirDialog.SelectedPath=textBox3.Text;
if(openDirDialog.ShowDialog() ==DialogResult.OK)
{
try
{
textBox3.Text=openDirDialog.SelectedPath.ToString();

}
catch(Exception ex)
{
MessageBox.Show("Error opening thespecified path! " +ex.Message);
}
}
}

privatevoid button6_Click(objectsender,EventArgs e)
{
button3.Enabled =false;
comboBox2.Enabled =false;
comboBox2.Items.Clear();

button6.Text ="Search...";
button6.Enabled =false;

#region Gettingtheserver name of the ODBC-sources

String[]instances =SqlLocator.GetServers();

foreach(String elementininstances)
{
comboBox2.Items.Add(element);
}

#end region
#region Getting alistof servers in a standard way
DataTablet =SqlDataSourceEnumerator.Instance.GetDataSources();
for(int i = 0; i <t.Rows.Count; i++)
{
stringServerName =t.Rows[i]["ServerName"].ToString();
stringInstanceName =t.Rows[i]["InstanceName"].ToString();
stringx =(InstanceName.Length > 0 ? (ServerName + "\\"+ InstanceName) :
ServerName);
comboBox2.Items.Add(x);
}
#end region

if(comboBox2.Items.Count!= 0)
{
comboBox2.Text=comboBox2.Items[0].ToString();

try
{
GetDataBase();
}
catch
{
MessageBox.Show("The selected server isnot available. \nPleasechoose another server",
"Attention",MessageBoxButtons.OK, MessageBoxIcon.Information);
}
}

button6.Text ="Get the list";
comboBox2.Enabled =true;
button6.Enabled = true;

}
#region Getting a list ofdatabases
privatevoid GetDataBase()
{
comboBox1.Items.Clear();
comboBox1.Text ="";
stringSecurity;
if(radioButton1.Checked== true)
{
Security ="Integrated Security=true";
}
else
{
Security =@"Persist Security Info=True;User ID=" +textBox5.Text +
";Password=" +textBox4.Text + "";

}

if(comboBox1.Text !="")
{ Security ="Initial Catalog=" + comboBox1.Text + ";" + Security; }

SqlConnectionsqlConn =new SqlConnection(@"Server=" + comboBox2.Text + ";" +
Security);

try
{
sqlConn.Open();

SqlCommandsqlCmd = newSqlCommand();
sqlCmd.Connection =sqlConn;
sqlCmd.CommandType =CommandType.StoredProcedure;
sqlCmd.CommandText ="sp_helpdb";

SqlDataAdapterda = newSqlDataAdapter(sqlCmd);
DataSetds = newDataSet();
da.Fill(ds);

foreach(DataRow row inds.Tables[0].Rows)
{
comboBox1.Items.Add(row["name"].ToString());
}
sqlConn.Close();



if(comboBox2.Text =="")
{
comboBox1.Enabled = false;
button3.Enabled = false;
button7.Enabled = false;
}
else
{
comboBox1.Enabled = true;
button7.Enabled = true;
}
}
catch
{
MessageBox.Show("Theselected server is not available.\nSelect adifferent server.",
"Attention",MessageBoxButtons.OK, MessageBoxIcon.Information);
stringtest =comboBox2.Text;
}
}
#endregion



privatevoid button7_Click(objectsender,EventArgs e)
{
StringSecurity ="";
if(comboBox1.Text =="")
{
comboBox1.Text ="master";
}

if(radioButton2.Checked== true)
{
Security =@"Persist Security Info=True;User ID=" +textBox5.Text +
";Password=" +textBox4.Text + "";
}
elseif(radioButton1.Checked == true)
{
Security ="Integrated Security=true";
}

SqlConnectionsqlConn =new SqlConnection(@"Server=" + comboBox2.Text + ";Initial
Catalog=" + comboBox1.Text + ";" + Security);

try
{
string_databasename =comboBox1.Text;
sqlConn.Open();
MessageBox.Show("Testingthe connection is made.", "Connecting to a database",
MessageBoxButtons.OK, MessageBoxIcon.Information);
SqlCommandsqlCmd = newSqlCommand();
sqlCmd.Connection =sqlConn;
sqlCmd.CommandType =CommandType.StoredProcedure;
sqlCmd.CommandText ="sp_helpdb";

SqlDataAdapterda = newSqlDataAdapter(sqlCmd);
DataSetds = newDataSet();
da.Fill(ds);

foreach(DataRow rowinds.Tables[0].Rows)
{
comboBox1.Items.Add(row["name"].ToString());
}
sqlConn.Close();
comboBox1.Text =_databasename;
button3.Enabled = true;
}
catch
{
MessageBox.Show("Testingthe connection failed.\n\nSQL Server is notavailable.\nMaybe not correctly
specify a username and password.", "Connecting to a database", MessageBoxButtons.OK,
MessageBoxIcon.Error);
}


}

privatevoid ServerOptionsForm_Load(objectsender, EventArgse)
{

}



}

public class SqlLocator
{
[DllImport("odbc32.dll")]
privatestatic extern shortSQLAllocHandle(shorthType, IntPtr inputHandle, out IntPtroutputHandle);
[DllImport("odbc32.dll")]
privatestatic extern shortSQLSetEnvAttr(IntPtrhenv, int attribute, IntPtrvaluePtr, int strLength);
[DllImport("odbc32.dll")]
privatestatic extern shortSQLFreeHandle(shorthType, IntPtr handle);
[DllImport("odbc32.dll", CharSet =CharSet.Ansi)]
privatestatic extern shortSQLBrowseConnect(IntPtrhconn, StringBuilder inString,
shortinStringLength,StringBuilder outString, short outStringLength,
outshortoutLengthNeeded);

privateconst shortSQL_HANDLE_ENV = 1;
privateconst shortSQL_HANDLE_DBC = 2;
privateconst intSQL_ATTR_ODBC_VERSION = 200;
privateconst intSQL_OV_ODBC3 = 3;
privateconst shortSQL_SUCCESS = 0;

privateconst shortSQL_NEED_DATA = 99;
privateconst shortDEFAULT_RESULT_SIZE = 1024;
privateconst stringSQL_DRIVER_STR ="DRIVER=SQL SERVER";

privateSqlLocator() { }

public static string[]GetServers()
{
string[]retval = null;
stringtxt = string.Empty;
IntPtrhenv =IntPtr.Zero;
IntPtrhconn =IntPtr.Zero;
StringBuilderinString =new StringBuilder(SQL_DRIVER_STR);
StringBuilderoutString= new StringBuilder(DEFAULT_RESULT_SIZE);
shortinStringLength =(short)inString.Length;
shortlenNeeded = 0;

try
{
if(SQL_SUCCESS ==SQLAllocHandle(SQL_HANDLE_ENV, henv, outhenv))
{
if(SQL_SUCCESS == SQLSetEnvAttr(henv,SQL_ATTR_ODBC_VERSION, (IntPtr)SQL_OV_ODBC3, 0))
{
if (SQL_SUCCESS ==SQLAllocHandle(SQL_HANDLE_DBC, henv, out hconn))
{
if (SQL_NEED_DATA ==SQLBrowseConnect(hconn,inString, inStringLength, outString,
DEFAULT_RESULT_SIZE, out lenNeeded))
{
if (DEFAULT_RESULT_SIZE < lenNeeded)
{
outString.Capacity = lenNeeded;
if (SQL_NEED_DATA !=SQLBrowseConnect(hconn,inString, inStringLength, outString,
lenNeeded, outlenNeeded))
{
throw newApplicationException("Unabledto aquire SQL Servers from ODBC
driver.");
}
}
txt =outString.ToString();
int start = txt.IndexOf("{")+ 1;
int len = txt.IndexOf("}")- start;
if ((start > 0) && (len > 0))
{
txt=txt.Substring(start, len);
}
else
{
txt = string.Empty;
}
}
}
}
}
}
catch(Exception ex)
{
//Throwaway any errorif we are not in debug mode
#if (DEBUG)
MessageBox.Show(ex.Message,"AcquireSQL Servier List Error");
#endif
txt = string.Empty;
}
finally
{
if(hconn !=IntPtr.Zero)
{
SQLFreeHandle(SQL_HANDLE_DBC, hconn);
}
if(henv != IntPtr.Zero)
{
SQLFreeHandle(SQL_HANDLE_ENV, hconn);
}
}

if(txt.Length > 0)
{
retval =txt.Split(",".ToCharArray());
}

returnretval;
}
}
}