Tag Archives: SQL

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.

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.

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//