2 Questions about ADO.net

  • Thread starter Thread starter Tom
  • Start date Start date
T

Tom

1. I drag the sqlDataAdapter and sqlConnection from the
tool bar to the web form.

It creates automatically:

Code: 
protected System.Data.SqlClient.SqlDataAdapter
sqlDataAdapter1;
protected System.Data.SqlClient.SqlCommand
sqlSelectCommand1;
protected System.Data.SqlClient.SqlCommand
sqlInsertCommand1;
protected System.Data.SqlClient.SqlCommand
sqlUpdateCommand1;
protected System.Data.SqlClient.SqlCommand
sqlDeleteCommand1;
protected System.Data.SqlClient.SqlConnection
sqlConnection1;

override protected void OnInit(EventArgs e)
{
InitializeComponent();
base.OnInit(e);
}

private void InitializeComponent()
{
this.sqlDataAdapter1 = new
System.Data.SqlClient.SqlDataAdapter();
this.sqlSelectCommand1 = new
System.Data.SqlClient.SqlCommand();
this.sqlInsertCommand1 = new
System.Data.SqlClient.SqlCommand();
this.sqlUpdateCommand1 = new
System.Data.SqlClient.SqlCommand();
this.sqlDeleteCommand1 = new
System.Data.SqlClient.SqlCommand();
this.sqlConnection1 = new
System.Data.SqlClient.SqlConnection();
this.Button1.Click += new System.EventHandler
(this.Button1_Click);

this.sqlDataAdapter1.DeleteCommand =
this.sqlDeleteCommand1;
this.sqlDataAdapter1.InsertCommand =
this.sqlInsertCommand1;
this.sqlDataAdapter1.SelectCommand =
this.sqlSelectCommand1;
this.sqlDataAdapter1.TableMappings.AddRange(new
System.Data.Common.DataTableMapping[] {
new System.Data.Common.DataTableMapping
("Table", "phonebook", new
System.Data.Common.DataColumnMapping[] {
new System.Data.Common.DataColumnMapping
("id", "id"),
new System.Data.Common.DataColumnMapping
("name", "name"),
new System.Data.Common.DataColumnMapping
("phone", "phone"),

this.sqlDataAdapter1.UpdateCommand =
this.sqlUpdateCommand1;

this.sqlSelectCommand1.CommandText = "SELECT id,
name, phone FROM dbo.phonebook";
this.sqlSelectCommand1.Connection =
this.sqlConnection1;

this.sqlInsertCommand1.CommandText = "INSERT INTO
dbo.phonebook(name, phone) VALUES (@name, @phone); SELECT
id, name, phone FROM dbo.phonebook WHERE (id =
@@IDENTITY)";

this.sqlInsertCommand1.Connection =
this.sqlConnection1;
this.sqlInsertCommand1.Parameters.Add(new
System.Data.SqlClient.SqlParameter("@name",
System.Data.SqlDbType.NVarChar, 20, "name"));
this.sqlInsertCommand1.Parameters.Add(new
System.Data.SqlClient.SqlParameter("@phone",
System.Data.SqlDbType.NVarChar, 10, "phone"));

this.sqlUpdateCommand1.CommandText = @"UPDATE
dbo.phonebook SET name = @name, phone = @phone WHERE (id =
@Original_id) AND (phone = @Original_phone); SELECT id,
name, phone FROM dbo.phonebook WHERE (id = @id)";

this.sqlUpdateCommand1.Connection =
this.sqlConnection1;
this.sqlUpdateCommand1.Parameters.Add(new
System.Data.SqlClient.SqlParameter("@name",
System.Data.SqlDbType.NVarChar, 20, "name"));
this.sqlUpdateCommand1.Parameters.Add(new
System.Data.SqlClient.SqlParameter("@phone",
System.Data.SqlDbType.NVarChar, 10, "phone"));

this.sqlUpdateCommand1.Parameters.Add(new
System.Data.SqlClient.SqlParameter("@Original_id",
System.Data.SqlDbType.Int, 4,
System.Data.ParameterDirection.Input, false, ((System.Byte)
(0)), ((System.Byte)(0)), "id",
System.Data.DataRowVersion.Original, null));

this.sqlUpdateCommand1.Parameters.Add(new
System.Data.SqlClient.SqlParameter("@Original_phone",
System.Data.SqlDbType.NVarChar, 160,
System.Data.ParameterDirection.Input, false, ((System.Byte)
(0)), ((System.Byte)(0)), "phone",
System.Data.DataRowVersion.Original, null));
this.sqlUpdateCommand1.Parameters.Add(new
System.Data.SqlClient.SqlParameter("@Original_name",
System.Data.SqlDbType.NVarChar, 1000,
System.Data.ParameterDirection.Input, false, ((System.Byte)
(0)), ((System.Byte)(0)), "name",
System.Data.DataRowVersion.Original, null));
this.sqlUpdateCommand1.Parameters.Add(new
System.Data.SqlClient.SqlParameter("@id",
System.Data.SqlDbType.Int, 4, "id"));


this.sqlDeleteCommand1.CommandText = "DELETE FROM
dbo.phonebook WHERE (id = @Original_id) AND (phone =
@Original_phone)";
this.sqlDeleteCommand1.Connection =
this.sqlConnection1;
this.sqlDeleteCommand1.Parameters.Add(new
System.Data.SqlClient.SqlParameter("@Original_id",
System.Data.SqlDbType.Int, 4,
System.Data.ParameterDirection.Input, false, ((System.Byte)
(0)), ((System.Byte)(0)), "id",
System.Data.DataRowVersion.Original, null));
this.sqlDeleteCommand1.Parameters.Add(new
System.Data.SqlClient.SqlParameter("@Original_phone",
System.Data.SqlDbType.NVarChar, 160,
System.Data.ParameterDirection.Input, false, ((System.Byte)
(0)), ((System.Byte)(0)), "phone",
System.Data.DataRowVersion.Original, null));
this.sqlDeleteCommand1.Parameters.Add(new
System.Data.SqlClient.SqlParameter("@Original_name",
System.Data.SqlDbType.NVarChar, 1000,
System.Data.ParameterDirection.Input, false, ((System.Byte)
(0)), ((System.Byte)(0)), "name",
System.Data.DataRowVersion.Original, null));

this.sqlConnection1.ConnectionString = "data
source=SERVER;initial catalog=phonedb;persist security
info=False;user id=phoneuser;password:useruser;workstation
id=SERVER;packet size=4096";
this.Load += new System.EventHandler
(this.Page_Load);
}

private void Button1_Click(object sender, System.EventArgs
e)
{
}

Although insert, delete, update and select sql statements
were done automatically, however, how can i make use of
them? i.e. how can I call them to use under Button1_Click?

2. You can see the this.sqlConnection1.ConnectionString
= "data source=SERVER;initial catalog=phonedb;persist
security info=False;user
id=phoneuser;password:useruser;workstation
id=SERVER;packet size=4096";

It has to include the login id and password in order to
make such connection. I guess it is not secure. How should
I write a secure dbconnection code? Am I better to write a
new DBConnect class?

If I use my DBConnect class, does the above auto-generated
code not useful?

Thanks for advice.
 
Tom,

In order to call the data adatper in your click event handler, you can
just call to sqlDataAdapter1. It's a member of your class, like any other
member. So, if you wanted to fill a dataset with the results from the
query, you would do:

// Fill the data set.
DataSet pobjDataSet = new DataSet();
sqlDataAdapter1.Fill(pobjDataSet);

As for creating a new connection class, that's not necessary. You have
one of two options here. The first is to store the connection information
in another location which is more secure. The second is to use trusted
connections (windows security) and not have to worry about logging in at
all.

Hope this helps.


--
- Nicholas Paldino [.NET/C# MVP]
- (e-mail address removed)

Tom said:
1. I drag the sqlDataAdapter and sqlConnection from the
tool bar to the web form.

It creates automatically:

Code: 
protected System.Data.SqlClient.SqlDataAdapter
sqlDataAdapter1;
protected System.Data.SqlClient.SqlCommand
sqlSelectCommand1;
protected System.Data.SqlClient.SqlCommand
sqlInsertCommand1;
protected System.Data.SqlClient.SqlCommand
sqlUpdateCommand1;
protected System.Data.SqlClient.SqlCommand
sqlDeleteCommand1;
protected System.Data.SqlClient.SqlConnection
sqlConnection1;

override protected void OnInit(EventArgs e)
{
InitializeComponent();
base.OnInit(e);
}

private void InitializeComponent()
{
this.sqlDataAdapter1 = new
System.Data.SqlClient.SqlDataAdapter();
this.sqlSelectCommand1 = new
System.Data.SqlClient.SqlCommand();
this.sqlInsertCommand1 = new
System.Data.SqlClient.SqlCommand();
this.sqlUpdateCommand1 = new
System.Data.SqlClient.SqlCommand();
this.sqlDeleteCommand1 = new
System.Data.SqlClient.SqlCommand();
this.sqlConnection1 = new
System.Data.SqlClient.SqlConnection();
this.Button1.Click += new System.EventHandler
(this.Button1_Click);

this.sqlDataAdapter1.DeleteCommand =
this.sqlDeleteCommand1;
this.sqlDataAdapter1.InsertCommand =
this.sqlInsertCommand1;
this.sqlDataAdapter1.SelectCommand =
this.sqlSelectCommand1;
this.sqlDataAdapter1.TableMappings.AddRange(new
System.Data.Common.DataTableMapping[] {
new System.Data.Common.DataTableMapping
("Table", "phonebook", new
System.Data.Common.DataColumnMapping[] {
new System.Data.Common.DataColumnMapping
("id", "id"),
new System.Data.Common.DataColumnMapping
("name", "name"),
new System.Data.Common.DataColumnMapping
("phone", "phone"),

this.sqlDataAdapter1.UpdateCommand =
this.sqlUpdateCommand1;

this.sqlSelectCommand1.CommandText = "SELECT id,
name, phone FROM dbo.phonebook";
this.sqlSelectCommand1.Connection =
this.sqlConnection1;

this.sqlInsertCommand1.CommandText = "INSERT INTO
dbo.phonebook(name, phone) VALUES (@name, @phone); SELECT
id, name, phone FROM dbo.phonebook WHERE (id =
@@IDENTITY)";

this.sqlInsertCommand1.Connection =
this.sqlConnection1;
this.sqlInsertCommand1.Parameters.Add(new
System.Data.SqlClient.SqlParameter("@name",
System.Data.SqlDbType.NVarChar, 20, "name"));
this.sqlInsertCommand1.Parameters.Add(new
System.Data.SqlClient.SqlParameter("@phone",
System.Data.SqlDbType.NVarChar, 10, "phone"));

this.sqlUpdateCommand1.CommandText = @"UPDATE
dbo.phonebook SET name = @name, phone = @phone WHERE (id =
@Original_id) AND (phone = @Original_phone); SELECT id,
name, phone FROM dbo.phonebook WHERE (id = @id)";

this.sqlUpdateCommand1.Connection =
this.sqlConnection1;
this.sqlUpdateCommand1.Parameters.Add(new
System.Data.SqlClient.SqlParameter("@name",
System.Data.SqlDbType.NVarChar, 20, "name"));
this.sqlUpdateCommand1.Parameters.Add(new
System.Data.SqlClient.SqlParameter("@phone",
System.Data.SqlDbType.NVarChar, 10, "phone"));

this.sqlUpdateCommand1.Parameters.Add(new
System.Data.SqlClient.SqlParameter("@Original_id",
System.Data.SqlDbType.Int, 4,
System.Data.ParameterDirection.Input, false, ((System.Byte)
(0)), ((System.Byte)(0)), "id",
System.Data.DataRowVersion.Original, null));

this.sqlUpdateCommand1.Parameters.Add(new
System.Data.SqlClient.SqlParameter("@Original_phone",
System.Data.SqlDbType.NVarChar, 160,
System.Data.ParameterDirection.Input, false, ((System.Byte)
(0)), ((System.Byte)(0)), "phone",
System.Data.DataRowVersion.Original, null));
this.sqlUpdateCommand1.Parameters.Add(new
System.Data.SqlClient.SqlParameter("@Original_name",
System.Data.SqlDbType.NVarChar, 1000,
System.Data.ParameterDirection.Input, false, ((System.Byte)
(0)), ((System.Byte)(0)), "name",
System.Data.DataRowVersion.Original, null));
this.sqlUpdateCommand1.Parameters.Add(new
System.Data.SqlClient.SqlParameter("@id",
System.Data.SqlDbType.Int, 4, "id"));


this.sqlDeleteCommand1.CommandText = "DELETE FROM
dbo.phonebook WHERE (id = @Original_id) AND (phone =
@Original_phone)";
this.sqlDeleteCommand1.Connection =
this.sqlConnection1;
this.sqlDeleteCommand1.Parameters.Add(new
System.Data.SqlClient.SqlParameter("@Original_id",
System.Data.SqlDbType.Int, 4,
System.Data.ParameterDirection.Input, false, ((System.Byte)
(0)), ((System.Byte)(0)), "id",
System.Data.DataRowVersion.Original, null));
this.sqlDeleteCommand1.Parameters.Add(new
System.Data.SqlClient.SqlParameter("@Original_phone",
System.Data.SqlDbType.NVarChar, 160,
System.Data.ParameterDirection.Input, false, ((System.Byte)
(0)), ((System.Byte)(0)), "phone",
System.Data.DataRowVersion.Original, null));
this.sqlDeleteCommand1.Parameters.Add(new
System.Data.SqlClient.SqlParameter("@Original_name",
System.Data.SqlDbType.NVarChar, 1000,
System.Data.ParameterDirection.Input, false, ((System.Byte)
(0)), ((System.Byte)(0)), "name",
System.Data.DataRowVersion.Original, null));

this.sqlConnection1.ConnectionString = "data
source=SERVER;initial catalog=phonedb;persist security
info=False;user id=phoneuser;password:useruser;workstation
id=SERVER;packet size=4096";
this.Load += new System.EventHandler
(this.Page_Load);
}

private void Button1_Click(object sender, System.EventArgs
e)
{
}

Although insert, delete, update and select sql statements
were done automatically, however, how can i make use of
them? i.e. how can I call them to use under Button1_Click?

2. You can see the this.sqlConnection1.ConnectionString
= "data source=SERVER;initial catalog=phonedb;persist
security info=False;user
id=phoneuser;password:useruser;workstation
id=SERVER;packet size=4096";

It has to include the login id and password in order to
make such connection. I guess it is not secure. How should
I write a secure dbconnection code? Am I better to write a
new DBConnect class?

If I use my DBConnect class, does the above auto-generated
code not useful?

Thanks for advice.
Click to expand...
 
Dear Sir,

Thank you for your kind help. Since I am a newbie and just
read some ASP.net and C# books. I am not still familiar
with it.

Here are my questions:

1. Do I write the code like that?

private void Button1_Click(object sender, System.EventArgs
e)
{
DataSet pobjDataSet = new DataSet();
sqlDataAdapter1.Fill(pobjDataSet);
}

But, how can I call the insert command? I need to input
the textbox to the DB.

2. The first is to store the connection information in
another location which is more secure.

Do you mean store it in the registry? How can I call it
from the code after I save it in registry?

The second is to use trusted connections (windows
security) and not have to worry about logging in at
all.

If I use windows security (I guess it is better), how
should I write the following?

this.sqlConnection1.ConnectionString = "data
source=SERVER;initial catalog=phonedb;persist security
info=False;user id=phoneuser;password:useruser;workstation
id=SERVER;packet size=4096";

Regards

Tom
 
Back
Top