Tips & Tricks

Re: How To: Connect to SQL using C# and SELECT INSERT UPDATE ?

Simon Deshaies — Mon, 06 Apr 2009 18:37:26 GMT

clownshoes2009,

Do you load your data on the page_load. If so when you post back it load the page_load before it gets to your call. Using ASP.NET control is always complex since they behave unexpectedly.

Regards,

Simon

Re: How To: Connect to SQL using C# and SELECT INSERT UPDATE ?

clownshoes2009 — Mon, 06 Apr 2009 16:57:02 GMT

Hi there

Just wondering if there was a solution for my problem.

I have a sqldatasource and gridview displaying the contents of a table. The display works fine BUT update and delete don't work. No error message - just flips back to what was orgianlly there.

I have two tables though, both have a primary datakey called CustomerId. Now one of my tables updates and deletes perfectly. The second one, doesn't. Is this the reason do you think?

Re: How To: Connect to SQL using C# and SELECT INSERT UPDATE ?

umar.tarar — Tue, 31 Mar 2009 15:47:16 GMT

Dear Simon,

can you please guide me what should I do to speed up my data insertion (I'm using C# & MySQL), as I've to insert thouands of data rows per execution and it takes huge amount of time (in hours). At present I'm usiing INSERT command inside a loop (loop iterates untill I still have data to be inserted that I'm reading from a file), although I've put the MySQLCommand variables' initializations outside the loop.

What comes to my mind is to find out a way to insert data as bulk or may be as a transaction, but I dont know yet how to insert data in bulk to optimize speed using C#.NET

so can you help me in this case.

thanks in advance

Umar

Re: How To: Connect to SQL using C# and SELECT INSERT UPDATE ?

Simon Deshaies — Mon, 30 Mar 2009 13:06:00 GMT

clownshoes2009:
System.Data.SqlClient.SqlException: The data types text and nvarchar are incompatible in the equal to operator.

It means that somewhere in your code you try to assign a text to nvarchar SQL types. This is either when you assign the parameter in the SQL command or it's at the SQL server, you try to assign a typed nvarchar value to a text column.

This is what I can think of, you need to run in debug step into and try to execute your query directly against the SQL Server in management studio. Like this you will see if it's your SQL or you C# the issue.

There are no magic tricks. Just debugging.

Regards,

Simon

Re: How To: Connect to SQL using C# and SELECT INSERT UPDATE ?

clownshoes2009 — Sat, 28 Mar 2009 16:55:55 GMT

One tihng i have noticed. I was testing my table view with a grid view.

Upon checking whether i could delete or update an entry i received an error

System.Data.SqlClient.SqlException: The data types text and nvarchar are incompatible in the equal to operator.

How does one fix that?

Re: How To: Connect to SQL using C# and SELECT INSERT UPDATE ?

clownshoes2009 — Sat, 28 Mar 2009 15:12:39 GMT

Ok - my error lived up to my username. It was a setting in the SQL table itself.

Perhaps you can help with another thing though. I have got the data writing to the table. Now after this there is a payment process. When they have paid I'd like the final column - payment to be updated with either yes or no.

Any ideas on how to tackle this? I'm working with Alertpay's IPN, butI'm not sure of how to do this bit.

Any help would be fab

Re: How To: Connect to SQL using C# and SELECT INSERT UPDATE ?

Simon Deshaies — Sat, 28 Mar 2009 12:13:20 GMT

Hi clownshoes2009,

What is your exception? You code seems good...

Regards,

Simon

Re: How To: Connect to SQL using C# and SELECT INSERT UPDATE ?

clownshoes2009 — Fri, 27 Mar 2009 20:13:18 GMT

Hi Simon - any chance you can check this code and see why it isn't inserting vlaues into my databse?

using System;

using System.Data.SqlClient;

using System.Text;

using System.Data;

using System.Configuration;

using System.Collections;

using System.Web;

using System.Net;

using System.Net.Mail;

using System.Web.Security;

using System.Web.UI;

using System.Web.UI.WebControls;

using System.Web.UI.WebControls.WebParts;

using System.Web.UI.HtmlControls;

public partial class competition : System.Web.UI.Page

