General Queries

Question

I am very new to C# so sorry if I am fumbling my way through this. I am trying to produce an database insert method for saving contact information on a windows form. I need to know a couple of things: 1. Is there a simpler way to pass in parameters 2. How do I get the next unique id for the table from my oracle sequence in C# 3. How do I pass back the database ID from my combo box value instead of the text value. ===================== private void SaveContact() { string strSQL; int i; string strSurname; string strForename; string strTitle; string strPhone; string strFax; string strEmail; int intBusUnit; int intDiv; int intLoc; int intTeam; strSurname = this.txtSurname.Text.Trim(); strForename = this.txtForename.Text.Trim(); strTitle = this.cboTitle.Text.Trim(); strPhone = this.txtPhone.Text.Trim(); strFax = this.txtFax.Text.Trim(); strEmail = this.txtEmail.Text.Trim(); intBusUnit = this.cboBusUnit.SelectedIndex; intDiv = 1; //this.cboDivision.ValueMember; //SelectedValue intLoc = 1; //this.cboLocation.Text; intTeam = 1; //this.cboTeam.Text; strSQL = "INSERT INTO PERSON (ID,SURNAME,FORENAME,TITLE,PHONE,EMAIL,FAX,DIVISIONID,BUSINESSUNITID, LOCATIONID, TEAMID)"; strSQL += "VALUES (20,'"+strSurname+"','"+strForename+"','"+strTitle+"','"+strPhone+"','"+strEmail+"','"+strFax+"',"+intDiv+","+intBusUnit+","+intLoc+","+intTeam+")"; try { // Create database Connection DataProvider.DataProvider provider; Utilities objUtil = new Utilities(); string strConnectionString = objUtil.GetConnectionString(); provider = new DataProvider.DataProvider(strConnectionString,DataProvider.DataProvider.DBType.OleDb); // Execute the insert i = provider.ExecuteNonQuery(strSQL); MessageBox.Show ("New Contact Saved.", "Saved", MessageBoxButtons.OK, MessageBoxIcon.Information); } finally { MessageBox.Show ("Nice one geezer!", "Saved", MessageBoxButtons.OK, MessageBoxIcon.Information); } }

Answer 1

1. No there is no simpler way than this. It doesn't seem very complicated to me. This is the simplest and yet the worst for two reasons. a) It's insecure - the string concatenation directly from the controls' properties without validation makes the code vulnerable to SQL injection attacks. (google for "SQL injection" to learn more if you haven't already - this is an important issue) b) Bad performance - if you pass parameters as parameters, not hardcoded in the sql statement I guess that oracle will use some optimizations. Do it like this(the example is for MS Sql data provider but I guess you get the idea): SqlCommand cmdAddComment = new SqlCommand( @" INSERT INTO WorkerActivityComments( CommentID, UserID, CategoryID, CommentDate, CommentText ) VALUES( @CommentID, @WorkerID, @CategoryID, @CommentDate, @CommentText)"); cmdAddComment.Parameters.Add( "@CommentID", SqlDbType.Int ).Value = TakeNumber.GetID( "Comments" ); cmdAddComment.Parameters.Add( "@WorkerID", SqlDbType.Int ).Value = userID; cmdAddComment.Parameters.Add( "@CategoryID", SqlDbType.Int ).Value = catID; cmdAddComment.Parameters.Add( "@CommentDate", SqlDbType.DateTime ).Value = DateTime.Now; 2. I have no idea, never used oracle. I've only heard of these sequences and how good it would be if we had them in MS Sql Server ;-) 3. I guess that ComboBox.SelectedValue shoud do the work I hope some of this can help