Sql Server 2005 - using SqlConnectionStringBuilder

Problem:
Method to connect to sql server 2005


Solution:

class attributes and accessor functions

private SqlConnection dbConn = null;


private string m_username = string.Empty;
private string m_password = string.Empty;
private string m_catalog = string.Empty;
private string m_datasource = string.Empty;
private string m_application = string.Empty;
private string m_version = string.Empty;
private bool m_IntegratedSecurity = false;

///

/// Name of user who has the appropriate connection privilges
///

public string User_Name
{
get { return (m_username); }
set { m_username = value; }
}

///

/// Password of user trying to connect
///

public string User_Password
{
get { return (m_password); }
set { m_password = value; }
}

///

/// Name of the catalog inside of SqlServer
///

public string DB_Catalog
{
get { return (m_catalog); }
set { m_catalog = value; }
}

///

/// Name of Server where SqlServer is installed
/// can use either the Name of the server or
/// the IP address
///

public string DB_Server
{
get { return (m_datasource); }
set { m_datasource = value; }
}

///

/// Name of application connecting to SqlServer
///

public string Application
{
get { return (m_application); }
set { m_application = value; }
}

public string DB_Version
{
get { return (m_version); }
set { m_version = value; }
}

public bool bool_IntegratedSecurity
{
get { return (m_IntegratedSecurity); }
set { m_IntegratedSecurity = value; }
}



public bool Connect()
{


//build new SqlConnectionStringBuilder object
System.Data.SqlClient.SqlConnectionStringBuilder csb =
new SqlConnectionStringBuilder();

try
{
//use Integrated Security
csb.IntegratedSecurity = this.bool_IntegratedSecurity;

if (this.DB_Server.Length > 0)
{
csb.DataSource = this.DB_Server;
}
else
{
this.int_error_level = ErrorLevel.Warning;
this.str_last_message = "Connection Error: You must provide the DataSource";
return (false);
}

if (this.Application.Length > 0)
{
csb.ApplicationName = this.Application;
}
else
{
csb.ApplicationName = "cRESqlServer";
}

if (this.DB_Catalog.Length > 0)
{
csb.InitialCatalog = this.DB_Catalog;
}
else
{
csb.InitialCatalog = "master";
}

csb.ConnectTimeout = 4;


this.dbConn = new SqlConnection();
this.dbConn.ConnectionString = csb.ConnectionString;
this.dbConn.Open();

this.DB_Version = this.dbConn.ServerVersion;


//success
return (true);
}
catch (System.Data.SqlClient.SqlException ex)
{
this.int_error_level = ErrorLevel.SevereError;
this.str_last_message = "Connect Error (SqlException):" + ex.Message.ToString();
this.HandleError();
return (false);
}
catch (System.Exception ex)
{
this.int_error_level = ErrorLevel.SevereError;
this.str_last_message = "Connect Error (Exception):" + ex.Message.ToString();
this.HandleError();
return (false);
}

}


#region ERROR LEVEL and LAST MESSAGE

private string mLastMessage = string.Empty;
private ErrorLevel mErrorLevel = 0;

public string str_last_message
{
get { return (mLastMessage); }
set { mLastMessage = value; }
}
public ErrorLevel int_error_level
{
get { return (mErrorLevel); }
set { mErrorLevel = value; }
}

#endregion

Datagrid - ItemTemplate, ToolTip

Problem:
Display a short description or abbreviation in a DataGrid column.

The short description or abbreviation by itself may not be general knowledge. Add the feature of allowing the user to hover over the value and receive a balloon help. The balloon help is a longer description of the abbreviation.



Solution:


<asp:TemplateColumn HeaderText="DataGridHeaderText">
<ItemTemplate>

<acronym title='<%# DataBinder.Eval(Container.DataItem, "FieldNameLongDescription") %> '>
<%# DataBinder.Eval(Container.DataItem, "FieldName") %>
</acronym>

</ItemTemplate>
</asp:TemplateColumn>

Convert Integer to Binary, Binary to Integer

Problem:
Convert integer to binary string

int binary
15 1111



Convert binary string to integer
binary int
1111 15



Solution:

//Convert binary string to an integer
int a_integer = Convert.ToInt32("1111", 2);

//convert an integer to a binary string
string a_binary_string = Convert.ToString(a_integer, 2);


Information:

How does the binary string "1111" equal 15

First, and most important, binary is read "right to left"

Second, each place holder position is 2^(position), zero position based
example:

(2^0)=1

if(a binary digit is 1) add to total

