Voici un bout de code sympa pour vous qui comme moi developpez des appli puis parfois se pose le probleme de copier certaines données d'une base a une autres a travers un script sql (install par exemple).
Ce code vous affiche les tables sous une base et vous pouvez cocher puis generer les requetes insert pour les colonnes .
Essayez plus tot
PS: je n'ai pas teste comment ca reagirait fasse a une table qui a un champ image.
Copy past the code into a winform under c#. ( i speak more often english than french, sorry)
Source / Exemple :
using System;
using System.Drawing;
using System.Collections;
using System.ComponentModel;
using System.Windows.Forms;
using System.Data;
using System.Data.SqlClient;
using System.Data.Common;
using System.Data.SqlTypes;
using System.Text;
using System.IO;
namespace InsertGenerator
{
/// <summary>
/// Summary description for Form1.
/// </summary>
public class Form1 : System.Windows.Forms.Form
{
private System.Windows.Forms.CheckedListBox checkedCols;
private System.Windows.Forms.Label label1;
private System.Windows.Forms.TextBox textBoxConn;
private System.Windows.Forms.Label label2;
private System.Windows.Forms.ComboBox comboBox1;
private System.Windows.Forms.TextBox textBox2;
private System.Windows.Forms.FolderBrowserDialog folderBrowserDialog1;
private System.Windows.Forms.Button buttonWhere;
private System.Windows.Forms.Button buttonConnect;
private System.Windows.Forms.Button buttonGen;
/// <summary>
/// Required designer variable.
/// </summary>
private System.ComponentModel.Container components = null;
private System.Windows.Forms.Button buttonNewCon;
SqlConnection con ;
public Form1()
{
//
// Required for Windows Form Designer support
//
InitializeComponent();
//
// TODO: Add any constructor code after InitializeComponent call
//
}
/// <summary>
/// Clean up any resources being used.
/// </summary>
protected override void Dispose( bool disposing )
{
if( disposing )
{
if (components != null)
{
components.Dispose();
}
}
base.Dispose( disposing );
}
#region Windows Form Designer generated code
/// <summary>
/// Required method for Designer support - do not modify
/// the contents of this method with the code editor.
/// </summary>
private void InitializeComponent()
{
System.Resources.ResourceManager resources = new System.Resources.ResourceManager(typeof(Form1));
this.checkedCols = new System.Windows.Forms.CheckedListBox();
this.label1 = new System.Windows.Forms.Label();
this.textBoxConn = new System.Windows.Forms.TextBox();
this.label2 = new System.Windows.Forms.Label();
this.comboBox1 = new System.Windows.Forms.ComboBox();
this.textBox2 = new System.Windows.Forms.TextBox();
this.folderBrowserDialog1 = new System.Windows.Forms.FolderBrowserDialog();
this.buttonWhere = new System.Windows.Forms.Button();
this.buttonConnect = new System.Windows.Forms.Button();
this.buttonGen = new System.Windows.Forms.Button();
this.buttonNewCon = new System.Windows.Forms.Button();
this.SuspendLayout();
//
// checkedCols
//
this.checkedCols.Location = new System.Drawing.Point(8, 16);
this.checkedCols.Name = "checkedCols";
this.checkedCols.Size = new System.Drawing.Size(184, 349);
this.checkedCols.TabIndex = 0;
//
// label1
//
this.label1.Location = new System.Drawing.Point(200, 24);
this.label1.Name = "label1";
this.label1.Size = new System.Drawing.Size(96, 16);
this.label1.TabIndex = 1;
this.label1.Text = "Connection String";
//
// textBoxConn
//
this.textBoxConn.Location = new System.Drawing.Point(304, 24);
this.textBoxConn.Name = "textBoxConn";
this.textBoxConn.Size = new System.Drawing.Size(288, 20);
this.textBoxConn.TabIndex = 2;
this.textBoxConn.Text = "Server=(local);Database=PROPHARMADEMO;user=Mana;pwd=Mana;";
//
// label2
//
this.label2.Location = new System.Drawing.Point(208, 88);
this.label2.Name = "label2";
this.label2.Size = new System.Drawing.Size(72, 16);
this.label2.TabIndex = 3;
this.label2.Text = "Tables List";
//
// comboBox1
//
this.comboBox1.DropDownStyle = System.Windows.Forms.ComboBoxStyle.DropDownList;
this.comboBox1.Location = new System.Drawing.Point(304, 88);
this.comboBox1.Name = "comboBox1";
this.comboBox1.Size = new System.Drawing.Size(288, 21);
this.comboBox1.TabIndex = 4;
this.comboBox1.SelectedIndexChanged += new System.EventHandler(this.OnTableSelectedChanged);
//
// textBox2
//
this.textBox2.Location = new System.Drawing.Point(304, 120);
this.textBox2.Name = "textBox2";
this.textBox2.ReadOnly = true;
this.textBox2.Size = new System.Drawing.Size(248, 20);
this.textBox2.TabIndex = 5;
this.textBox2.Text = "c:\\";
//
// buttonWhere
//
this.buttonWhere.Image = ((System.Drawing.Image)(resources.GetObject("buttonWhere.Image")));
this.buttonWhere.Location = new System.Drawing.Point(560, 120);
this.buttonWhere.Name = "buttonWhere";
this.buttonWhere.Size = new System.Drawing.Size(32, 24);
this.buttonWhere.TabIndex = 6;
this.buttonWhere.Click += new System.EventHandler(this.OnFolderSelect);
//
// buttonConnect
//
this.buttonConnect.Location = new System.Drawing.Point(400, 56);
this.buttonConnect.Name = "buttonConnect";
this.buttonConnect.Size = new System.Drawing.Size(88, 24);
this.buttonConnect.TabIndex = 7;
this.buttonConnect.Text = "Connect";
this.buttonConnect.Click += new System.EventHandler(this.OnConnectClick);
//
// buttonGen
//
this.buttonGen.Location = new System.Drawing.Point(480, 328);
this.buttonGen.Name = "buttonGen";
this.buttonGen.Size = new System.Drawing.Size(120, 24);
this.buttonGen.TabIndex = 8;
this.buttonGen.Text = "Generate";
this.buttonGen.Click += new System.EventHandler(this.OnGenerateClick);
//
// buttonNewCon
//
this.buttonNewCon.Location = new System.Drawing.Point(496, 56);
this.buttonNewCon.Name = "buttonNewCon";
this.buttonNewCon.Size = new System.Drawing.Size(96, 24);
this.buttonNewCon.TabIndex = 9;
this.buttonNewCon.Text = "New Connection";
this.buttonNewCon.Click += new System.EventHandler(this.OnCloseConClick);
//
// Form1
//
this.AutoScaleBaseSize = new System.Drawing.Size(5, 13);
this.ClientSize = new System.Drawing.Size(608, 373);
this.Controls.Add(this.buttonNewCon);
this.Controls.Add(this.buttonGen);
this.Controls.Add(this.buttonConnect);
this.Controls.Add(this.buttonWhere);
this.Controls.Add(this.textBox2);
this.Controls.Add(this.comboBox1);
this.Controls.Add(this.label2);
this.Controls.Add(this.textBoxConn);
this.Controls.Add(this.label1);
this.Controls.Add(this.checkedCols);
this.Name = "Form1";
this.Text = "Form1";
this.ResumeLayout(false);
}
#endregion
/// <summary>
/// The main entry point for the application.
/// </summary>
[STAThread]
static void Main()
{
Application.Run(new Form1());
}
private void OnConnectClick(object sender, EventArgs e)
{
con = new SqlConnection(@textBoxConn.Text.Trim());
try
{
con.Open();
}
catch(Exception ex)
{
MessageBox.Show(ex.Message);
return;
}
SqlCommand cc = new SqlCommand("select * from sysobjects where xtype = 'U'",con);
SqlDataReader rd = cc.ExecuteReader();
while(rd.Read())
{
comboBox1.Items.Add(rd.GetSqlString(0));
}
rd.Close();
comboBox1.SelectedIndex = 0;
textBoxConn.Enabled = false;
MessageBox.Show("Connected !");
}
private void OnCloseConClick(object sender, EventArgs e)
{
if (textBoxConn.Enabled == true)return;
con.Close();
textBoxConn.Enabled = true;
}
private void OnTableSelectedChanged(object sender, EventArgs e)
{
LoadFields(GetTableFromName(comboBox1.Text));
}
private void LoadFields(DataTable _table)
{
checkedCols.Items.Clear();
for (int i =0 ; i < _table.Columns.Count ;i++ )
{
if(_table.Columns[i].AutoIncrement)continue;
checkedCols.Items.Add(_table.Columns[i].ColumnName);
}
_gl = _table;
}
private DataTable GetTableFromName(string _tableName)
{
#region GetTableFromName
string retour = string.Empty;
SqlDataAdapter da = new SqlDataAdapter("select top 1 * from " +"[" + _tableName + "]",con);
DataTable t = new DataTable(_tableName);
da.FillSchema(t,SchemaType.Source);
da.Fill(t);
return t;
#endregion
}
private string GetPlaceHolder(Type _colType, int i, bool doForUpdate)
{
string retour = String.Empty;
string _stringVarPlaceHolder = "'{{{0}}}'";
string _otherVarPlaceHolder = "{{{0}}}";
string _castDatetime = "(cast '{0}' as DateTime)";
#region Process String base var
if (_colType == System.Type.GetType("System.Char"))
retour = string.Format(_stringVarPlaceHolder,i.ToString());
if (_colType == System.Type.GetType("System.String"))
retour = string.Format(_stringVarPlaceHolder,i.ToString());
#endregion
#region Process Integer Base var
if (_colType == System.Type.GetType("System.Boolean"))
retour = string.Format( _otherVarPlaceHolder,i.ToString());
if (_colType == System.Type.GetType("System.DateTime"))
{
if (!doForUpdate)
retour = string.Format( _otherVarPlaceHolder,i.ToString());
else
retour = string.Format(String.Format(_castDatetime, _otherVarPlaceHolder),i.ToString());
}
if (_colType == System.Type.GetType("System.Decimal"))
retour = string.Format( _otherVarPlaceHolder,i.ToString());
if (_colType == System.Type.GetType("System.Double"))
retour = string.Format( _otherVarPlaceHolder,i.ToString());
if (_colType == System.Type.GetType("System.Int16"))
retour = string.Format( _otherVarPlaceHolder,i.ToString());
if (_colType == System.Type.GetType("System.Int32"))
retour = string.Format( _otherVarPlaceHolder,i.ToString());
if (_colType == System.Type.GetType("System.Byte"))
retour = string.Format( _otherVarPlaceHolder,i.ToString());
if (_colType == System.Type.GetType("System.Int64"))
retour = string.Format( _otherVarPlaceHolder,i.ToString());
if (_colType == System.Type.GetType("System.SByte"))
retour = string.Format( _otherVarPlaceHolder,i.ToString());
if (_colType == System.Type.GetType("System.Byte"))
retour = string.Format( _otherVarPlaceHolder,i.ToString());
if (_colType == System.Type.GetType("System.Byte[]"))
retour = string.Format( _otherVarPlaceHolder,i.ToString());
if (_colType == System.Type.GetType("System.Single"))
retour = string.Format( _otherVarPlaceHolder,i.ToString());
#endregion
return retour;
}
private DataTable _gl;
private string MakeInsert(string[] cols,DataTable _table)
{
#region MakeInsert
string _sqlInsert = "insert into [{0}] (";
string retour = string.Empty;
StringBuilder tmp = new StringBuilder();
tmp.Append(String.Format(_sqlInsert,comboBox1.Text));
#region Build Insert sql
for (int i =0 ; i < cols.Length ;i++ )
{
if(_table.Columns[cols[i]].AutoIncrement)continue;
tmp.Append(cols[i]);
if ((i + 1) == cols.Length)
tmp.Append(")");
else
tmp.Append(",");
}
tmp.Append(" values (");
int positionIndex = 0;
for (int i =0 ; i < cols.Length ;i++ )
{
if (_table.Columns[cols[i]].AutoIncrement)
{
positionIndex = i;
continue;
}
Type tp = _table.Columns[cols[i]].DataType;
tmp.Append(GetPlaceHolder(tp,positionIndex,false));
if ((i + 1) != cols.Length)
tmp.Append(",");
positionIndex++;
}
tmp.Append(")" + "\nGO\n");
#endregion
StringBuilder tmp2 = new StringBuilder();
SqlCommand cmd = new SqlCommand("select " + string.Join(",",cols) + " from " + _table.TableName,con);
SqlDataReader rd = cmd.ExecuteReader();
string[] values = new string[cols.Length];
while(rd.Read())
{
for (int k = 0; k < cols.Length; k++)
{
values[k] = rd.GetValue(rd.GetOrdinal(cols[k])).ToString().Replace("'","''").Replace(",",".");
}
tmp2.Append(string.Format(tmp.ToString(),values));
}
rd.Close();
retour = tmp2.ToString();
return retour;
#endregion
}
private void MakeCS(string streamF,string dest)
{
try
{
string _header = "//**********************************************************//\n"+
"//* Table:{0} *//\n"+
"//* Généré le : {1} par {2} *//\n"+
"//*********************************************************//\n\n";
StreamWriter Output=new StreamWriter(dest + @"\" + _gl.TableName.Trim() + ".sql");
Output.WriteLine(string.Format(_header,_gl.TableName,DateTime.Now,"Sodson"));
Output.WriteLine(streamF);
Output.Flush();
//Ferme les flots de données
Output.Close();
}
catch(Exception ex)
{
Console.WriteLine(ex.Message);
}
}
private void OnGenerateClick(object sender, EventArgs e)
{
string[] cols;
if (checkedCols.CheckedItems.Count >0)
{
cols = new string[checkedCols.CheckedItems.Count];
}
else
{
return;
}
for(int i = 0; i < cols.Length; i++)
{
cols[i] = checkedCols.CheckedItems[i].ToString();
}
MakeCS(MakeInsert(cols,_gl),textBox2.Text);
MessageBox.Show("Generation terminé");
}
private void OnFolderSelect(object sender,EventArgs e)
{
if ( folderBrowserDialog1.ShowDialog() == DialogResult.OK)
{
textBox2.Text = folderBrowserDialog1.SelectedPath;
}
}
}
}
Vous n'êtes pas encore membre ?
inscrivez-vous, c'est gratuit et ça prend moins d'une minute !
Les membres obtiennent plus de réponses que les utilisateurs anonymes.
Le fait d'être membre vous permet d'avoir un suivi détaillé de vos demandes et codes sources.
Le fait d'être membre vous permet d'avoir des options supplémentaires.