Evo to primer klase kojom mozes da se konektujes na tri baze: MS Sql Server, Sql Server Compact Edition i Access.
Code:
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.Common;
using System.Data.OleDb;
using System.Data.SqlClient;
using System.Data.SqlServerCe;
using System.Drawing;
using System.Text;
using System.Windows.Forms;
namespace WindowsApplication1
{
public sealed class SQL
{
public static string CreateConnectionString(string server, string username, string password, string database)
{
int timeout = 10;
server = (server != null && server.Length > 0 ? "Data Source=" + server + ";" : "Data Source=(local);");
string login = "Integrated Security=SSPI;";
if(username != null && username.Length > 0) {
if(password != null && password.Length > 0)
login = "User ID=" + username + ";Password=" + password + ";";
else
login = "User ID=" + username + ";Password=\"\";";
}
string catalog = "Initial Catalog=" + (database != null && database.Length > 0 ? database : "master");
return server + login + catalog + (timeout > 0 ? ";Connect Timeout=10" : "");
}
public static string CreateConnectionString(string fileName, string password)
{
if(!string.IsNullOrEmpty(fileName)) {
string cs = "Data Source=" + fileName;
if(password != null && password.Length > 0) cs += ";Password=" + password + ";";
return cs;
}
return "";
}
public static ConnectionStringType GetConnectionStringType(string connectionString)
{
if(!string.IsNullOrEmpty(connectionString)) {
string connectionStringLower = connectionString.ToLower();
if(connectionStringLower.Contains(".sdf")) return ConnectionStringType.SqlServerCe;
if(connectionStringLower.Contains(".mdb")) return ConnectionStringType.Access;
return ConnectionStringType.SqlServer;
}
return ConnectionStringType.None;
}
public static DbConnection GetConnection(string connectionString)
{
switch(GetConnectionStringType(connectionString)) {
case ConnectionStringType.SqlServer:
return new SqlConnection(connectionString);
case ConnectionStringType.SqlServerCe:
return new SqlCeConnection(connectionString);
case ConnectionStringType.Access:
return new OleDbConnection(connectionString);
}
return null;
}
public static DbDataAdapter GetDataAdapter(string connectionString, string query)
{
switch(GetConnectionStringType(connectionString)) {
case ConnectionStringType.SqlServer:
return new SqlDataAdapter(query, connectionString);
case ConnectionStringType.SqlServerCe:
return new SqlCeDataAdapter(query, connectionString);
case ConnectionStringType.Access:
return new OleDbDataAdapter(query, connectionString);
}
return null;
}
public static DbCommand GetCommand(string connectionString, string query)
{
DbConnection connection = GetConnection(connectionString);
if(connection != null) {
switch(GetConnectionStringType(connectionString)) {
case ConnectionStringType.SqlServer:
return new SqlCommand(query, connection as SqlConnection);
case ConnectionStringType.SqlServerCe:
return new SqlCeCommand(query, connection as SqlCeConnection);
case ConnectionStringType.Access:
return new OleDbCommand(query, connection as OleDbConnection);
}
}
return null;
}
public static DataTable ExecuteQuery(string connectionString, string query)
{
DataTable dataTable = new DataTable();
DbDataAdapter adapter = null;
try {
adapter = GetDataAdapter(connectionString, query);
if(adapter != null) adapter.Fill(dataTable);
} catch(Exception ex) {
MessageBox.Show(ex.Message, "", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
if(adapter != null) try { adapter.Dispose(); } catch { }
return dataTable;
}
public static int ExecuteNonQuery(string connectionString, string statement)
{
if(!string.IsNullOrEmpty(statement)) {
DbConnection con = null;
DbCommand cmd = null;
int result = -1;
try {
con = GetConnection(connectionString);
con.Open();
cmd = con.CreateCommand();
cmd.CommandText = statement;
result = cmd.ExecuteNonQuery();
} catch(Exception ex) {
MessageBox.Show(ex.Message, "", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
if(cmd != null) try { cmd.Dispose(); } catch { }
if(con != null) try { con.Close(); con.Dispose(); } catch { }
return result;
}
return 0;
}
public static void DisplayDataTable(DataTable table)
{
Form form = new Form();
DataGridView grid = new DataGridView();
grid.DataSource = table;
grid.Dock = DockStyle.Fill;
form.Controls.Add(grid);
form.Size = new Size(800, 600);
form.StartPosition = FormStartPosition.CenterScreen;
form.ShowDialog();
grid.Dispose();
form.Dispose();
}
}
public enum ConnectionStringType
{
None,
SqlServer,
SqlServerCe,
Access
}
}
Evo ti primer konektovanja na Sql Server.
Code:
string connectionString = SQL.CreateConnectionString("", "", "", "PROBA");
DataTable dataTable = SQL.ExecuteQuery(connectionString, "select * from dbo.sysobjects");
SQL.DisplayDataTable(dataTable);
Nadam se da ce ti pomoci.
Pozdrav.