{

protected void Page_Load(object sender, EventArgs e)

{

Label1.Text = txtFname.Text;

Label2.Text = txtLname.Text;

Label3.Text = txtEmail.Text;

Label4.Text = txtad1.Text;

Label5.Text = txtpostcode.Text;

Label6.Text = RadioButtonList1.SelectedValue;

Label7.Text = RadioButtonList2.SelectedValue;

Label8.Text = RadioButtonList3.SelectedValue;

Label9.Text = RadioButtonList4.SelectedValue;

Label10.Text = txtTieBreaker.Text;

}

protected void Wizard1_FinishButtonClick(object sender, WizardNavigationEventArgs e)

{

string strQuery;

SqlCommand cmd;

strQuery = "INSERT INTO competition (FirstName, LastName, PhoneNumber, Email, Address, PostCode, Question1, Question2, Question3, Question4, TieBreaker) values (@FirstName, @LastName, @PhoneNumber, @Email, @Address, @PostCode, @Question1, @Question2, @Question3, @Question4, @TieBreaker)";

cmd = new SqlCommand(strQuery);

SmtpClient smtpClient = new SmtpClient();

MailMessage message = new MailMessage();

StringBuilder sb = new StringBuilder();

sb.Append("First Name: ");

sb.Append(txtFname.Text);

sb.Append("\r\n");

sb.Append("Last Name: ");

sb.Append(txtLname.Text);

sb.Append("\r\n");

sb.Append("Email: ");

sb.Append(txtEmail.Text);

sb.Append("\r\n");

sb.Append("Phone Number: ");

sb.Append(txtMobile.Text);

sb.Append("\r\n");

sb.Append("Address 1: ");

sb.Append(txtad1.Text);

sb.Append("\r\n");

sb.Append("Post Code: ");

sb.Append(txtpostcode.Text);

sb.Append("\r\n");

sb.Append("Question 1: ");

sb.Append(RadioButtonList1.SelectedItem.Value);

sb.Append("\r\n");

sb.Append("Question 2: ");

sb.Append(RadioButtonList2.SelectedItem.Value);

sb.Append("\r\n");

sb.Append("Question 3: ");

sb.Append(RadioButtonList3.SelectedItem.Value);

sb.Append("\r\n");

sb.Append("Question 4: ");

sb.Append(RadioButtonList4.SelectedItem.Value);

sb.Append("\r\n");

sb.Append("Tie Breaker: ");

sb.Append(txtTieBreaker.Text);

sb.Append("\r\n");

MailAddress fromAddress = new MailAddress("email@mydomain.com");

smtpClient.Host = "localhost";

System.Net.NetworkCredential NetworkCred = new System.Net.NetworkCredential();

NetworkCred.UserName = "email@mydomain.com";

NetworkCred.Password = "password";

smtpClient.UseDefaultCredentials = true;

smtpClient.Credentials = NetworkCred;

smtpClient.Port = 25;

message.From = fromAddress;

message.To.Add(txtEmail.Text);

message.Bcc.Add("email@mydomain.com");

message.Subject = "Entry";

message.IsBodyHtml = false;

message.Body = sb.ToString();

smtpClient.Send(message);

cmd.Parameters.AddWithValue("@FirstName", txtFname.Text);

cmd.Parameters.AddWithValue("@LastName", txtLname.Text);

cmd.Parameters.AddWithValue("@PhoneNumber", txtMobile.Text);

cmd.Parameters.AddWithValue("@Email", txtEmail.Text);

cmd.Parameters.AddWithValue("@Address1", txtad1.Text);

cmd.Parameters.AddWithValue("@PostCode", txtpostcode.Text);

cmd.Parameters.AddWithValue("@Question1", RadioButtonList1.SelectedItem.Value);

cmd.Parameters.AddWithValue("@Question2", RadioButtonList2.SelectedItem.Value);

cmd.Parameters.AddWithValue("@Question3", RadioButtonList3.SelectedItem.Value);

cmd.Parameters.AddWithValue("@Question4", RadioButtonList4.SelectedItem.Value);

cmd.Parameters.AddWithValue("@TieBreaker", txtTieBreaker.Text);

InsertUpdateData(cmd);

Response.Redirect("anotherpage.aspx");

}

private Boolean InsertUpdateData(SqlCommand cmd)

{

String strConnString = System.Configuration.ConfigurationManager.ConnectionStrings["constring"].ConnectionString;

SqlConnection con = new SqlConnection(strConnString);

cmd.CommandType = CommandType.Text;

cmd.Connection = con;

try

{

con.Open();

cmd.ExecuteNonQuery();

return true;

}

catch (Exception ex)

{

Response.Write(ex.Message);

return false;

}

finally

{

con.Close();

con.Dispose();

}

Thanks!

Re: How To: Connect to SQL using C# and SELECT INSERT UPDATE ?

Simon Deshaies — Thu, 26 Mar 2009 13:01:40 GMT

Hi phoebe_ph,

The VB you posted is not valid... can you give us the whole method/function/sub ?

Thanks,

Simon

How To: Connect to SQL using C# and SELECT INSERT UPDATE ?

phoebe_ph — Wed, 25 Mar 2009 21:43:10 GMT

Hello to all,

I am a newbie in programming and I find it overwhelming. Is ther some expert here who could transalte this code (powerbuilder code with oracle dab) to C# please? Thank you very much!

--------------------------------

String old_mrg, new_mrg

String old_sns, new_sns

Select mrg_account, sns_number
into :old_mrg, :old_sns
from pub.subscription
where account = :his_acct;

If SQLCA.SQLCode = 0 then

Update pub.subscription
  set mrg_account = :mrg_acct,
    sns_number = :sns_acct
  where account = :his_acct;
commit;

If SQLCA.SQLCode = 0 then

  Select  mrg_account, sns_number
   into :new_mrg, :new_sns
   from pub.subscription
   where account = :his_acct;

  MessageBox('Change MRG account number',                &
       'Company account number: ' + string(his_acct) + '.~n' +       &
       '   Old -- MRG account: ' + old_mrg + '  SNS number: ' + old_sns + '.~n'+ &
       '   New -- MRG account: ' + new_mrg + '  SNS number: ' + new_sns + '.'    )
  return 'Update
Else
  return 'Error (' + string(SQLCA.SQLCode) +') on update.'
End If
Else
return 'Error (' + string(SQLCA.SQLCode) +') finding account# ' + string(his_acct) + '.'
End if

Re: How To: Connect to SQL using C# and SELECT INSERT UPDATE ?

free2step.com — Sat, 14 Feb 2009 07:52:47 GMT

thanks, I solved my problem with help - Free2step

Re: How To: Connect to SQL using C# and SELECT INSERT UPDATE ?

Simon Deshaies — Fri, 13 Feb 2009 13:23:24 GMT

Hi Ranganayaki,

What I would do is create a object that represent yout product. Query the database and create a generic list of this object. Then when you want to add a new product you call the Save method and in Save you check if product exist in database.

Here is the example:

1    public partial class _Default : System.Web.UI.Page
2    {
3        protected void Page_Load(object sender, EventArgs e)
4        {
5            // get products
6            List<Product> list = ProductHelper.GetAllProducts();
7
8            // cahnge first product
9            list[0].Name = "Apple";
10
11           // Save product
12           list[0].Save();
13
14       }
15   }
16
17   public class Product
18   {
19       /// <summary>
20       /// Create a instance of Product.
21       /// </summary>
22       /// <param name="name">Product name.</param>
23   /// <param name="description">Product description.</param>
24       public Product(String name, String description)
25       {
26           this.Name = name;
27           this.Description = description;
28       }
29
30       /// <summary>
31       /// Product name.
32       /// </summary>
33       public String Name { get; set; }
34
35       /// <summary>
36       /// Product description.
37       /// </summary>
38       public String Description { get; set; }
39
40       public void Save()
41       {
42           //SQL query to save the product to database.
43           //Here is you use linq you can make sure that
44           //the product does not exist and if it does you
45           // can throw and new Exception.
46       }
47   }
48
49   public static class ProductHelper
50   {
51       public static List GetAllProducts()
52       {
53           List list = new List();
54
55           //Query the database and using the _SqlDataReader_ populate the list or a _linq_ to sql query that will make it much more easy.
56
57           return list;
58       }
59   }

Comments? Questions?

Simon

Re: How To: Connect to SQL using C# and SELECT INSERT UPDATE ?

Ranganayaki — Fri, 13 Feb 2009 05:00:11 GMT

Hi Simon,

I'm new to c# can you tell me how can i improve my programming logic.

I have a product table in which product id is auto-generated using stored procedure,

while inserting product name and description i need to check whether product already exist?if so

if present and active return 0 else if present and deleted return 1,if not present return 2

how can i do it please help me.Take Care

Re: How To: Connect to SQL using C# and SELECT INSERT UPDATE ?

Simon Deshaies — Sun, 16 Nov 2008 17:52:48 GMT

I rewrote this class into one that implement the singleton pattern and it now closes itself. Read: http://weblog.simondeshaies.net/post/2008/11/How-To-Connect-to-SQL-Server-with-C-and-Optimize-Reusability.aspx

Re: How To: Connect to SQL using C# and SELECT INSERT UPDATE ?

Simon Deshaies — Sat, 04 Oct 2008 16:07:07 GMT

There are a couple way you could do this. I like to use a WHILE.

MySqlConnection MySql = new MySqlConnection();
MySql.CreateConn();
MySql.Command.CommandText = "SELECT * FROM dbo.[Product] )";
MySql.DataReader = MySql.Command.ExecuteReader();
while (MySql.DataReader.Read())
{
NameLablel = Convert.ToString(MySql.DataReader[0]);
//etc....
}
MySql.DataReader.Close();
MySql.CloseConn();

Or you can use a repeater in the aspx page. And set a DataTable as it's DataSource.

ASPX:
<asp:Repeater ID="linkRepeater" runat="server">
<HeaderTemplate>
<ul>
</HeaderTemplate>
<ItemTemplate>
<li>
<a href='#<%# Eval("faqID") %>'><%# Eval("faqQuestion") %></a>
</li>
</ItemTemplate>
<FooterTemplate>
</ul>
</FooterTemplate>
</asp:Repeater>

C#:
MySqlConnection MySql = new MySqlConnection();
DataTable datatable = new DataTable();
mySql.CreateConn();
SqlDataAdapter adapter = new SqlDataAdapter("SELECT * FROM dbo.faq " + Name + " ORDER BY faqSubject, faqQuestion", mySql.Connection);
adapter.Fill(datatable);
mySql.CloseConn();
linkRepeater.DataSource = datatable;
linkRepeater.DataBind();