Database

This May Be A Stupid Question But... [modified] Found It!

31-Jul-10 2:19

T.RATHA KRISHNAN wrote:
I"ve to add values to a field

A single field has a single value. The UPDATE-command would replace the current value of a field with a new value.

I are Troll Suspicious | :suss:

Roger Wright30-Jul-10 20:14

30-Jul-10 20:14

I'll ask it anyway, having already tried Google and MSDN.

I'm fiddling with a SQL Server app to track substation equipment - one of several projects I'm working on. I've got a skeleton working on a local instance of SQL Server Express, but I'm thinking ahead to the day I deploy this on the company server, which has the full version of SQL Server installed. I don't want to manually recreate the database on the server - that way madness lies - and I don't want to Export/Import my existing database, as it's full of test cases I don't want to have to delete. I'm sure there's a nice, efficient way to create a database in SQL Server programmatically when my app runs for the first time, but I haven't a clue how to do it. Google returns a plethora of responses, enough to completely confuse me, and MSDN returns none of any relevance - the usual ratio.

Can someone point me to a tutorial of some sort that my aged brain can comprehend?

[EDIT]

While MSDN didn't help, I finally asked the right question in Google - which led straight back to Microsoft, of course:

using System;
using System.Data.SqlClient;
    String str;
    SqlConnection myConn = new SqlConnection ("Server=localhost;Integrated               security=SSPI;database=master");

    str = "CREATE DATABASE MyDatabase ON PRIMARY " +
        "(NAME = MyDatabase_Data, " +
        "FILENAME = 'C:\\MyDatabaseData.mdf', " +
        "SIZE = 2MB, MAXSIZE = 10MB, FILEGROWTH = 10%) " +
        "LOG ON (NAME = MyDatabase_Log, " +
        "FILENAME = 'C:\\MyDatabaseLog.ldf', " +
        "SIZE = 1MB, " +
        "MAXSIZE = 5MB, " +
        "FILEGROWTH = 10%)";

    SqlCommand myCommand = new SqlCommand(str, myConn);
    try 
    {
        myConn.Open();
	myCommand.ExecuteNonQuery();
	MessageBox.Show("DataBase is Created Successfully", "MyProgram", MessageBoxButtons.OK,    MessageBoxIcon.Information);
    }
    catch (System.Exception ex)
    {
	MessageBox.Show(ex.ToString(), "MyProgram", MessageBoxButtons.OK, MessageBoxIcon.Information);
    }
    finally
    {
	if (myConn.State == ConnectionState.Open)
	{
	    myConn.Close();
	}
    }

Sorry about the formatting - that's Opera at work...

The article further states that, if I want just to use the Model database, eliminate all the crud and use simply, str = "CREATE DATABASE MyDatabase".

I haven't tried it yet as it's getting rather late, but I think I'll play a bit later this week. I presume that I can then use CREATE TABLE and all its sundry relatives to fill out the details once this part works, and I've already found a way to enumerate the available SQL Server instances, which will allow the end user to decide which server to host the app on.

Assuming that this works as advertised, and since it doesn't seem to be common knowledge, I suspect that it will make a decent article or tip. Big Grin | :-D

By the way, the article is located at: support.microsoft.com[^]

[/EDIT] We now return you to our regular programming...

"A Journey of a Thousand Rest Stops Begins with a Single Movement"

modified on Wednesday, August 4, 2010 1:47 AM

Mycroft Holmes30-Jul-10 21:30

Mycroft Holmes

30-Jul-10 21:30

Roger Wright wrote:
I'm sure there's a nice, efficient way to create a database in SQL Server

Not programatically but there are 2 very simple ways to move a copy of your database to the production server. You can either backup your express version and restore it to the server or take a copy of the .mdf and .ldf files to the server and attach them. I think the attach one has the benefit of bringing the diagram which the backup/restore misses. Both are VERY simple processes.

