Thomas Sampson


1 Comment

SQL Generator

I found this tool today which allows you to use a webUI to design a database table, and will then generate you the SQL code to create that table at runtime.

http://ondras.zarovi.cz/sql/demo/

I used this to design a table to use with sqlite3 and although the code generated is intended for use with mysql, a few minor adjustments allowed me to execute this on a sqlite3 database with no problems.

Advertisements


Leave a comment

Connection string rules

Important rules for connection strings

This is general rules adopted by most drivers and providers. However, keep in mind that these rules is not 100% accurate for every driver and every situation.

Never the less, following and knowing about these basic rules will keep some common problems out of your way. Okay? Here we go..

  • All blank characters, except those placed within a value or within quotation marks, are ignored
  • Blank characters will though affect connection pooling mechanism, pooled connections must have the EXACT same connection string
  • If a semicolon (;) is part of a value it must be delimited by quotation marks (“)
  • Use a single-quote (‘) if the value begins with a double-quote (“)
  • Conversely, use the double quote (“) if the value begins with a single quote (‘)
  • No escape sequences are supported
  • The value type is NOT relevant
  • Names are case iNsEnSiTiVe
  • If a KEYWORD=VALUE pair occurs more than once in the connection string, the value associated with the LAST occurrence is used
  • But!… if the PROVIDER keyword occurs multiple times in the string, the FIRST occurrence is used.
  • If a keyword contains an equal sign (=), it must be preceded by an additional equal sign to indicate that it is part of the keyword.


Leave a comment

Notes on SQL Server

 

Include the reference;

using System.Data.SqlClient;

to connect and open…

SqlConnection myConnection = new SqlConnection(“user id=SQLSERVERUSERNAME;” +”password=SQLSERVERPASSWORD;server=SERVER PATH HERE, USE . IF LOCAL;” + “Trusted_Connection=yes;”+ “database=DATABSE TO CONNECT TO; “);
myConnection.Open();

NOTE: Connection must be made EVERY time data is added / retrieved

To Add Data the connected Database….

//create new command to add data//
SqlCommand COMANDNAMEHERE=newSqlCommand(“INSERTINTO tablename(collumname) ” //here colum names can be comma seperated//
+ “Values (valuetoadd)”, myConnection);//here the values to add can be comma seperated//

// myConnection is name of establishd connection//

//this command will insert new value (valuetoadd) into tablename in specified column//

COMMANDNAMEHERE.ExecuteNonQuery();
//run command//

To Retrieve Data from the connected Database and assign to a label….

SqlDataReader READERNAMEHERE = null; //create new reader object//
//create new command to retrieve data//

SqlCommand COMMANDNAMEHERE = new SqlCommand(“select * from tablename where collum LIKE ‘value’, myConnection);
//this command selects all the values from the table where the value appears in the collum//
READERNAMEHERE = COMMANDNAMEHERE.ExecuteReader();
//code above runs the command into the reader and starts it ‘reading’//
while(READERNAMEHERE.Read())
{
labelname.Text+=myReader[“Collum”].ToString();
}
//while ever the reader is reading, grab the values from the collumn and add them to a multiline text box//

CLOSE THE CONNECTION (Must be done after data has been added / retrieved)

MyConnection.Close(); //myConnection is the name used when creating connection//