Try to store the data into sqlserver

Question

i want to store the data in sqlserver and i'm able with this code.Now i want to check if table is exists than insert the data or create new table and insert the data..So i need help...thnku

SqlConnection con = new SqlConnection("Data Source=PRAWAT; Initial Catalog=StudentData ;Integrated security=true; ");

string query = "insert into NewValidStudentData(StudentId,Name,Marks) values (@StudentId,@Name,@Marks);";

SqlCommand cmd = new SqlCommand();
SqlDataAdapter da = new SqlDataAdapter();
da.InsertCommand = new SqlCommand(query, con);

con.Open();

da.InsertCommand.Parameters.Clear();
da.InsertCommand.Parameters.AddWithValue("@StudentId", System.Data.SqlDbType.NVarChar).Value = value[0];
da.InsertCommand.Parameters.AddWithValue("@Name", System.Data.SqlDbType.NVarChar).Value = value[1];
da.InsertCommand.Parameters.AddWithValue("@Marks", System.Data.SqlDbType.NVarChar).Value = value[2];

da.InsertCommand.ExecuteNonQuery();

con.Close();

Have you looked at the error when you run this against a database that doesn't have this table? — JeffO, Nov 17 '14 at 15:41
wrap in try/catch/create table. Or on application start up query information_schema.tables to find out if you need to apply any migrations. Or read up on migrations, which is a feature of many frameworks, which does much the same thing. — MatthewMartin, Nov 17 '14 at 15:43
Better to do your SQL in stored procedures. If you need to make changes to your SQL you modify the stored proc, not your C# application. — Rick S, Nov 17 '14 at 15:43
If you're not using an ORM model, I think it makes sense to put the logic in a [Stored Procedure](http://technet.microsoft.com/en-us/library/aa174792(v=sql.80).aspx) and call it from your code. Inside the procedure you can check to see if the table exists or not. — Arian Motamedi, Nov 17 '14 at 15:46
Imho the code handling your actual data should not be dealing with the database schema. If you are creating new tables while processing data there's something wrong in your design unless your application is some sort of meta-application like a Content Management System. — Filburt, Nov 17 '14 at 15:46

score 0 · Answer 1 · answered Nov 17 '14 at 15:44

You can edit your query to something like:

IF (EXISTS 
   (SELECT * FROM INFORMATION_SCHEMA.TABLES 
    WHERE TABLE_SCHEMA = 'YourSchemaName'// if you don't know the name, try 'dbo'
    AND  TABLE_NAME = 'NewValidStudentData'))
BEGIN
    INSERT INTO NewValidStudentData(StudentId, Name, Marks) 
    VALUES (@StudentId, @Name, @Marks);";
END

Just wrap this query as a string and execute the same. This way, you can control the table's existence and insert the data within a single call to database server.

score 0 · Answer 2 · edited May 23 '17 at 12:12

First check if your table exists with this snippet (also see this answer):

bool exists;
string tableName = "WhatEverItIs";

try {
    // ANSI SQL way.  Works in PostgreSQL, MSSQL, MySQL.  
    var cmd = new OdbcCommand(
      "select case when exists((select * from information_schema.tables where table_name = '" + tableName + "')) then 1 else 0 end");

    exists = (int)cmd.ExecuteScalar() == 1;
} catch {
    try {
        // Other RDBMS.  Graceful degradation
        exists = true;
        var cmdOthers = new OdbcCommand("select 1 from " + tableName + " where 1 = 0");
        cmdOthers.ExecuteNonQuery();
    } catch {
        exists = false;
    }
}

Then if it wasn't exist:

if(!exists) {
    var createTableSql = "CREATE TABLE WHAT_YOUR_TABLE_SCHEME_IS";
    // execute a command with above createTableSql, to create your table
}

And then, do the rest of your code

score 0 · Answer 3 · answered Nov 17 '14 at 15:49

StudentId as NVARCHAR? Does Student Id has characters in it?

IF  NOT EXISTS (SELECT * FROM sys.objects 
WHERE object_id = OBJECT_ID(N'[dbo].[NewValidStudentData]') AND type in (N'U'))

BEGIN
CREATE TABLE [dbo].[NewValidStudentData](
StudentId NVARCHAR(10),
Name NVARCHAR(100),
Marks NVARCHAR(3)
) 

END

Note: I would suggest handle this in stored procedure instead of writing all this in c# code.

Try to store the data into sqlserver

3 Answers3