Never underestimate the power of human stupidity
RAH

Roger Wright2-Aug-10 17:20

2-Aug-10 17:20

Have you ever tried coding this[^] into a SqlCommand? I think I may give it a try...

"A Journey of a Thousand Rest Stops Begins with a Single Movement"

Peter_in_278030-Jul-10 21:58

Peter_in_2780

30-Jul-10 21:58

I haven't done it in your kind of environment, but the following approach has worked for me elsewhere:

connect_to_server();
if (error_db_not_exist == open_database(my_db))
  {
  read_from_file(canned_sql);
  run_query(canned_sql); // maybe multiple queries to run
  if (success != open_database(my_db))
    // oh, @#$%^&*!
  }

where canned_sql is the appropriate set of creates, etc. Hopefully you can get this by querying system tables on your test server. An alternative is to export your test db, then delete all the *data* from that, leaving just the "structure".
HTH
Peter

Software rusts. Simon Stephenson, ca 1994.

Jörgen Andersson30-Jul-10 23:40

Jörgen Andersson

30-Jul-10 23:40

It sounds to me that it's something similar to this[^] you need.

It creates a script for recreating a database on another server.

"When did ignorance become a point of view" - Dilbert

Roger Wright31-Jul-10 4:34

31-Jul-10 4:34

That's worth a look, Jorgen - Thanks!

It just seems silly to me that I can't export my database to a XML file, then regenerate the database on the target server using that file. I've used db-based product before that create the database on installation, but I assumed there was a CREATE DATABASE function, in addition to the CREATE TABLE function used regularly. It never occurred to me that they were scripting a copy and import process in the installer.

"A Journey of a Thousand Rest Stops Begins with a Single Movement"

PIEBALDconsult31-Jul-10 10:22

PIEBALDconsult

31-Jul-10 10:22

I agree with Mycroft. I detach the database, copy the files, and reattach.

Corporal Agarn2-Aug-10 9:55

Corporal Agarn

2-Aug-10 9:55

It sounds like scripting is the best way as you do not want everything to go. However I do not know if Express has scripting.

Another way would be to 1) backup the database 2) truncate tables 3) backup to second file then restore the second backup to new server.

Roger Wright2-Aug-10 17:18

2-Aug-10 17:18

Actually, I found this[^] to be intriguing. I wonder if I can code this into a SqlCommand? Big Grin | :-D

"A Journey of a Thousand Rest Stops Begins with a Single Movement"

Roger Wright3-Aug-10 20:16

3-Aug-10 20:16

I found the solution at Microsoft Support, not MSDN. Big Grin | :-D

"A Journey of a Thousand Rest Stops Begins with a Single Movement"

query takes long time!

Jassim Rahma30-Jul-10 4:34

Jassim Rahma

30-Jul-10 4:34

I am using this code on SQL Server Expess as stored procedure. The icd_disease has 12,000 records but with LEFT(icd_code, 1) = 'R' it’s just 400 records. Then why it takes more than 40seconds to bind into grid control although I ave already indexed the icd_code and icd_description field

SELECT icd_diseases.icd_id, icd_diseases.icd_code, icd_diseases.icd_description
FROM icd_diseases 
WHERE LEFT(icd_code, 1) = 'R'
ORDER BY icd_description, icd_code

This is my binding code

data_table = new DataTable();

sql_connection = new SqlConnection((string)public_var._system_parameters_hash["SQL_SERVER_CONNECTION"]);

sql_connection.Open();
sql_command = new SqlCommand("sp_get_all_symptoms", sql_connection);
sql_command.CommandType = CommandType.StoredProcedure;
sql_adapter = new SqlDataAdapter(sql_command);
sql_adapter.Fill(data_table);
dataSymptoms.DataSource = null;
dataSymptoms.ResetBindings();
dataSymptoms.DataSource = data_table;