"1111" = (2^3) + (2^2) + (2^1) +(2^0)
^ -----
^
^
^ ------- ---- ---- ----
(2*2*2) (2*2) (2) (1)

8 + 4 + 2 + 1

= 15


"0010" = (0) + (0) + (2^1) + (0)
0 + 0 + 2 + 0

= 2

Convert DataTable to CSV

Problem:
User wants to view data in Excel.
1) Execute query against database.
2) Build DataTable object.
3) Populate Grid Control (DataGrid, GridView...)
4) Export DataTable to CSV file



Solution:
private int Export2CSV(DataTable dt,
string sFullPathAndFileName,
bool bNeedHeaderRow)
{

//usage history initialization
this.int_error_level = 0;
this.str_last_message = string.Empty;

try
{
System.IO.StreamWriter sw = new
System.IO.StreamWriter(sFullPathAndFileName,
false,
System.Text.Encoding.ASCII,
4096);


System.Text.StringBuilder buff = new System.Text.StringBuilder();

if (bNeedHeaderRow == true)
{
//build header
for (int c = 0; c < dt.Columns.Count; c++)
{
buff.Append(dt.Columns[c].ColumnName.ToString().ToUpper());
if (c < (dt.Columns.Count - 1))
{
buff.Append(",");
}
}

sw.WriteLine(buff.ToString());
buff.Length = 0;

}


for (int r = 0; r < dt.Rows.Count; r++)
{
for (int c = 0; c < dt.Columns.Count; c++)
{
switch (dt.Columns[c].DataType.ToString().ToUpper())
{
case "SYSTEM.STRING":
case "SYSTEM.DATETIME":
buff.Append("\"" + dt.Rows[r][c].ToString() + "\"");
break;
default:
buff.Append(dt.Rows[r][c].ToString());
break;
}

if (c < (dt.Columns.Count - 1))
{
buff.Append(",");
}
}

sw.WriteLine(buff);
buff.Length = 0;
}


sw.Flush();
sw.Close();
sw = null;

return (0);
}
catch (System.Exception ex)
{
this.int_error_level = 1;
this.str_last_message = ex.Message.ToString();
return (-1);
}
}

Drop Down List - Show Month Name and Year

Problem:
populate ddl with MonthName and Year with the previous 12 months

Solution:
private void InitializeStartDate(System.Web.UI.WebControls.ListControl lc)
{

//clear out list control
lc.Items.Clear();

DateTime start = DateTime.Now;
DateTime workDate = new DateTime(start.Year, start.Month, 1);

int months = 0;
while (months < 12)
{
ListItem li = new ListItem();

//format display {$MonthName} {$Year} i.e January 2006
li.Text = string.Format("{0:MMMM} {1:yyyy}",workDate,workDate);

//get value of display date
li.Value = workDate.ToShortDateString();

//assign ListItem to DropDownList
lc.Items.Add(li);

//decrement workDate by one month
workDate = workDate.AddMonths(-1);

//increment number of items counter
months++;
}
}

Regular Expression : Validate State Abbreviation

Problem:
Validate user input.
State Abbreviations.
Only allow valid input

Solution:

using System.Text.RegularExpressions;

//input from user
string state = this.textBox1.Text.ToString().Trim().ToUpper();

if (Regex.IsMatch(state, "^(?:(A[KLRZ]*|C[AOT]*|D[CE]*|F[L]*|G[A]*|H[I]*|I[ADLN]*|K[SY]*|L[A]*|M[ADEINOST]*|N[CDEHJMVY]*|O[HKR]*|P[AR]*|R[I]*|S[CD]*|T[NX]*|U[T]*|V[AIT]*|W[AIVY]*))$") == true)
{
MessageBox.Show("Valid");
}
else
{
MessageBox.Show("InValid");
}


Results:
Good: A, AK, AZ
Bad: AS, AM

C# .Net 2.0 - get application name

Problem:
How to console print a program application name?


Solution:


System.Console.WriteLine
("Usage {0}", AppDomain.CurrentDomain.SetupInformation.ApplicationName);

ASP.Net GridView .Net 2.0

GridView Control.

Problem:
Add HTML to HeaderText

Solution:
Set the attribute HtmlEncode to false

Example:
<asp:BoundField DataField="pk" HeaderText="Primary<br />Key" HtmlEncode="false" />

Comments:
I also found that to add DataFormatString="{0:d}" to work, you'll also have to set the HtmlEncode attribute to false for that given BoundField

Example:
<asp:BoundField DataField="AsOfDate" HeaderText="As Of Date" HtmlEncode="false" DataFormatString="{0:d}">