gridSymptoms.Columns["icd_id"].Caption = "icd_id#";
gridSymptoms.Columns["icd_id"].AppearanceHeader.TextOptions.HAlignment = DevExpress.Utils.HorzAlignment.Center;
gridSymptoms.Columns["icd_id"].AppearanceCell.TextOptions.HAlignment = DevExpress.Utils.HorzAlignment.Center;
gridSymptoms.Columns["icd_id"].Visible = false;

gridSymptoms.Columns["icd_code"].Caption = "icd_code#";
gridSymptoms.Columns["icd_code"].AppearanceHeader.TextOptions.HAlignment = DevExpress.Utils.HorzAlignment.Center;
gridSymptoms.Columns["icd_code"].AppearanceCell.TextOptions.HAlignment = DevExpress.Utils.HorzAlignment.Center;
gridSymptoms.Columns["icd_code"].Visible = false;

gridSymptoms.Columns["icd_description"].Caption = "Symptom";
gridSymptoms.Columns["icd_description"].AppearanceHeader.TextOptions.HAlignment = DevExpress.Utils.HorzAlignment.Default;
gridSymptoms.Columns["icd_description"].AppearanceCell.TextOptions.HAlignment = DevExpress.Utils.HorzAlignment.Default;
gridSymptoms.Columns["icd_description"].Width = 420;

dan!sh 30-Jul-10 4:49

dan!sh

30-Jul-10 4:49

Figure out what takes more time, the query or the code. If it is the query, take a look at execution plan. Might help.

Eddy Vluggen30-Jul-10 5:07

30-Jul-10 5:07

SQL

WHERE LEFT(icd_code, 1) = 'R'

Doesn't look very efficient. I'd add an extra (redundant) column that holds the first character that you're filtering on. And put an index on that field Smile | :)

I are Troll Suspicious | :suss:

Jassim Rahma30-Jul-10 5:43

Jassim Rahma

30-Jul-10 5:43

I am sure there is something wrong because running a query to get all the 12000 on the same PC but using SSMS takes 1sec only! The issue is when I run it on winform either to get all or to get starting with R so no creating another col holding the first character won't help when I want to get all

Eddy Vluggen30-Jul-10 6:04

30-Jul-10 6:04

jrahma wrote:
when I want to get all

sp_get_all_symptoms

jrahma wrote:
I am sure there is something wrong because running a query to get all the 12000 on the same PC but using SSMS takes 1sec only!

I don't think that SSMS is using a datatable to cache the entire table locally. Might be using a reader, filling your grids with results as they arrive over TCP.

If all the ICD-codes need to be on the client, why not put them on the clientmachine as a SqlCe database?

I are Troll Suspicious | :suss:

David Skelly30-Jul-10 6:12

David Skelly

30-Jul-10 6:12

Why not just do this?

WHERE icd_code LIKE 'R%'

Isn't that the same thing as LEFT(icd_code, 1)? I don't know whether the query optimiser is smart enough to reduce the two queries down to the same thing or not. Using LIKE would definitely result in the optimiser considering using an index on icd_code if there is one. I don't know whether the query optimiser considers using an index for LEFT or not.

Eddy Vluggen30-Jul-10 6:29

30-Jul-10 6:29

David Skelly wrote:
Why not just do this?

Would be an option too, even cleaner to read. Another (a bit more obfuscating perhaps) alternative would be a computed column, including an index;

SQL

BEGIN TRANSACTION
CREATE TABLE Test
(
    ICDKEY  VARCHAR(8)
   ,ICDVAL  VARCHAR(5000)
   
   ,KEYPFX AS LEFT(ICDKEY, 1)
)

CREATE INDEX Test_KeyPfx_Idx ON Test (KEYPFX)

ROLLBACK

Then again, if I have the option of doing the processing ahead, I'd surely choose to add a readonly-column and do a single update-statement.

Mean part of that query is that it's dragging along a large text-field.

I are Troll Suspicious | :suss: